Reading Access 2010 .mdb files from SQL Server directly

OK, I have already ripped most of my hair out and I DID get it to work with many hours of research and many smart people at the helm online and at my client’s work. This article will save your HOURS I hope. The permissions thing was credit to them. Thanks.

[UPDATE: Feb 2014 – I now used a linked Server to a dB with a password (the with a password is a big deal – googling for a long time finally paid off – whoo hoo it works! See that article too – you will still need this one]

Here are some links I used to get things working. I use the OPENROWSET (see SQL example below) and there are 4 things that are key (this guy saved my bacon so I will link this to give him the most credit). If you have 32 bit office installed on a 64 bit machine etc etc? DON’T MIX BIT STUFF. Have 64 bit everything and this goes smoother – everyone is ripping their hair out over this on google.

use the query type that works. This requires all this to work
- a driver installed : Microsoft Access database engine 2010 (English) available on the web from msdn. It will not take effect until a reboot
- a registry edit for this driver (below for details)
- SQL Commands to be run on the SQL server to allow the MICROSOFT.ACE.OLEDB.12.0 driver to work (included below with details)
- you MIGHT need to open permissions on 2 hidden folders under the c:\Users\ directory owned by the users who run the SQL Server accounts
- NTFS permissions set properly on the data directory containing the .mdb files (and those files’ permission set properly as well)
  - we are running SQL from another server and the user is mydomain\myaccount running SQL. IF you have a default install – you can see what account is running it using services.msc command from dos. It is most likely
    - NT AUTHORITY\Network Service or “Network Service” if you are searching it. Don’t forget to tell the search to look in the local computer. Frustrating if you don’t.
- I am using an APSX to also access the files and this permissions article is a succinct one that I found handy and am putting here for my own reference. IF THIS IS AN ASPX WEB APP – RESTART YOUR WEBSITE AND APP POOL. 2.5 hours with 2 of us was wasted on this one.
- If you use a password protected mdb – your job is to post below. TAKE THE PASSWORD OFF. I haven’t figured this one out is why my advice is as it is.

The registry edit for the SQL Server install computer requires 3 registry keys adjusted in the following location – BUT DO IT BY HAND. Your path might be slightly altered where it lists the version:


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001
"DisallowAdhocAccess"=dword:00000000

SQL Stored Proc to get things working


EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OVERRIDE
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
RECONFIGURE WITH OVERRIDE
GO


-- The stuff below can be pasted into a stored proc or just run as is (I think). Copy all this in to SQL Server and hit execute (adjusting stuff to suite your needs etc.)

SET @sql = N'INSERT INTO dbo.tblMySQLServerTable (sqlServerImportFilePath , sqlServerImportDateTime, '
 SET @sql = @sql + ' sqlServerField1, sqlServerField2, sqlServerField3) '
 SET @sql = @sql + ' SELECT ''' + @accessFileName + ''' As ImportFilePath, NULL As ImportDateTime, '
 SET @sql = @sql + ' msAccessField1, msAccessField, msAccessField3 '
 SET @sql = @sql + ' FROM OPENROWSET (''MICROSOFT.ACE.OLEDB.12.0'', ''' + @accessFileName + ''';''Admin'';'''', tblMyTableInMsAccess)';
 PRINT @sql
 EXEC SP_executeSQL @sql

Links I used: