Importing into Access From SQL Server

[jharding08]

I am running an Export table function that reads an sql server connection then imports a table into access db connection. I have code that reads an access db and imports a table into another (different) access db. this is the code:

sSQL = "SELECT " & Columns & " INTO " & sTable & " FROM " & sTable & " IN '' [MS ACCESS;DATABASE=" & DatabaseFrom & ";TABLE=" & Table & "]"

oConn.Execute sSQL


I am trying to find out the syntax for importing from SQL Server.

Any ideas will be appreciated.

 

[cmmrfrds]

SQL Server has a function called OPENROWSET that allows you to reference an Access mdb in the SQL Statement run in SQL Server. Look up openrowset in the sql server documentation.

 

[jharding08]

I have been doing that for when i want to import into SQL Server. In this case, i want to export from SQL Server and import into Access, so the current connection that will execute the SSQL statement will be for access. I believe I just need to change the

[MS ACCESS;DATABASE=" & DatabaseFrom & ";TABLE=" & Table & "]"

part of it to connect to the SQL server within the statement