How do I setup a linked server to connect from SQL to an Access or Excel data source

Description

How do I setup a linked server to connect from SQL to an Access or Excel data source

Disclaimer

These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). Before making changes, backup all system and application DBs required for a full restore.

Resolution

To create a linked server on a SQL Server, either 32-bit or 64-bit, to connect to Access or Excel. use the Microsoft Office 12.0 Access Database Engine OLE DB Provider.

Access Example:

General tab:
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: Access
Data source: path to access file (xxxx.mdb)

Security tab:
For a login not defined in the list above, connections will:
select Be made using this security context:
Remote login Admin
With password leave blank

Excel Example:

General tab:
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: Excel
Data source: path to Excel file
Provider string Excel 12.0

Security tab:
Select Be made without using a security context
If you receive a test connection failed message, select Yes to create the linked server anyway, and then do the following:

1. Run this in a Query Analyzer window on the SQL Server where you are trying to create the linked server:

   USE [master]
   GO
   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

2. Run this in a Query Analyzer window on the SQL Server – the ad hoc distributed queries needs to be on:

   sp_configure ‘show advanced options’, 1
   RECONFIGURE
   EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
   RECONFIGURE
   GO

3. Close SQL Server Management Studio and re-open it.
4. Stopping and restarting the SQL Server service may be required.