Setting up Access as Linked Server

You can establish an Access database as a linked server to Microsoft SQL Server and query it with OPENQUERY. Here are some setup examples:

EXEC sp_addlinkedserver
@server = ‘Warehouse’,
@provider = ‘Microsoft.Jet.OLEDB.4.0′,
@srvproduct = ‘OLE DB Provider for Jet’,
@datasrc = ‘e:\access\warehouse.mdb’
GO

SQL Server linked server setup to Access 2007 database:

EXEC sp_addlinkedserver
@server = ‘NYCInventory’,
@provider = Microsoft.ACE.OLEDB.12.0′,
@srvproduct = ‘OLE DB Provider for ACE’,
@datasrc = ‘e:\access\nycwarehouse.accdb’
GO

This is how you can query a linked Access database:

SELECT * FROM OPENQUERY(Warehouse, ‘SELECT SKU, Item, Description, Quantity FROM dbo.Inventory’)
GO

source:SQL USA

Advertisement