Monday, July 01, 2013

SQL Server 2012 Linked Server for MS Access

Here's the scoop: I've installed SQL Server, but I want to use Management Studio (SSMS) to query an Access Database.  I followed the directions, and also found out that the Jet OLEDB 4.0 provider is only available for 32-bit.  But now I'm getting this error:


Text:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[server name]".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[server name]" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
There are some solutions to similar errors, but none of them are working.  What now?  Try running SSMS as an administrator.  In fact make sure it always runs with administrative rights by right-clicking the shortcut/executable, selecting properties, and checking the "Run this program as an administrator" box:


Once you do this, SSMS will have full rights to connect to your MS Access files (and any others you'd like to query).


No comments: