[Webinar] Learn how to a build a cloud-first strategyRegister Now


Will an MS Access application typically run faster using a SQL Server backend or MS Access backend

Posted on 2009-05-01
Medium Priority
Last Modified: 2012-06-21
I know there are many factors involved that can affect the answer to this question but I am after a bit of a general reply.
I have an application that has 4 very heavy duty users. Each user needs to call up and select a customer record from a table, currently containing about 4000 records. The form then displays the fields from that master table on a form with about 6 subforms. Each subform displays a list of related records taken from various tables. Each related table contains somewhere between a few hundred and up to 20000 records. The user needs to just from this tabbed form (MS Access 2007 tabbed forms forat) to any one of half a dozen other forms that display other records in other formats. The processing that takes place each time the user selects a new record or changes to one of the other tabbed forms means it is currently taking between 2 and 5 seconds to requery the data. Then there are about 6 to 8 other users who intermittently call on and edit various fields in the master table (low drain on resources).

If I convert from a MS Access backend on the server to an SQL Server 2005 or 2008 Express backend on the server and ONLY make the changes to the code necessary to link to the alternative backend (no stored procedures etc), should I expect that the application will run faster, slower or no noticable change - generally speaking?
Question by:Rob4077
  • 3
  • 2
  • 2
  • +1
LVL 60

Expert Comment

ID: 24278960
You should expect better concurrency and transaction handling if you start using the SQL Server express backend, that is for sure.

Author Comment

ID: 24279047
Thanks for the comment chapmandew. Will that translate into more speed? What is happening is that the users are now starting to complain that the system is too slow. I therefore need to build an archive system and only keep the most necessary data in the main tables but was wondering if, by changing to SQL Server BE I could delay building the archive system due to an inherent speed increase
LVL 60

Accepted Solution

chapmandew earned 1000 total points
ID: 24279093
I would say that you're almost certainly going to see a speed increase.  MS Access is OK, but SQL Server is a true db engine, with a great query engine that is going to optimize queries very well.  Of course, it depends on your design, index structure, etc...but you're putting the odds in your favor MUCH more if you switch to the SQL engine.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1000 total points
ID: 24279129
You almost answer this yourself in your opening line. :-)

I think you know that a blanket answer isn't possible.
In theory, if your server hardware is beefy enough and your network fast enough then yes - you should see an improvement.
It could be .001 seconds faster or 2 to 5 seconds faster.
The fact that you only have 4 active users, 4000 rows in your main work table and yet a single operation is taking up to 5 seconds implies you might have deeper problems.  And deeper problems aren't solved by moving platform - just postponed.
If I have a big pile of stinky manure in my garden and I pay £20,000 to build a lovely shed around it - I still have a big stinky pile of manure in my garden.
Your ultimate answer to your question is - just do it.
It doesn't take long for a very basic conversion.
But if you see big performance gains (but know in the back of your mind that your database schema is not correct) then you could be back in this position again in due course - except with no platform to move to.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24279193
And not to harp on unnecessarily... but I can't say it often enough... MS Access has no database engine.
You mention Access 2007 - so if you're using ACE (Access Connectivity Engine also known as the catchy "Microsoft Office Access database engine 2007" which really refers more to the library which replaces DAO) - which is less a generic name than Jet was and clearly points to Access - but ACE is now the database engine for Office.  Access is still just a UI development environment with database integration abilities. :-)

Author Comment

ID: 24279278
Thanks for the comments. Understand what you're saying and am now sure I know what I need to do. Thanks again
LVL 11

Expert Comment

by:Goodangel Matope
ID: 24279302
I am a firm believer in the magic of SQL Server, AND the magic of indexing. I suspect that (considering the size of your database, which is small) you have a problem with fields not being indexed. I abandoned Microsoft Access quite a while ago, but I have realised that almost any database will have its performance enhanced by adding indexes to join fields. Indexing your tables will most certainly improve your performance if you do it properly.

I recommend that you move to SQL Server because it is more reliable and robust. It probably will perform better than the access database, but I will recommend that you look at the table structure as well.

Author Comment

ID: 24279501
Thanks Goodangel, appreciate your comments. I think the problem goes beyond indexing.  I have spent considerable effort making sure indexes are in place for every field that is used as a link or a lookup. If anything I may have too many indexes. I think the problem lies with some of the queries I have had to use - I know that points to poor design but fortunately I can't take all the blame for that.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question