2

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?

MOLAP
  • 784
  • 4
  • 13
  • 28
  • 1
    Linking in this direction is always slow, as Access will process all the work locally so you don't get the advantage of the server. If you have some level of SQL server access then what I always found was best, is to export from access to a standard txt or csv file and then use the **bulk insert** option of SQL server. If you're forced to do all from the access end then there isn't really a quick method available unfortunately. – Matt Donnan Oct 18 '12 at 11:31
  • 1
    I would probably try comparing speeds using DAO (in VBA) or ADO. You could also try using ADO together with a SQL Server Stored Procedure. But I agree with Matt that your biggest gains will probably be made by doing this with something other than Access. Even a .NET dll or AddIn might work since I believe either one could use a background thread (there could be gotchas and hiccups with this though). – HK1 Oct 18 '12 at 14:10
  • Thanks for your input. Seems like this reveals yet another limitation in Access, besides the many other limitations it has. – MOLAP Oct 19 '12 at 10:03
  • 3
    Now I certainly agree with the comments here that some type of bulk transfer methodology in which perhaps you place the data on the server where SQL server resides could most certainly yield great promise here. As for limitation of access for SQL server, by definition a passthrough query means that the query is going to run server side on SQL server, therefore logically if this is assumed to be some limitation, and we're obviously talking about a limitation of SQL server and not access then right? – Albert D. Kallal Oct 19 '12 at 21:38
  • @AlbertD.Kallal: Thanks for your contributions to the topic. Your input definetely brings more light on the nuances in this debate. My reason for listing the "problem" as an Access limitation was because the problem initially was perceived as such, since I'm in the Access GUI and using a Access-specific feature such a pass-through query. So it's surely a suprise that the root cause can be seen as a limitation of SQL server. – MOLAP Oct 22 '12 at 07:52
  • Related question: [How to increase performance for bulk INSERTs to ODBC linked tables in Access?](http://stackoverflow.com/q/25863473/2144390) – Gord Thompson Mar 31 '16 at 14:17

2 Answers2

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

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.

fuhry
  • 79
  • 1