Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm overhauling the entire IT infrastructure for a small office. They had a Dell T410 doing nothing but serving as a Win2k8 file share, so I figured I could replicate its functionality on an unused desktop running Vista while I took the server down for a rebuild.

That's all fine and good, but one thing they were doing was simultaneously accessing an Access database off of the share. It worked, slowly, while on the T410 but once I migrated it to the temporary server only one user can use it at a time-- additional users drag it to an unusable halt. There are 30-60 second pauses for queries. Everything else runs well enough.

This was something my predecessor had to deal with, and the office ladies are saying he had done something on the server to lessen the query time to 5-10 seconds. They instantaneously run in single-user mode, but there is no documentation of what he did to mitigate this for multiple users. The server had no RAMDISK, RAID setup or anything special from what I could tell.

First I thought the hard drive might be slow so I set up a RAMDISK and am hosting that one particular file on it. Single-user access is now blazing fast but multi-user access is still just as slow. Next I thought the network card might be a bottleneck but I've watched the resource monitor and nothing is anywhere near capping out. My next thought was to blame hardware/Vista but it's a Dell somethingorother Core 2 Duo with 3GB of RAM, and Vista Business is the only OS I have a spare license for. Should be enough for the job at hand.

I've seen a lot of similar complains about poorly-designed Access databases being unusable in multi-user environments, followed by suggestions to split the database into front-ends and back-ends but I'm not willing to spend much time tweaking Access considering my next project after rebuilding the server is redesigning the entire back-end in SQL Server. Plus, the last guy was able to semi-fix the problem without overhauling anything (this much I do know). In the meantime though, this is killing productivity.

What might have changed between the old server and the new? Are there any simple things I can change to bring the query time back down to a reasonable delay?

share|improve this question
Windows' Desktop OS's don't allow as many simultaneous connections as a Server OS, how many users are trying to use it at once? – Ƭᴇcʜιᴇ007 Feb 7 '13 at 18:31
Most of the day, 2 users at a time. There are rare occasions where there are up to 4. Forgot to mention, the entire network is wired, and the temporary server is hooked up directly to the 10/100 switch, same as the old server. Thanks – johnny Feb 7 '13 at 18:33
Are they all just loading the same DB into Access and editing it directly, or is it a Frontend/backend setup? If it's FE/BE, Is there any reference in the Frontend's code to the BE server? If so, is it still accurate now that you moved it to a new path? – Ƭᴇcʜιᴇ007 Feb 7 '13 at 18:39
No FE/BE; it's just a monolithic .mdb database. I knew going into this that they were going to have performance/scalability issues with their current setup, hence my push for a proper redesign. They used to open it via the G: drive, which I have since remapped on all the workstations to the respective location on the temporary server. The database works, just not in a productive manner. – johnny Feb 7 '13 at 18:45
This is exactly what splitting will solve. It will be faster and more stable. Splitting is no more complicated than running a wizard in Access and distributing the front end to all your users desktops (making sure it points to the right backend file) – Brad Mar 19 '13 at 14:21

Splitting into fe,be is not complicated. Then link tables from be. These are deployment basics for ms access. If you are facing msql build then splitting a database will be very easy.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .