How to split large access table to export to excel

[Angelia Howard]

There is no way to narrow down the data with queries.
I would like to have the vb code to split up an access table into tables of no more than 50000 each to be able to export to excel. The export has to be excel.

[Mariostg]

This is one way...You can split your big table in as many smaller tables as required with this:

Function SplitTables()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connectio n
Set cn = CurrentProject. Connection
Dim rowcount As Integer
Dim tblcount As Integer
Dim i As Integer
sql = "SELECT * INTO tmp_Flush FROM YourBigTable"
DoCmd.RunSQL sql
sql = "ALTER TABLE tmp_Flush ADD COLUMN id COUNTER"
DoCmd.RunSQL sql
sql = "SELECT count(*) as rowcount from YourBigTable"
rs.Open sql, cn
rowcount = rs!rowcount
rs.Close
tblcount = rowcount / 50000 + 1
For i = 1 To tblcount
sql = "SELECT * into tmp_flush" & i & " FROM tmp_Flush" & _
" WHERE id<=50000*" & i
DoCmd.RunSQL sql
sql = "DELETE * FROM tmp_Flush" & _
" WHERE id<=50000*" & i
DoCmd.RunSQL sql
Next i

End Function

Then export those tables into Excel.

[Angelia Howard]

I follow everything but "NP_Monitor ing" Where did that table come from?

[Mariostg]

Sorry about that. This would be the name of your large table you want to split.

[Angelia Howard]

If I change "NP_Monitor ing" to the name of my table, I get overflow error at "rowcount = rs!rowcount"

[Mariostg]

Ok, that makes sense, Dim rowcount as a long instead :

Dim rowcount As Long

[Angelia Howard]

You are wonderful :) thank you !