SQL Server Linked Table in Access DB

Question

I'm wondering if/how an SQL Server would be impacted by an Access DB that has ODBC Read-Only linked tables from that SQL Server on it.

If a form event triggers a query on one of those linked tables, is that query hitting a local version of that linked table, or is it going to impact the overall performance of the SQL Server? I have about 30 users simultaneously using an Access Front End, linked to an Access Back End, and have added functionality that draws on several of our SQL Server's tables; I just can't allow that many users to be hitting the server with query on top of query. It would bog down the SQL Server immensely.

If you have any ideas on how to best proceed, let me know!

Thanks,

• LowlyDBA - John M: It will impact - how much totally depends on your queries, data size, hardware, etc. If 30 users is a scary idea, though, you may need to re-think your architecture as that generally shouldn't be an issue for SQL Server (I'm assuming the queries can't be that bad if this thing is built off Access, but again YMMV). You need to do some testing to find all of this out.

Answer 1

Yes, Access on the client machine will connect directly to the linked tables located on SQL Server (or whatever ODBC back-end you're using). If you're dealing with forms and reports bound directly to these linked tables, it shouldn't cause any major performance problems. If you've written more complex queries within Access, then performance can depend on how well those have been optimized.

We've got a number of small Access "apps" running against SQL Server. They're great for when you just need to bang out a simple CRUD tool for a few people in an hour or two. Haven't had any real performance problems with them, though occasionally we run into a lock being held longer than it should.

My advice would be to take all the tables stored in that Access back-end and migrate them to SQL Server as well. Shared Access data files tend to be awful for performance and reliability.