Search code examples
sql-serversql-server-2008linked-server

Expose Microsoft Access database over SQL Server using linked server

We have one .exe application that uses one .mdb Microsoft Access database.

I need to access data inside access file over Microsoft SQL Server. We have SQL Server 2008 R2 Enterprise that has linked server pointed to this Access file and I can run select / update query using SQL statement.

SELECT * FROM [LinkedServerAccessDB]...[SomeTable]

How can I configure that this linked server, my Access database, is directly published as "Database" when some application tries to connect to my SQL Server using SQL Server instance name, and username and password. Which "database name" should I use to use directly linked server ?

Thank you

Solution

  • It sounds like you want your MS Access Linked Server object available as a database (i.e. available in the 'Databases' folder in SSMS). This isn't possible, directly.

    Suggest you create a new SQL Server database that mimics the name of that Access database. Map a user to that login you've got above. Allow the user to run queries against the linked server.