MS Access - How to split one large table into smaller tables by record count

[bush3102]

I have a table in MS Access that has +17K of records. I am trying to break down that table into smaller tables of 500 records each. Using the following code, I am able to create the temp table, but I cannot reset the number of ID column. The ID column on the original table is an autonumber. I am trying to reset the ID field on the temp table so I can do a record search starting at 1 and going to 500.

The alter SQL that I have does not update/reset the temp table's ID column to 1. Any ideas?

Function SplitTables_Actual()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Dim rowcount As Long
Dim tblcount As Integer
Dim i As Integer
SQL = "SELECT * INTO tmp_Flush_Actual FROM BIG_Table"
DoCmd.RunSQL SQL
SQL = "ALTER TABLE tmp_Flush_Actual ALTER COLUMN ID COUNTER(1,1)"
DoCmd.RunSQL SQL
SQL = "SELECT count(*) as rowcount from BIG_Table"
rs.Open SQL, cn
rowcount = rs!rowcount
rs.Close
tblcount = rowcount / 500 + 1
For i = 1 To tblcount
SQL = "SELECT * into tmp_flush_Actual" & i & " FROM tmp_Flush_Actual" & _
" WHERE ID <= 500*" & i
DoCmd.RunSQL SQL
SQL = "DELETE * FROM tmp_Flush_Actual" & _
" WHERE ID<= 500*" & i
DoCmd.RunSQL SQL
Next i
 
End Function
 

[Rabbit]

This is something you should not do. You are denormalizing your data and it will make future queries more complicated. Whatever reason made you think that splitting up the table is necessary can be accomplished using one table.

More info about normalization can be had in our normalization article: http://bytes.com/topic/access/insigh...ble-structures

[TheSmileyCoder]

I agree with Rabbit. Keep it in a single table. If you need to you can always select a small subset of your table by using a query. Access will only pull the information needed to perform the query, and as such access will not pull down the entire table. Thus there is very little to be gained by this approach, and alot of time to be wasted.

[jimatqsi]

One more to chime in in agreement. 17K is not really so many records. Would you care to discuss the decision for splitting the data. I only ask because if it is a matter of performance you can problem solve any performance problems in a better way than splitting the data into various tables.

And if you do split, this would be a good time to reconsider how your keys are defined.

Jim

[bush3102]

We are uploading data in an Excel spreadsheet, via an IE GUI that will not accept any file with more than 500 records.

[Rabbit]

There's really no need to break up your table into multiple tables because of that. You can just create a query that returns 500 records at a time.