SachAccess
Active member
- Local time
- Today, 08:36
- Joined
- Nov 22, 2021
- Messages
- 389
Hi,
Am trying to edit below code.
I am getting error at below two lines. I am not sure if it is related to adding any reference to the module.
Can anyone please help me in this.
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Copied from below URL.
Am trying to edit below code.
I am getting error at below two lines. I am not sure if it is related to adding any reference to the module.
Can anyone please help me in this.
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Code:
Sub SplitTables()
Dim rowcount As Integer
Dim tblcount As Integer
Dim i As Integer
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
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 Sub
Copied from below URL.
How to split large access table to export to excel - Microsoft Access / VBA
How to split large access table to export to excel. Microsoft Access / VBA Forums on Bytes.
bytes.com