User-defined type not defined Dim rs As New ADODB.Recordset and Dim cn As New ADODB.Connection

[SachAccess]

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

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 - Post.Byes

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.

bytes.com

 

[pbaldy]

You need the Microsoft ActiveX Data Objects library checked.

 

[SachAccess]

Hi @pbaldy thanks a lot for the help.

 

[pbaldy]

Happy to help!