6

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.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • proper way for a .mdb or an .adp access database? a native .adp or one that is connected to any variation of ms sql server? – dde Jul 13 '09 at 02:56
  • An MDB used as a backend. The frontend is an MDE. – Robert Harvey Jul 13 '09 at 02:58
  • Where are you getting the "more than 20 users" crap? 20-25 users is the point at which most professional Access developers would start urging the client to upsize to a server back end. This is not to say that it can't be done and can't be done reliably, but the amount of work it takes is much greater than with a server back end. – David-W-Fenton Jul 13 '09 at 22:34
  • @David, I agree with you completely on this point. I only wish we had known how much more work it was actually going to be. When we began the project, it was much smaller than it is today, and had different goals than it does now. When the problems began, rewriting the application was not really an option. It took us awhile to recognize that our best option was upsizing to a free version of SQL Server, and not insisting that our customers get better hardware (a losing argument). Had we made the switch sooner, we could have avoided a lot of pain. – Robert Harvey Jul 13 '09 at 23:04
  • Unfortunately we had too many people telling us that Access was perfectly reliable at this user level, and that we should be looking for other problems, rather than doing the sensible thing. – Robert Harvey Jul 13 '09 at 23:06

6 Answers6

6

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • I'd say you have a more complete, better informed list in this answer. – dkretz Jul 13 '09 at 04:10
  • 1
    Complete, if a little wordy. I was kind of hoping someone would tell me what it meant to do it correctly, instead of telling me how wonderful Access is. – Robert Harvey Jul 13 '09 at 04:27
  • So if I understand you correctly, your recommendation is to design your Access application to use as little wire bandwidth as possible? – Robert Harvey Jul 13 '09 at 04:29
  • Yes after splitting the suggestion is to reduce bandwidth requirements. This suggestion equally applies to single user applications, those split and running on a typical office LAN, or when building applications that pull data from sql server. I have a series of screen shots that layout a sample search screen here that gives some ideas as to how you can keep bandwidth requirements down: http://www.members.shaw.ca/AlbertKallal/Search/index.html Ms-access often gets a bad performance reputation and often it due to people loading up forms/combo boxes with 1000’s of records. – Albert D. Kallal Jul 13 '09 at 06:39
  • "[the engine] has had a row locking feature built in since office 2000 came out" -- but those still using DAO won't be taking advantage of it: see PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60 (http://support.microsoft.com/kb/306435/en-us). Do you know whether ACEDAO uses page- or row-level? Better still, can you tell me where this is documented? Thanks. – onedaywhen Jul 13 '09 at 07:27
  • ...now asked as a question on SO: http://stackoverflow.com/questions/1118219/does-acedao-support-row-level-locking – onedaywhen Jul 13 '09 at 08:11
  • @onedaywhen: bound Access apps can use record-level locking because they aren't using DAO for their data interface. – David-W-Fenton Jul 13 '09 at 22:36
  • Quibble with Albert's post: "only ONE RECORD will be dragged from the customers table down the network wire" Not true -- some index pages will be dragged across the wire in order to determine which particular data page to request for the specific record. If you're retrieving on the PK, this will be a very, very insignificant amount of data, but if you're querying on other fields, it could take more than one index request. – David-W-Fenton Jul 13 '09 at 22:38
  • "The only people who have operational problems on networks are those that not split, and not placed the front end on each computer." - this is pure poppycock, and I have empirical proof. Just this morning I had a team test a database which had been split and the front end distributed to each tester's local machine. The result was massive conflicts, navigation errors, failures to execute procedures that inserted data, etc.. So, no, those are NOT the only two reasons and I came here looking for a useful answer, not an entire page of pontification and bloviation. – DaveInAZ Feb 26 '20 at 21:47
  • As noted, a split database helps a lot. In recent times, we seen a rash of issues. One big reason is windows 10 is FAR more aggressive in power management. So, turning off windows 10 "allow windows" to manage or power off the network card can help. And for sure, access on a network can have problems. So, I don't think the claim here is JUST that you split and you never see corruptions. Today, with SQL express being free, and having a 10 gig limit (as opposed to Access JET backends being 2 gigs), then another really great solution is to adopt the free edition of SQL server. – Albert D. Kallal Feb 27 '20 at 18:39
2

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).

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Tony Toews
  • 7,850
  • 1
  • 22
  • 27
  • Here's what I mean by "Write your program correctly." I have broad personal experience with corruption in Access databases. It is my opinion that an interrupted write is a poor excuse for a database catastrophically crashing. When I explain this to your MVP friends, they tell me that I am incompetent and ignorant, and that I just don't know how to program it correctly. See http://stackoverflow.com/questions/1115385/microsoft-access-2007-power-users/1115424#1115424 – Robert Harvey Jul 13 '09 at 04:59
  • Whereas I have very little personal experience with corruptions as I've seen so little of them. However I've read almost all of the Usenet and Microsoft NNTP newsgroup postings on this topic. What do you mean by "interrupted write?" Hardware failure? User hitting the power switch? Or something in your code? I agree that SQL Server and similar are a better solution in many respects. However frequently IT has a strangle hold on such. And they're more work to setup and administor. – Tony Toews Jul 13 '09 at 04:59
  • Ah, ok, please note that David Fenton is not an MVP. Although for the most part he does post good answers. At times the attitude in his postings do reflect his New York heritage. And we've had this discussion in the newsgroups a while back so he knows where I'm coming from on this issue. David should explain in greater detail what he means. Some of it will be the obvious things such as not pulling down forms based on queries with thousands of records. – Tony Toews Jul 13 '09 at 05:07
  • >>At times the attitude in his postings do reflect his New York heritage.<<-- I appreciate your thoughtful response. When someone responds the way David does I assume he means it. – Robert Harvey Jul 13 '09 at 05:10
  • >> obvious things such as not pulling down forms based on queries with thousands of records. <<-- But why would that cause database corruption as opposed to simply degraded performance? – Robert Harvey Jul 13 '09 at 05:11
  • >>At times the attitude in his postings do reflect his New York heritage<< -- could someone "internationalize" this for me please? I have actually seen that particular sitcom set in the Big Apple, so do rude comments pass for flattery in New York, as being really envious of one aother and falling out at the drop of a hat counts for being 'Friends'? :) – onedaywhen Jul 13 '09 at 07:50
  • >> What do you mean by "interrupted write?" Hardware failure? User hitting the power switch? <<-- It's difficult to blame a crash on the customer's hardware. They will tell you that this kind of failure doesn't happen with their other software. – Robert Harvey Jul 13 '09 at 13:42
  • Robert, Access is the proverbial canary in a coal mine.. And you are correct. This kind of problem frequently doesn't happen with other software. Word, Excel and PPT generally read the entire file into memory and write it back out when done. Obviously Access doesn't work that way. – Tony Toews Jul 13 '09 at 18:15
  • I like the "canary in a coal mine" analogy. It's very apt. But most people don't want to be canaries, and they don't have to be if they use SQL Server Express as a backend. It's frustrating to see well-respected experts like Albert give the impression that Access is bullet-proof, and then blame the customer or the programmer when things don't work out. – Robert Harvey Jul 13 '09 at 18:28
  • Robert, good point on bringing down thousands of records not causing corruptions just degraded performance. I'm going to let David Fenton explain his comment on good programming practices. Other than, as previously stated, splitting the database and local copies of the FE. – Tony Toews Jul 13 '09 at 19:08
  • 1
    I was born in rural Illinois, though I've lived in NYC since 1988. I had the "attitude" (i.e., I don't tolerate stupidity) long before I arrived here. – David-W-Fenton Jul 13 '09 at 22:40
  • R.I.P. David. Your answers were always spot-on for me at least. – Taptronic Jun 24 '15 at 11:12
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.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • My practial real life experience has been that 25 users are fine in Access. We did have one reliable poster stating that he had 100 users in his app. But no idea how many were data entry and others inquiry/reporting only. – Tony Toews Jul 13 '09 at 19:10
  • To be fair, the application that I worked on for several years had some pretty heavy network traffic. But that was by design. A dropdown with 1000 client names in it might be considered a bug in someone else's application, but it was a feature in ours; this is what our customers wanted. Eventually we moved to a SQL Server Express backend, but not without accepting some damage to our reputation. We (and the customers) could accept some performance degradation, but not database corruption. – Robert Harvey Jul 13 '09 at 19:19
  • The biggest problems were with the installations with 20 or more users. Invariably a hardware problem was involved, as you pointed out. We went onsite and migrated all of the big installations to SQL Server Express manually. Eventually, we wrote a migration tool into the installer and required all of our customers to migrate to SQL Server Express. There were enough differences between the two databases that we didn't want to support both. – Robert Harvey Jul 13 '09 at 19:30
  • The corruption problems stopped when we started using SQL Server Express as our backend database. I think that speaks for itself. – Robert Harvey Jul 13 '09 at 22:54
  • 2
    If you must be "good at it" to avoid corruption then I declare Access to be fickle and unintuitive, hence to be avoided by the 'generalist' (i.e. the typical Stackoverflow users) and left to the 'specialist' (i.e. the regulars in the Access newsgroups). Imagine if MS Word documents went corrupt if you weren't a good typist ;) – onedaywhen Jul 15 '09 at 09:44
2

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.

Rx_
  • 1
  • 1
0

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

Jabin
  • 1
  • 1
-2

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.

Eric
  • 1