0

I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 table. So my question is how to build a linked server (from SQL Server) connected only to one specific table in Access?

2 Answers 2

1

Setting up an Access DB as a linked server gets pretty ugly, but if you absolutely have to do it, you can split that one table into a separate database file, and create a linked table pointing to it from the original Access DB file. Then set up a linked server in SQL Server pointing to the Access database file with that one table.

Better option: Build an SSIS package that periodically imports/synchronizes data from your Access database.

Best option: Use the upsizing wizard in Access to migrate the data to SQL Server, and continue using Access as the front-end application, but not as the storage engine.

0

You could create a view only showing the desired table. (Or perhaps a stored procedure). Only give the user(s) read rights to the view. Lock down the linked server.

1
  • I am unsure that is enough, depending on how locked down it is supposed to be. A user with rights to the linked server can circumvent views and stored procedures and go directly through the linked server to select whatever he has rights to see.
    – RLF
    Sep 13, 2013 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.