INSERT ADO Recordset into Currentdb Table (1 Viewer)

GohDiamond

"Access- Imagineer that!"
Local time
Today, 14:04
Joined
Nov 1, 2006
Messages
549
Code:
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

Registered User.
Local time
Today, 19:04
Joined
Oct 1, 2009
Messages
2,341
What's your point? What's your Question?
 

howling_muffin

New member
Local time
Today, 19:04
Joined
Jul 29, 2020
Messages
5
Thank you for this lifesaving piece of code, it's exactly what I need and saved me a good few hours of research
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:04
Joined
Oct 29, 2018
Messages
11,114
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

error reading drive A:
Local time
Tomorrow, 02:04
Joined
May 7, 2009
Messages
9,872
hmm, you can always link the server.table to your db and without Coding, can update the local table using Query.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom