Backend DB crashes every couple days. Any ideas?

[RKLINE]

We are running an MS Access 2000 backend, 6mb file, not huge, on a network share. The front end is copied to the users desktops with a batchfile whenever there are changes to the front end.

I'm thinking the problem lie in the fact that at any given time we have over 300 users (maybe as many as 400) pushing data to the backend tables, which for the most part, are Access tables.

I read a thread (hey, that rhymes) about the max number of connections, however, we've had over 200 users on this db for a couple years. Just recently, the users desktops were migrated to XP SP2, but I can't be certain that is the problem. Also, we're not running the backend on a desktop, rather, in a shared location on a server, so I don't think that is the issue.

Any assistance is appreciated. And articles to show solutions, also appreciated.

Regards.
Rich.

[Stewart Ross]

Hi, and Welcome to Bytes!

When you say the back end database crashes, can you be more specific? Is it loss of connection to the tables, or what?

Access is not the right choice of back end given the number of users you have; performance and data integrity issues spring to mind when trying to shoehorn the non-server-based Access into use as an application involving hundreds of users. I have no idea how Access will deal with record updates and locks in such circumstances or how they will affect your users.

The norm would be to use an Access front end (just as you have already) and use SQL Server as the back-end database. It is a transaction processing server-based system intended for the larger scale you currently have. Migrating Access tables to SQL Server should be straightforward, and I am sure that it will be in your long-term best interests to consider such a migration.

-Stewart

[Stewart Ross]

Hi. Further to my post above, if you review the specifications for Access (linked here) you will find that the maximum no. of concurrent users Access supports is 255.

-Stewart