Tutorial :Proper way to program a Microsoft Access Backend Database in a Multiuser Environment



Question:

There is a prevailing opinion that regards Access as an unreliable backend database for concurrent use, especially for more than 20 concurrent users, due to the tendency of the database being corrupted.

There is a minority opinion that says an Access database backend is perfectly stable and performant, provided that:

  1. Your network has no problems, and
  2. You write your program correctly.

My question is very specific: what does "Write your program correctly" mean? What are the requirements that you have to follow in order to prevent the database from being corrupted?

Edit: To be clear: The database is already split. Assume less than 25 users. I'm not interested in performance considerations, only database stability.


Solution:1

If you’re looking for great example of what programming practices you need to avoid, number one on the list is generally that of NOT running a split database. Number two is not placing the front end on each computer.

For example the above poster had all kinds of problems, but you can darn your bet that their failing was either that they didn’t have the databae split, or they weren’t placing the software (front end) on each computer.

As for the person having to resort to some weird locking mechanism, that’s kind of strange and not required. Access (actually the JET data engine, now called ACE) has had a row locking feature built in since office 2000 came out.

I’ve been deploying applications written access commercially for about 12 years now. In all those years I had one corruption occur from ONE customer.

Keep in mind that before Microsoft started pushing and selling SQL server, they rated the JET database engine for about 50 users. While my clients don't have problems, in 9 out of 10 cases when someone has a probem you find number one on the list is that they failed to split the database, or they’re not installing the front in part on each computer.

As for coding Techniques or tips? Any program design that you build and make it in which a reduced number of records are loaded into the form is a great start in your designs. In other words you never want to just simply throw up a form attached to a large table without restricting the the records to be loaded into the form. This is probably the number one tip I can give here.

For example, it makes no sense to load up an instant teller machine with everybody’s account number, and THEN ask the user what account number to work on. In fact I asked a 80 year old grandmother if this idea made any sense, and even she could figure that out. It makes far more sense to ask the user what account to work on, and then simply load in the one customer.

The above same concept applies to a split database on a network. If you ask a user for the customer account number, and THEN open up the form to the one record with a where clause, then even with 100,000 records in the back end, the form load time will be near instant because only ONE RECORD will be dragged from the customers table down the network wire.

Also keep in mind that there is a good number of commercial applications in the marketplace such as simply accounting that use a jet back end ( you can actually open simply accounting files with MS access, they renamed the extensions to hide this fact, but it is an access mdb file).

Some of my clients have 3-5 users with headsets on, and they’re running my reservation software all day long. Many have booked more then 40,000+ customers and in a 10 year period NONE of them have had a probem. (the one corruption example above was actually on a single user system believe it or not).

So, I never had one service call due to reliability of my access products. On the other hand this application only has 160 forms, and about 30,000 lines of code. It has about 65 highly related and noralized tables (relations enforced, and also cascade deletes).

So there’s no particular programming approach needed here for multi user applications, the exception being good designs that reduce bandwidth requirements.

At the end of the day it turns out that good applications are ones that do not load unnecessary records into a form. It turns out that when you design your applications this way then when you change your backend part to SQL server you find this approach results in very little work needed to make your access front end work great with a SQL server back end.

At last count I think here's an estimate of close to 100 million access users around the world. Access is by far the most popular desktop database engine out there and for the most part users find they have trouble free operation.

The only people who have operational problems on networks are those that not split, and not placed the front end on each computer.


Solution:2

The only compelling answers so far seem to be to reduce network traffic, and make sure your hardware cannot fail.

I find these answers unsatisfactory for a number of reasons.

  1. The network traffic position is contradictory. If the database can only handle a certain amount of network traffic, then people need sensible guidelines to gauge this, so they can intelligently choose a database that is appropriate.

  2. Blaming Access database crashes on hardware failures is not a defensible position. Users will (rightly) claim that their other software doesn't suffer from these kinds of problems.

Access database corruption is not an imaginary problem. The people who regularly suggest that 5 to 20 users is the upper practical limit for Access applications are speaking from experience.


Solution:3

Also see Corrupt Microsoft Access MDBs FAQ Which I've compiled over the years based on newsgroup postings and predates Allen's page. That said my clients have had very few corruptions over the years and have never lost data nor had to restore from backup.

I'm not sure what "write your program correcly" means in this context. I've read a few postings indicating this but it's more the implementation aspects. As Albert has pointed out you have to split the database and give each user their own copy of the FE MDB/MDE. You can't access a backend MDB over a wireless network card as they are too unstable. Same with a WAN unless the WAN is very fast/wide and very stable. We then suggest upszing to SQL Server or using Terminal Services/Citrix.

I have several clients running 20 to 25 users all day long into the system. One MDB has 120 tables while another has 160 tables. A few tables have over 600,000 to 800,000 records. The one client had 4 or 5 corruptions in five or seven years. We figured out the cause of all but two of those. And they were hardware related in one way or another. At least one of these apps should've been upsized to SQL Server. However that was cancelled on me by a Dilbert's PHB (Pointy Haired Boss).


Solution:4

Very good code (wrapped in trasactions with rollbacks) we had a call center with over 100 very active users at a time back in Access 97 days. Another one with VB 5 front-end, Access Jet on portables that RAS (yes the old dial up days) to a SQL Server 6 database - 250 concurrent users.

People using the wizard to link a form directly to a table where the form is used to make edits ... might be a problem.


Solution:5

Uncompleted transactions e.g a recordset that does not get closed properly and a break in network connection for any reason while a database is open (have seen the power saving features of NIC causing corruption) are my number one causes


Solution:6

I don't believe the number of users is a limitation with MS-Access Jet Engine. My understanding is that the Jet Engine queues up concurrent maintenance transactions to apply them 1 at a time (like a printer queue does to print print jobs). Via ODBC connectivity, and an intelligent user-application program that manages the record set sizes, locking of records open for edit, and only maintains DB connections long enough to retrive a record and save a record, that puts little strain on the jet engine. I look at mdb file as tables. You can conceivably have 100s of these in one database, or more. The SQL querying to these tables would be by random access, and the naming convention of the mdb files lets the SQL query built in the applciations program which table (mdb file) to access. MS-Access databases can be 10s 100s or 1000s of Gigabytes this way and run smoothly. Proper indexing and normalizing of data to revent storing of redundant data also helps. I've never run into a database crash or concurrency issue with MS-Access and ODBC and Win32 Perl GUI interface driving the applciation. I use no MS-Access Objects other than tables, indexes, and perhaps views/queries. And yes, I store the database on a dedicated PC and install my applications software on each workstation PC.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »