Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We have a database at cadets that uses Access for all functionality and data. It's starting to get slow and not always update. I know that Access 2003 and higher (not sure on older ones) lets you connect to an external data source for the data, and use reports and forms just from access

If I converted all that data to MSSQL for the data and just pointed Access to the server, would multi-user performance and reliability improve, get worse or stay the same? What are the pros and cons for a small database with less than 5 users at a time?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Attaching an Access "front end" containing queries, forms, and reports to data tables stored in a SQL Server database is very straightforward. There is a tool called the "Microsoft SQL Server Migration Assistant for Access" that transfers an existing Access database to SQL Server.

It's very questionable though whether you would benefit from doing this. A "small" database with less than 5 simultaneous users should not have any performance or reliability problems running in any version of Access back to Access 97. I personally have over 200 installations of a very complex multiuser Access system, many running for more than 10 years, each installation with as many as 25 simultaneous users, with no particular problems.

Having said that, SQL Server is certainly more bullet proof than the Access engine, but it shouldn't be necessary for the kind of small application you describe. The only real issue with Access reliability is network reliability. Dropped or inconsistent connections to the network can cause database corruption but that corruption is almost always easily fixed just by opening the data file in Access and allowing it to automatically repair itself.

Performance of SQL Server is definitely going to be better with hundreds of simultaneous users but with 5 users most functions will be just as fast or even faster in Access. Keep in mind that even though 5 people may have the application open, the only time they are actually transacting with the database is when they load data into a form or report (running a query) or save changes to the data. Observing their work you'll almost certainly find that even having two actual simultaneous actions on the database isn't that common.

Almost all performance problems with Access applications are due to incorrect design, starting with the data structures and continuing with bad query and form design, use of macros (don't ever), and/or bad VBA code. Most new Access users are unaware of the need to split a multiuser application into separate front end and back end databases. This article explains why this is necessary and exactly how to do it. It's fairly easy - there is even a wizard in Access to help you do it.

If you can't explain or resolve specific performance issues with an Access application, you should probably seek help here or on other forums by describing the exact nature of the problem. There are also some fine books available - particularly the Access Developer's Handbook by Ken Getz, et. al. While quite old (2002) this is the "bible" for Access design and still 99% applicable to newer versions.

Good luck!

share|improve this answer
    
Very good writeup! My main issue is that the database constantly gets "updated" as well, where we email the current .MDB file to the maker, and he makes changes and emails back. So I don't know if that would work with SQL server right now, but still... Great post/info –  Canadian Luke Jan 14 '12 at 8:29
    
Thanks! Splitting the application into "front end" with forms, reports, etc. and "back end" with data only makes updating much easier. The data file stays on your server, the front end file on the client workstation. As long as the changes to the program don't involve the data table structures, only the front end is replaced. You don't need to send files to the programmer - they just update their copy of the program front end and send it to you. You can keep working continuously with the data file. When an update arrives you just copy it to the client machines with minimal disruption. –  Dave Becker Jan 14 '12 at 16:23
    
So even if I have linked tables and send in just the front end, it will work? What about when he updates tables too? –  Canadian Luke Jan 14 '12 at 16:51
    
"So even if I have linked tables and send in just the front end, it will work?" Absolutely. "What about when he updates tables too?" Then you have to stop using the data file (back end) while he modifies it, as you have been doing - but that should be much less common. If you're paying someone to create this data system and that person hasn't already split it into front end and back end, you are not getting a professional quality design. –  Dave Becker Jan 14 '12 at 17:23
    
It's for a volunteer organization, he's the boss of another division of the group. It's 100% volunteer, but what is being used nationally –  Canadian Luke Jan 14 '12 at 17:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.