INSERT INTO.... SQL Server TO Access

[stanl]

Troubling.... We are migrating MySQL data from a 3rd party into SQL Server for storage (as opposed to when I stored in Access). Problem is the SQL Server is controlled by IT and they will not allow ad-hoc distributed queries (which I was used to with other experience when I either ran or queried SQL Server).

That means no access to openrowset() or opendatabase() to directly run SELECT INTO or INSERT INTO queries to fill some of the local Access tables still used for reporting.

Now, with MySQL it was easy to create ADO queries using the IN "" [ODBC;Driver= etc ] clause, but that does not appear to work with SQL Server (either the Provider or Driver).

The nasty alternative is to query SQL Server, save as csv and import with the Access Provider Text driver. 

NOTE: I should have also said linked servers are also not an option per IT.

I have googled but unable to find a way to INSERT directly into Access from SQL Server via an ADO command.

If someone has overcome this obstacle, I would appreciate a reference.

[JTaylor]

Wish I had a good idea but not sure what it would be.   Using GetRows() and ArrayFilePutCSV() would make that part easy and, as you mentioned, import in that fashion.   Guessing OLE wouldn't make things any easier and may not be feasible?  All other ideas I have would probably be too slow.

Jim

[stanl]

Actually I figured it out. The IN "" [ODBC;,,, clause didn't work, but

SELECT * INTO [AccessTable] FROM  [ODBC;,,,].dbo.SQLServerTable WHERE etc...

does. I had a lot of trial and error with syntax, for example the standard "with (NOLOCK)" to prevent contention will not work but NOLOCK will. It seems that and [ or ] or ( or ) after [ODBC;,,,]. will give an "Error in the FROM clause"....

Was able to test with a view as well as a table and with both the Microsoft SQL Server driver as well as the Native Client 11.0

[JTaylor]

Glad to hear it.  I stopped thinking along those lines as I assumed from your original post that nothing along those lines would work.

Jim