Software

Ask something
Discussion Locked

Problems accessing MS Access Database

0 helpful
3 replies

by lstanfield

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
My team uses an Access Database over our network, and we are having an issue with a corruption flag. After 15 to 20 users have logged in, a corruption flag will be set which disallows further logins. The data isn't actually being corrupted, and those who are already in can stay in and use it without problems indefinitely. In order to correct this, we have to get everyone out and run a "compact and repair" operation, which resets the flag and allows access again. Unfortunately, once again withinminutes, the flag will again go up, and anyone who isn't fast enough to have logged in already will not be able to get in. This "window of opportunity" has gotten increasingly shorter in recent days. It should be noted that this problem only recently became this severe: we have used this application for many months, with this issue being a rare occurrence until the last few weeks. We are stumped: what can cause this and how do we prevent it?

3 replies

by charles byrne

The Jet Database Engine that runs Access in theory, can handle 255 concurrent users, but I've seen performance hits around 10 concurrent trying to write to the same database. The database slows down as it tries to send the copy of database across the network.

If the database is complete, meaning you won't be doing any further development for a while, you may want to consider splitting the database. When you split the database, the tables are put in a back end table on your network and the other objects are in the front-end and distributed to the users as a local copy. The front-end is linked to the back-end.
This greatly reduces network traffic and concurrent connections depending on your DB settings. It's also good when there are users with different versions of Access using the DB. Access has an add-in that will assist with the splits. One caveat to this is if you move the back end you need to re-link all the front-ends.

Another method you may want to consider is database replication. I've never done it, but it allows you to have two or more copies of the db that synchronize data with the master copy. One con is that you don't have immediate updates to your information, which isn't good.

Of course if you have Access 2000 or greater you can make a client server database using the upsizing wizard which makes Access the front-end and MSDE(SQL Server) the back-end. Although Access makes this relatively easy to do, it is a true client / server environment so you'll need the back end put on a true database server not just at a network file location. You'll need to have familiarity with SQL server, how it handles autonumber fields, reserved words, triggers, datatype conversions, etc.

I'd try the database split first. Then if I was familiar with SQL server, had the server resources, I'd prepare the DB for an upsize.

Hope this helps,
Charles

by lstanfield

Thank you! The database has been split, which helped with performance, but unfortunately didn't relieve the corruption flag issue. Now the admin has split the tables as well, and it turns out that only one of them seems to have an issue. He's now in the process of upsizing to SQL, which will hopefully resolve all the problems.

This discussion is closed to new replies.