SQL Server Linked Server – Querying MS Access 2003 with Microsoft Jet 4.0 OLE DB Provider

linked-serverms accesssql-server-2008-r2

I can however list the tables…

If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables.

But if I run

Exec sp_columns_ex 'LSTest', '<one of those table names>'

I get an empty list, and if I try to query them I also get nothing.

If I go into the properties of the Linked Server I have the Be made using this security context and I use the Remote login: and With password: for the workgroup file that I would normally use in MS Access.

If I try to script a query by right clicking the table name in the linked server, instead of a select statement I get the following:

-- [LSTest]...[Tablename] contains no columns that can be selected or the current user does not have permissions on that object.

Best Answer

1. Set your SystemDB File in the Registry

Note that you can only specify ONE of these damn things.

On 32-bit Windows

If your Windows installation on which your SQL Server instance installed is 32-bit which is doubtful...use the following key to set the path to your MS-Access Workgroup file (*.mdw):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

On 64-bit Windows

On the other hand if your Windows installation on which your SQL Server Instance is installed, is 64-bit, (more likely) set the path to your MS-Access Workgroup file (*.mdw) here:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0\Engines\SystemDB

2. Create your linked server to MS Access

USE [master]
GO

-- Create a new linked table
EXEC sp_addlinkedserver
@server='LinkedServerToMSAccessMDB',
@provider='Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'E:\DB_local\MyMDB.mdb'
GO

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='LinkedServerToMSAccessMDB',
@useself='FALSE',
@locallogin=NULL,
@rmtuser='someuser',
@rmtpassword='daspasswd'
GO

3. Make sure that your database credentials are passed to the Workgroup File (.mdw) when running a query from the (.mdb)

Now if you want to make sure that the password is being passed correctly after running this....in the Object Explorer panel, in <server-name>\Server Objects\Linked Servers, right click it and click refresh...your new LinkedServerToMSAccessMDB should now appear in the list, right click it and select Properties

In the window that appears next in the Select a page panel, click Security and then in the For a login no defined in the list above, connections will: radio button list, select the Be made using this security context: (if you don't want to be particular about what network users have access to it....otherwise use the mappings) and then in enter the username and password you would normally use to access MS Access using your .mdw file in the Remote login: and With password: text boxes.

4. Run a Query

After you click OK you should now be able to run a select on the .mdb file so that you can create a VIEW in a schema for some specified user.

SELECT [Field1]
  FROM [LinkedServerToMSAccessMDB]...[T_Table1]
GO
Related Question