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

Question

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

To reset the "seed" on a table in Access, look at this post: http://stackoverflow.com/questions/2290938/reset-autonumber-seed — HK1, Aug 14 '13 at 20:52
17K records really isn't that many. I'm not sure why you would want to do this. I usually don't see issues on Access until you get over 100K records but it depends a lot on your hardware and your network. — HK1, Aug 14 '13 at 20:53
We are uploading data in an Excel spreadsheet, via an IE GUI that will not accept any file with more than 500 records. — Jeremy F., Aug 14 '13 at 20:59
Can you use `SELECT TOP 500 * WHERE ID > EndOfLastBatchID` queries to get the data you need to upload? — HK1, Aug 14 '13 at 21:02
Another good option in your case is to just stop using AutoNumber fields and use your own ID Generator function for every insert. — HK1, Aug 14 '13 at 21:03

score 0 · Accepted Answer · answered Aug 28 '13 at 14:06

Bottom line, on the initial query, don't select the ID (Autonumber) column. Select the columns you want into the initial temp table, then alter the table to add a new counter column. I used COUNTER(1,1) so that each time the temp table is created, the first record is 1.

I added a little nugget that saves the broken files to a folder. I commented out the error handling, but uncomment it to make sure your save directory is working correctly.

    Function SplitTables_RTPA_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
        'Just don't select the ID column
        SQL = "SELECT Company, Incurred_By, Transaction_Type, Format(Transaction_Date, 'mm/dd/yyyy'), Investment_ID, " & _
        "Task_ID, Charge_Code, Resource_ID, Role, Notes, Quantity INTO tmp_Flush_Tran_Actual FROM Actual_Transaction_Data"
    DoCmd.RunSQL SQL
        SQL = "ALTER TABLE tmp_Flush_Tran_Actual ADD COLUMN ID COUNTER(1,1)"
    DoCmd.RunSQL SQL
        SQL = "SELECT count(*) as rowcount from Actual_Transaction_Data"
    rs.Open SQL, cn
    rowcount = rs!rowcount
    rs.Close
    tblcount = rowcount / 100 + 1

    For i = 1 To tblcount
            'Create Temp Flush File
            SQL = "SELECT * into tmp_Flush_Tran_Actual" & i & " FROM tmp_Flush_Tran_Actual" & _
            " WHERE ID <=100*" & i
        DoCmd.RunSQL SQL
            SQL = "ALTER TABLE tmp_Flush_Tran_Actual" & i _
            & " DROP COLUMN ID;"
        DoCmd.RunSQL SQL

        'Delete 500 from Temp Flush File
            SQL = "DELETE * FROM tmp_Flush_Tran_Actual" & _
            " WHERE ID <=100*" & i
        DoCmd.RunSQL SQL

        'On Error GoTo ErrorHandler

        Dim strTable As String
        Dim strWorksheetPath As String

        'Location where you want to save the broken out files
        strWorksheetPath = "C:\YOUR TEMP FOLDER\TEST\"
        strWorksheetPath = strWorksheetPath & "Actual_Transactions" & i & ".xls"
        strTable = "tmp_Flush_Tran_Actual" & i

        DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel9, TableName:=strTable, FileName:=strWorksheetPath, hasfieldnames:=True

        'ErrorHandlerExit:
        '    Exit Function
        '    'Next i
        '
        'ErrorHandler:
        '    MsgBox "Error No: " & Err.Number _
        '    & "; Description: " & Err.Description
        '    Resume ErrorHandlerExit

    Next i

End Function

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

1 Answers1