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.
