Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag 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

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Murray Brown


thanks very much. Going to go with the recordset loop