INSERT ADO Recordset into Currentdb Table

[GohDiamond]

Function Write_rstADO_to_CurrdB_Table()

'Assumes you have already setup a DSN to your Server
'Assumes YOURDESTINATIONTABLE is the same structure as your SERVER.TABLE

Dim cnnADO As ADODB.Connection
Dim wkspDAO As DAO.Workspace

Dim rstADO As ADODB.Recordset
Dim rstDAO As DAO.Recordset

' Dim objRequestID As ADODB.Field {If you want to confirm by writing to the immediate Window}

Dim i As Long, strSQL As String, rstDEST As String ', x As Integer {If you want a count of the records you are writing to the immediate Window}

Set cnnADO = CreateObject("ADODB.Connection")
    cnnADO.Open "DSN=NAMEOFYOURDSNSERVER"

Set rstADO = CreateObject("ADODB.Recordset")

    strSQL = "Select * from SERVER.TABLE WHERE FIELDNAME1 = 'CONDITION1' Or FIELDNAME1= 'CONDITION2' Or FIELDNAME1 = 'CONDITION3'"
    rstADO.Open strSQL, cnnADO
    
Set wkspDAO = DBEngine.Workspaces(0)
    wkspDAO.BeginTrans 'Start the transaction buffer to gather all the records before saving the table

    rstDEST = "YOURDESTINATIONTABLE"
Set rstDAO = CurrentDb.OpenRecordset(rstDEST, dbOpenDynaset)


With rstADO
    'Set objRequestID = rstADO(0) {to confirm by writing to the immediate Window}
    Do While Not .EOF 'x = 10
    
    With .Fields                                'Begin to Loop thru the ADO Recordset
    rstDAO.AddNew                                'Begin Write to Table buffer
        For i = 0 To (.Count - 1)               'Begin Loop thru Fields
        rstDAO.Fields(i).Value = .Item(i).Value  'Write each field value to Table Buffer
        Next                                    'Continue loop thru Fields to end of Fields
    rstDAO.Update                                'Update the Table Buffer
    End With                                    'Prepare to move to the next Record in the ADO recordset

    'x = x + 1                   {to write the count of records to the Immediate Window}
    'Debug.Print x; objRequestID {to write the rstADO(0) to the ImmediateWindow}
     
    .MoveNext                                   'Move to the Next Record of the ADO Recordset

Loop                                            'Continue loop to the End of the ADO Recorset
wkspDAO.CommitTrans                             'Commit the transaction to the DAO dataset which is the YOURDESTINATIONTABLE

MsgBox "DONE"

End With

cnnADO.Close
Set rstADO = Nothing


End Function

Don't wanna lose this bit of code

Cheers!
Goh

 

[Trevor G]

What's your point? What's your Question?

 

[howling_muffin]

Thank you for this lifesaving piece of code, it's exactly what I need and saved me a good few hours of research

 

[theDBguy]

Thank you for this lifesaving piece of code, it's exactly what I need and saved me a good few hours of research

Hi. Welcome to AWF!

Thank you for sharing your find. You might also consider posting a reference to the source to give credit to the original author.

 

[arnelgp]

hmm, you can always link the server.table to your db and without Coding, can update the local table using Query.