Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access Copy data from Online SQL table to Access Table


I am using the following code toread the contents of an online table.
I have a table of the same structure in my Access database and want to add records
with an ID that isn't already there.
Should I loop through the recordset as in the code section below
or is there a quicker way to do something similar to the following code:
Sub Append_Extra_From_People1_To_People2()

    Dim S As String
    S = "Insert Into People2"
    S = S & " Select * From People1"
    S = S & " Where People1.[Operator COY] Not In (Select [Operator COY] From People2)"

    DoCmd.RunSQL S

End Sub
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command

Sub ConnectToSQLServer()

    On Error GoTo EH

    Set con = New ADODB.Connection
    con.Open "Provider=SQLOLEDB;Data Source=,1444;Network Library=DBMSSOCN;Initial Catalog=lesedi_hr;User ID=Graham;Password=maharg1234;"

    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM Table1", con

    Do While Not rst.EOF
        MsgBox rst.Fields("Col1").Value
    Set rst = Nothing
    Set con = Nothing

Exit Sub

    MsgBox Err.Description
End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Murray Brown

thanks very much. Going to go with the recordset loop
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck