Access database as linked server in SQL server RRS feed

  • Question

  • Has anyone worked with an Access database as a linked server in a SQL server environment?  More specifically I am curious about the use of SQL server as a kind of "shell" around an Access database which might allow enhanced security and greater reliability (and even performance) over a WAN. I know the best solution is to simply convert the database to SQL but the database I am considering this approach with is from an outside source and for various reasons must remain in .accdb format.  I'm thinking very speculatively but wondering if anyone has considered or done such a thing.  Thanks!


    Friday, February 8, 2019 6:31 PM

All replies

  • Bruce - I can't conceive of that setup. SQL Server is not a shell in any way that I know of. The Access database can stay a ACCDB (or better, ACCDE). Using SQL Server as the back end will take advantage of SQL Server's more robust security, but you still have to set it up. And if the user can access the raw linked tables through your FE, your security is still not tight enough.

    The performance should be better if properly constructed, and, of course, the reliability will be greatly increased. This article is a great place to start. FMS - SQL Server

    Bill Mosca

    Friday, February 8, 2019 8:41 PM