We help IT Professionals succeed at work.

Access VBA populate table from recordset

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I am using the following code to read data from an online SQL Server 2008 database.
I want the data to populate a table called Table1 in the Access database in which this VBA resudes. How do I adjust my code to do this?
Sub ConnectToSQLServer()

    On Error GoTo EH
    'Assuming the SQL Express database is configured for remote connections you can. You generally do this by opening a connection to the database and then issuing your query:
    'Ref Microsoft AxtiveX Data Objects 6.0 Library
  
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=strCoonect
     
    'sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)
    
    '/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2
    
    
    Set rst = New ADODB.Recordset
    
    rst.Open "SELECT * FROM Table1", con
    
    'These are the basics, but this would leave you with a fully filled Recordset.
    
    'Note that unless your online host has assigned a name for this, you'd have to use the full IP address as "Server" portion
    
    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:
Do you mean you want to move the data from Table1 into a local Access table?

If so, and if you must use Recordsets (i.e. you're not working with Linked Tables), then you can do this:

Do Until rst.EOF
  Currentdb.Execute "INSERT INTO MyLocalTable(Col1, Col2, Col3) VALUES('" & rst("Field1") & "','" & rst("Field2") & "','" & rst("Field3") & "')"
  rst.MoveNext
Loop

If you are working with linked tables, you can use an INSERT INTO query:

Currentdb.Execute "INSERT INTO MyLocalTable(Col1, Col2, Col3) SELECT Field1, Field2, Field FROM MyLinkedTable"

Note that MyLocalTable must exist. If it does not exist, and you want to create it each time:

Currentdb.Execute "SELECT * FROM MyLinkedTable INTO MyLocalTable"

In the case above, MyLocalTable cannot exist.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much