Access Copy data from Online SQL table to Access Table

Murray Brown
Murray Brown used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are you able to link the only SQL Tables to your local Access database? If so then the SQL that you've shown would probably be faster, although you might consider using a JOIN instead of subselect.

If you're not able to link the tables, then you really don't have any other options other than opening a Recordset and looping through.

You could perhaps dump the contents of the SQL table into a temporary table, and then use the JOIN syntax to get the records needed.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much. Going to go with the recordset loop

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial