SQL Server Performance Forum – Threads Archive
MS Access Front End -Issues
Hello I was wondering if anybody knows the pro’s/con’s of using Ms Access front end and SQL database. I am wondering if I should use pass-through queries or Link the tables? Does anybody think that it will make a difference?Thanks
pass-through queries allow you to call stored procedures and have exact control over the SQL statements being sent to SQL Server. This can be quite an advantage… Link tables are easier to work with in Access, but at the expense of less control over performance and security. Cheers
Twan
From what I recall MS Access is very poor (or at least it used to be) at accessing data in sql. I ran some tests on a couple of years ago in order to prove to my employers at the time that it was a bad idea. There were a number of areas where it would run multiple operations on single rows rather than a single operation on multiple rows for instance. If you do need to use it then I’d suggest testing some scenarios with profiler running just to check how the version you’ll be using works.
Avoid using linked tables. Access isn’t very clever when it comes to reading from linked tables (sometimes loads the entire table into client’s memory when there is no need). Using pure pass-through queries should be okay, especially if using Stored Procedures. Tom Pullen
DBA, Oxfam GB
It depends on how you use Access as a front-end. If you use Access as a front-end similar to VB, meaning that use use VBA code to use ADO to approriately call stored procedures in SQL Server, then Access is perfectly fine as a front-end. Using linked-tables and pass-through queries has its drawbacks and certain issues need to be taken into consideration when architecting an application this way. If you have to choose between linked tables and pass-through queries, I would choose pass through queries.
"How do you expect to beat me when I am forever?"
]]>