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

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

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

Link to home
membership
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

ASKER

thanks very much. Going to go with the recordset loop