Insert into ms Access from SQL Server using OleDb provider

Question

In this post Remou has nicely described how to bulk insert data from a SQL Server table into an MS Access table using the following syntax:

SELECT fld1, fld2 INTO accessTable FROM [sql connection string].sqltable

In the examples in his post, Remou is using the ODBC keyword. How can we modify those examples if we were to use SQL Server Native Client 10.0 OLE DB Provider instead of SQL Server Native Client 10.0 ODBC Driver

For example, I tried the following but it does not work. It gives me the error "Could not find installable ISAM" on the call to ExecuteNonQuery(). AccessConn works fine as I've tested it with the ODBC example of Remou. The issue seems to be with the connection string for SQL Server.

OleDbConnection AccessConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\TestFolder\\Test.mdb'");
OleDbCommand AccessCmd = new OleDbCommand("INSERT INTO Table2 SELECT * FROM [OLEDB;Provider=SQLNCLI10;Server=MyServer\\SQLEXPRESS;Database=TestDb1;Uid=TestUser;Pwd=TestPassword].Table1", AccessConn);
AccessCmd.ExecuteNonQuery();
AccessConn.Close();

Thanks.

Gord Thompson · Answer 1 · 2013-04-12T09:35:34.200

I suspect that there simply isn't a way to do what you describe using OLEDB.

I tested the following VBA code and it worked:

Sub odbcTest()
Dim cdb As DAO.Database, sql As String
Set cdb = CurrentDb
sql = _
        "SELECT ID, LastName INTO myContacts " & _
        "FROM [ODBC;Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS;Database=myDb;Trusted_Connection=yes;].dbo.myContacts"
cdb.Execute sql, dbFailOnError
End Sub

Then, I tested (what I hoped would be) the OLEDB equivalent and it failed with the same error that you got ("Could not find installable ISAM.")

Sub oledbTest()
Dim cdb As DAO.Database, sql As String
Set cdb = CurrentDb
sql = _
        "SELECT ID, LastName INTO myContacts " & _
        "FROM [OLEDB;Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=myDb;Trusted_Connection=yes;].dbo.myContacts"
cdb.Execute sql, dbFailOnError
End Sub

But notice the ODBC; and OLEDB; prefixes inside the square brackets: They are not needed when establishing a "normal" connection. The following code works...

Sub adoTest()
Dim con As Object
Const sqlInstance = ".\SQLEXPRESS"
Set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server Native Client 10.0};Server=" & sqlInstance & ";Database=myDb;Trusted_Connection=yes;"
MsgBox "ODBC connection to " & sqlInstance & " established."
con.Close
Set con = Nothing
End Sub

...but when I add an ODBC; clause to the beginning of the connection string...

Sub adoTest()
Dim con As Object
Const sqlInstance = ".\SQLEXPRESS"
Set con = CreateObject("ADODB.Connection")
con.Open "ODBC;Driver={SQL Server Native Client 10.0};Server=" & sqlInstance & ";Database=myDb;Trusted_Connection=yes;"
MsgBox "ODBC connection to " & sqlInstance & " established."
con.Close
Set con = Nothing
End Sub

...it fails with "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

So, that ODBC; prefix in the first Sub odbcTest() code sample above is a clue that the FROM [ODBC;...] method is just an Access shortcut to pull data in from an ODBC data source without explicitly creating a linked table. (That stuff inside the square brackets would be the .Connect property of the TableDef object for a linked table.)

Therefore, since as far as I know Access only supports ODBC linked tables (and not OLEDB linked tables), using this method with OLDEB won't work.

Insert into ms Access from SQL Server using OleDb provider

1 Answers1