How To Append Data From An Access Table To A Sql Server Table Via A Pass-through Query?

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

Solution 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.

Solution 2:

It is possible in Access to use a macro to dynamically create the pass through query definition using SetValue (I think this is called SetProperty now). Basically you can update the query definition for an existing pass through query, then run it. I did this once and it was a lot faster, but that was a long time ago. I may have had a piece of VB that cycled through a table to create the query.