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

Hi

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=196.220.43.247,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
        rst.MoveNext
    Loop
    
    rst.Close
    
    Set rst = Nothing
    Set con = Nothing


Exit Sub

EH:
    MsgBox Err.Description
    
End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
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
or
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

ASKER
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