I have set up the Access Database Engine for SQL Server 2008 R2 (Service Pack 1) on many machines running x64 Windows 7 without any problems running as 'Administrator' (and setting up SQL Server as 'sa'). I have also installed the Access Database Engine on a x86 based machine running Windows 7 and SQL Server 2008 R2 Express Edition (x86) again without problems. However, when I attempt to setup the Access Database Engine using the Developer Edition of SQL Server 2008 R2 under x86 Windows 7 it does not work!? It works for Express, but not for Developer. I have attempted a full reinstall of SQL, but still it does not work, throwing the following error:
OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".
The machine information is (obtained from the command line running 'systeminfo'):
OS Name: Microsoft Windows 7 Professional OS Version: 6.1.7601 Service Pack 1 Build 7601 OS Configuration: Standalone workstation System Type: X86-based PC Processor(s): 1 Processor(s) Installed. : x64 Family 6 Model 42 Stepping 7 GenuineIntel ~2501Mhz
To setup the Access Engine having successfully installed the 32-bit version, I go into management studio and run the following queries:
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 each of the following separately. EXEC sp_configure 'show advanced options',1 reconfigure EXEC sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure
One of the queries I usually run to establish whether or not the Linked Server is configured correctly is:
SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'J:\Data\thisDir\thatDir\someAccessFile.mdb'; 'admin';'',thisTable); GO
This process works for all configurations including SQL Express 2008 R2 both (x86 and x64) with the Access Database Engines (x86 and x64 respectively), but for Developer Edition on x86 Windows 7 it does not, why?
Has anyone come across this who can offer me some advice as to how to solve this issue?
Thanks very much for your time.