I was doing this yesterday to query our call logs which are stored in an Access database. I figured it would be really nice to be able to query our databases from one application. The software that came with our phone system to generate reports from the call logs is also not that flexible. It’s just much easier for me to run reports using SQL Server Query Analyzer plus we can join the tables from other databases with it easily if we need to.

Just open Query Analyzer and execute this command:

sp_addlinkedserver 'LinkedServerName', 'ProductName', 'Microsoft.Jet.OLEDB.4.0', 'path_to_access_database_file'

Example:

sp_addlinkedserver 'Server1_NBXdb', 'MS Access', 'Microsoft.Jet.OLEDB.4.0', '\\server1\nbxcdr.mdb'

To query the database, use this format:

select * from  openquery(linked_server_name,'select * from table_name')

or

select * from linked_server_name...table_name

For more information, please visit this website where I got this information from: Configuring a Linked Microsoft Access Server on SQL 2005 Server