How to append data from an Access table to a SQL server table via a pass-through query?

Question

It seems like it's only possible to use a pass-through query to retrieve data from your SQL Server tables and into MS Access. But how about the other way? From an Access table to a SQL server table.

What are my options from within MS Access when I need high performance?
(The normal approach of having an append query that appends to a linked table is simply too slow)

In an pass-through query I cannot reference MS Access tables or queries, and therefore my INSERT INTO statement cannot work. Is there a work around via VBA?

Answer 1

You can use OPENROWSET in a passthrough query.

SELECT id,
       atext
INTO   anewtable
FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                  'z:\docs\test.accdb'; 'admin';'',table1); 

You may need some or all of these options:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
     N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 
     N'DynamicParameters', 1
GO

I doubt that it will be any faster.