Slow access to Access Database after Server Migration

Are you running Windows Server Update Services on it? If you are then it's probably downloading a large amount of updates for clients.

After hours of struggling with this problem I finally found not one, but two solutions! I'm using SQL Server 2008 and Access 2000, but I guess the solution is the same.

Solution 1: Use OPENDATASOURCE

Use this approach when you will be accessing data on the linked server infrequently:

select * from 
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Data Source=C:\MyAccessDB.mdb;User ID=Admin;Password=;Jet OLEDB:Database Password=MyDBPassword;')...MyTable

For this to work you also need to turn on the Ad Hoc Distributed Queries option:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Solution 2: Use Linked Server

As you already stated, the linked server doesn't seem to work. However, there's a small gotcha that makes everything work smoothly; you need to specify the database password in the provider string like ;PWD=password.

exec sp_addlinkedserver 
    @server = 'TestLinkServer', 
    @provider = 'Microsoft.Jet.OLEDB.4.0', 
    @srvproduct = 'Access',
    @datasrc = 'C:\MyAccessDB.mdb', 
    @provstr = ';PWD=MyDBPassword'

exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'TestLinkServer',
    @useself = 'FALSE',
    @locallogin = null, 
    @rmtuser = 'Admin', 
    @rmtpassword = null

GO

Now you can query your Access database as a linked server:

select * from TestLinkServer...MyTable