Create a Linked Server to an Access Database

On a recent project I've had the need (unfortunately) to pull data from Access into SQL Server. We implemented this using Linked Server to query the Access database. In case I need to find it again, here's the code...

The Linked Server can be created using SQL Enterprise Manager, but we needed to script it.

USE MASTER
GO

-- Remove linked servers and drop associated logins
EXEC sp_dropserver 'Link_MyDatabase', 'droplogins'

-- Create a linked server
EXEC sp_addlinkedserver 'Link_MyDatabase', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'D:\MyDatabase.mdb'

-- Provide login credentials for the access database (no username or password required in this case)
EXEC sp_addlinkedsrvlogin 'Link_MyDatabase', 'false', NULL, NULL, NULL

To query the database, we can add the linked server to the front of the table name (no database name or owner name required). Sometimes we needed to query around bad data before it gets to the ODBC driver to prevent data corruption issues and ODBC driver errors. Date data proved particularly troublesome in this regard. To do so, we use an OPENQUERY statement, which runs the query on Access before it gets to the OLEDB driver.

-- Query data directly
SELECT *
FROM Link_MyDatabase...MyTable

-- Query data using OPENQUERY to avoid invalid date values
SELECT *
FROM OPENQUERY(WXIS_OPS, 'select * FROM MyTable WHERE BadDate > #01/01/1900#')