PDA

View Full Version : Access



Bob Worsley
7-Mar-2022, 09:49 PM
Has anyone come up with a scheme to read an Access database via SQL Server? I've read that it can be done and have tried a couple of suggestions that I found on the Web but so far none have actually worked

Mike Peat
8-Mar-2022, 03:00 AM
Bob

I've used Access from DF, but only through ODBC.

Mike

Garret Mott
8-Mar-2022, 07:09 AM
I've imported access dbs into MS SQL without problem - I've not tried to just hook up to them though. As Mike says, through DF with ODBC should work.

Bob Worsley
8-Mar-2022, 10:32 AM
The problem is that I was asking this for a non-DF friend who's trying to read the Access db from his non-DF application which apparently can't use ODBC though it can interface with SQL Server. Sorry for the confusion but I was trying to keep question simple but apparently did too good a job. So the goal is still to read Access via SQL Server

Michael Mullan
8-Mar-2022, 10:51 AM
from THIS StackOverflow (https://stackoverflow.com/questions/3145270/select-from-access-database-file-and-insert-to-sql-database)



SELECT* INTO dbo.TestAccess FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0'
'Data Source="\\server\share\somefolder\scratchpad.mdb"')...MyTable;

Bob Worsley
8-Mar-2022, 11:26 AM
Funny you should come up with that, I was looking at it earlier and while it didn't actually work, a variation of it did:



SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'C:\VBdotNet\QBInterface\AccessDB\Assets4_Data.mdb ';
'admin';'',Employees)


Different driver, this one for 64-bit. But then there are a couple of other wrinkles. Before the above approach will work you first need to execute the following:



sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
---------------------------------------------
USE [master]


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


Then download & install the 64-bit version of the "Microsoft.ACE.OLEDB.12.0" driver, assuming you're using 64-bit SSMS

And then the query works!