Expose Microsoft Access database over SQL Server using linked server

Question

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

+1 to @p.campbell's answer below, but I'm curious why the SQL Server component is required. Is there a reason you don't want to connect to the Access file directly from the application? — Bryan
– Bryan, Commented Sep 21, 2011 at 19:31
Hi, yes few applications that needs to access data from this Access file, does support only directly MSSQL, and I can not change all data to MSSQL it needs to stay original in current Access file. Thank you — Hrvoje Kusulja
– Hrvoje Kusulja, Commented Sep 24, 2011 at 7:20

p.campbell · Accepted Answer · 2011-09-21 18:35:25Z

3

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.

answered Sep 21, 2011 at 18:35

p.campbell

101k70 gold badges264 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hrvoje Kusulja Over a year ago

Ok, when I create new SQL database, how to I create mimics and map login to Access then ? My application that needs that support only standard MSSQL , and i need (for other reasons) for data to stay in current shape/access database. Thank you

Conrad Frix · Accepted Answer · 2011-09-21 19:08:15Z

1

You can use CREATE SYNONYM like so.

USE ASQLServerDB
GO

CREATE SYNONYM Sometable FOR LinkedServerAccessDB...SomeTable

Once this is done you can write SELECT [...] from SomeTable as though it was a member of the database ASQLServerDB

I was only able to get it to work at the object level so you'll need to do this for each object you want to expose. You could create an empty database that just contained these Synonyms if you wanted to get that "published as a database" feel.

 --This doesn't work
CREATE SYNONYM Sometable FOR LinkedServerAccessDB...

edited Sep 21, 2011 at 19:08

answered Sep 21, 2011 at 19:02

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

1 Comment

Hrvoje Kusulja Over a year ago

Hi, i have created manually synonim for one table now, thank you a lot, and select and update query works normally. However my applications looks inside "Tables" not Synonyms so still can not use data, for example in Sharepoint with BCS or some other application. Can somehow synonym be presented in "Tables" part in MSSQL management studio ? thank you

Collectives™ on Stack Overflow

Expose Microsoft Access database over SQL Server using linked server

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related