2

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,

Improve this question
1
  • 2
    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.
    – LowlyDBA - John M
    Dec 1, 2017 at 15:34

1 Answer 1

Reset to default
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.

Improve this answer

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.