Will an MS Access application typically run faster using a SQL Server backend or MS Access backend
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?
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?
chapmandew
You should expect better concurrency and transaction handling if you start using the SQL Server express backend, that is for sure.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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. :-)
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. :-)
ASKER
Thanks for the comments. Understand what you're saying and am now sure I know what I need to do. Thanks again
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.
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.
ASKER
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.