469,282 Members | 1,650 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

How to split large access table to export to excel

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.
Sep 17 '10 #1
6 13907
Mariostg
332 100+
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.Connection
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.
Sep 17 '10 #2
I follow everything but "NP_Monitoring" Where did that table come from?
Sep 17 '10 #3
Mariostg
332 100+
Sorry about that. This would be the name of your large table you want to split.
Sep 17 '10 #4
If I change "NP_Monitoring" to the name of my table, I get overflow error at "rowcount = rs!rowcount"
Sep 17 '10 #5
Mariostg
332 100+
Ok, that makes sense, Dim rowcount as a long instead :

Dim rowcount As Long
Sep 17 '10 #6
You are wonderful :) thank you !
Sep 17 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by tomg | last post: by
1 post views Thread by Jim M | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.