Solved: SQL Server to Access Table

[Marcster]

Hi people,

I can import SQL Server Data into an access table by using the built in wizard, get external data.

Can this be done with VBA?.


As I have an access table setup to accept SQL Server data, I can obtain the SQL Server data, but how do I get it to populate the table?.


Thanks,

[CreganTur]

Use an ADO or DAO connection; connect to your SQL sever database, pull the data you want into a recordset, and then push the recordset contents into the Access table.

Or, if you're going to be pulling data from teh same SQL Server table on a regular basis, just setup a linked table to the SQL Server and setup an append query to pull the data into the Access Table.

[Marcster]

Have you sample code to do this?...

[CreganTur]

This example shows how to connecto to an Access database. Check http://www.connectionstrings.com/ to find the connection string for your type of SQL Server.

Dim conn As ADODB.Connection '<<<connection to SQL Server
Dim rst As ADODB.Recordset '<<<Recordset for SQL Server
Dim dbConn As ADODB.Connection '<<<connection for current access db

'create connection to external database
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\MyDatabase.mdb"

'pull records from database into recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblEmployees WHERE EmployeeID < 50", _
conn

Set dbConn = CurrentProject.Connection

'move through each record and load it into the local access database using Execute method
rst.MoveFirst
Do While Not rst.EOF
dbConn.Execute("INSERT INTO Table(Field1, Field2) " _
"VALUES('" & rst.Fields(0).Value & "', '" & rst.Fields(1).Value & "')"
rst.MoveNest
Loop

dbConn.Close
Set dbConn = Nothing
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing

This example shows how you can create and populate a recordset from an external database, and then load the values into an access database.

This was written from memory, so there might be a few kinks in it. Access Help will be a good resource.

Also, you're going to need a reference (tools-> references) to Microsoft ActiveX Data Objects x.x Library (where x is your version number).

HTH:thumb