Hi,

I'm trying to figure out a way to get our small office (5 or so users, 2-3 concurrent) Access program significantly faster. We are having issues with performance over our local network. Here are the situations and options:

Program: Access front and back end, about 1 GB, maybe 30 tables or so, all but one are either very small or a few thousand records. Linked to a smaller 30 mb table we copy to our web server. Written in VBA 5 or 6 (someone else usually works on it), it's in a .mdb Access 2000 format. Runs on the client computers from a shared folder on the server.

Server: Windows Server 2003 Small Business (which is SMB 1 only), 32 bit Pentium D processor. Mirrored RAID drives, it is pretty much just a file server.

Network: 100 Mbps through a network switch and router, although the server and most clients have Gigabit network cards. I got about 25 Mbps reading a table on the server from a client this afternoon.

Clients: 4 Windows 7 machines, 2 XP machines used less often.


Main Problem: slow. For example, reading an almost identical table on our website returns results almost instantly while our Access program takes a couple seconds every time the program queries. Also, we're worried about the database becoming corrupt after it bloats, which it has once or twice. It's probably time to upgrade.

What I've Tried: we tried splitting the database at one point, but it was still slow. Today I installed MySQL on my local computer and ran a statement in Visual Studio pulling 1000 records out of the same tables from three databases. On the Access database we have on the server, it took about 1900 ms. On a copy of the db on my workstation, it took about 120 ms. On the MySQL database, 25 ms. Now, linking to the MySQL database from Access took around 360 ms (thought it might be faster).

Options:

1) Fix Access as-is so that it is quick and doesn't corrupt.

2) Upgrade server to Windows Server 2008 and see if SMB 2 is faster in this situation. WS 2003 has also been on the machine for years, a wipe could do it some good.

3) Upgrade network to 1000 Mbps - don't think this is the bottleneck

4) Change backend to MySQL

5) Switch entirely to a Windows Forms application with MySQL serving as the backend.

6) New server hardware in combination with the above.

I'm confident enough to do any of this although I'm no seasoned pro, please give me your advice.

I would begin by having a copy of the front-end installed on every client machine.

Apart from that, there can be many reasons why an application is slow but from what you describe, I don't believe any of the improvements you're considering will bring any huge improvement of the performances, except perhaps for using a "true" server and rewriting the application to take advantage of it (stored procedures, server-side cursors, etc.).

In such a case, I would begin by examining how I can improve the way the application works as it is. Have a look at: Use Microsoft Jet's ShowPlan to write more efficient queries | TechRepublic and Microsoft Access tips: Surviving subqueries.