I am supporting an Access 2000 database. The database is split between user forms (5 different databases for different users) and data (1 database), but it is all in Access 2000. We have 20 users on Access 2000, and maybe 10 users at a time on the web whose database is the same Access 2000 db as the 20 users use.
Needless to say, concurrent user issues exist.

Would you recommend upgrading to Access 2007 or Access 2010? Should i do an incremental upgrade? going from 2000 to 2003, then to 2007, then to 2010?

Our main problems are concurrent users and slow response time. I hope an upgrade to a more current version of Access will help that.

---

Richard Answers:

If you're experiencing a slow database running over a network, upgrading to a newer version of Access will probably NOT solve your problem.

The problem in situations like this is USUALLY due to a network bottleneck. See, if you have 20 users all working on the same backend database, and they're pulling records, ALL of that data traffic is flying across your network. You could start by taking a look at the speed of your network. If you're running an old network, you might want to see if you can upgrade your infrastructure to Gigabit ethernet (wiring, router, network cards, etc.). This will increase your performace a lot.

Also, take a look at the processing power of your server. Many companies set up an old machine as their "database server" and expect it to be fine. It might work for a handful of casual users, but if you've got an underpowered box acting as a de facto database server, it might not be able to crunch the data fast enough for you.

If neither of those solutions help, you might want to take a look and see if you can OPTIMIZE your database for network traffic. For example, if you have users doing a lot of data ENTRY, set up forms that are only for DATA ENTRY. This way when you open up a customer form, for example, Access doesn't have to send all of that customer data down to the desktop. It will just place the user on a new blank record.

Also, if you have reports that are based on big, complex QUERIES, you might want to see if you can simplify them to make them load faster. See, one of the things that a database SERVER program (like SQL SERVER or MySQL) does is it performs all of the data crunching AT THE SERVER. So if you request a query of all of your customers from New York, the server figures it out and just sends you those records. With a NON-server solution (like just an Access database) the local copy of Access has to pull ALL of those thousands of customer records across the wire to your local PC and THEN figure out who the customers from New York are.

So, as you can see, there are a couple of things you can look at. While upgrading to a newer version of Access MAY speed things up a TINY BIT for you, it probably won't help your speed issue. In fact, if your computers are older, it might even make things WORSE. The newer versions of Access have higher system requirements than the older versions do.

I would look at your network bandwidth first, the processing power of your "server" machine second, optimizing whatever you can in your database third, and finally if all else fails consider upgrading to a REAL database server. SQL Server is a little pricey, but there are free solutions available like MySQL. I'll be preparing tutorials on these two topics this summer.

I'll also be putting together a seminar in the near future on the best techniques for splitting your database and optimizing performance. Stay tuned.