I can vouch for the dangerousness of using Access in a heavy production environment. There is a Microsoft Knowledge base article about using MS Access in a production environment. If I can find it I will post it. I did a project once where my director demanded me to process about 6gb of patient data using Access. It wasn't pretty, and when he asked why the database kept crashing, my response was simple - something like "Well, boss, it's 'cause we're using the wrong tool for what you want."
Essentially, Microsoft would prefer people use MS SQL for mission critical production type environments. Horst's problems sound like he's just pushing Access' limits too much (which isn't hard to do). It's necessary to clean and compact an Access database once in a while to keep the index files maintained properly. Especially after a crash. You're better off using PostgreSQL or MySQL over Access for a production environment. Richard Schilling Webmaster / Web Integration Programmer Affiliated Health Services Mount Vernon, WA USA http://www.affiliatedhealth.org phone: 360.856.7129 > -----Original Message----- > From: Tim Churches [mailto:[EMAIL PROTECTED]] > Sent: Sunday, December 30, 2001 12:31 PM > To: [EMAIL PROTECTED] > Subject: Re: MS ACCESS can be dangerous - (careful, hearsay) > > > "John S. Gage" wrote: > > > > All this relegates Access to the category of a "toy". The > question is, is > > VB a "toy" also? It would be interesting to have some > references on that. > > VB, like Access , can connect to a wide range of database > back-ends, but > the default back-end for VB is the same as for Access, i.e. the Jet > engine. > > Further to my earlier post, the Microsoft KnowledgeBase entry which > refers to the problem is Q191883 (Data Changes are Saved to the Wrong > Record). It notes that the problem is fixed in Access 97 by installing > MS Office 97 Service Pack 2... > > However, this is different from the problems experienced by > Horst, which > seem to be related to corruption of the MDB (the Jet engine data file) > after a crash. I have also experienced a surprisingly high > frequency of > database corruptions when using Access, always after a crash which > brings down the entire OS, which is a regular event when using Windows > 95/98/ME, often caused by some other program. This problem is > compounded > when Jet databases are used simultaneously by multiple users > by sharing > over a network - a crash on any one of the machines using the database > can then corrupt it. This was also a big problem with dBase, > Clipper and > FoxPro databases shared over a network back in the Bad Old > Days, and is > a fundamental problem with file-sharing ISAM databases in > general (which > is why the Revelation and Advanced Revelation databases, which were > based on Pick, were such a good but alas unpopular choice in the late > 1980s - no matter what happened, they never became > corrupted). Indeed, I > understand that the most recent version of Access comes with an > alternative to the Jet engine which is actually a cut-down > version of MS > SQL Server which runs locally i.e. they are pushing a proper > client-server database architecture even for local use, and certainly > for shared use. > > So, I think it is wrong to dismiss Access as a toy - it is the most > widely used database in existence, mostly for "end user" databases. It > has a great interface and the original concept of giving end users the > ability to create even quite complex databases with slick > interfaces and > complex reports without having to do any programming at all was (and > still is) brilliant. If only it were not Microsoft implementing that > fantastic idea... > > However, I don't think that Access or VB using the Jet engine are good > choices for mission-critical or safety-critical or security-critical > applications or shared-access applications. That still leaves a lot of > "little" databases which people set up for their personal use or for > occasional use by a small number of immediate colleagues - as I said, > better that they use Access than Excel for such purposes > (Excel is nice > spreadsheet but a data management disaster area). > > Access or VB with SQL Server or equivalent as the back end is another > matter - I know of a number of such systems which are very reliable > despite being pounded by hundreds of users non-stop. But those systems > were developed professionally and cost hundreds of thousands > of dollars. > Which comes back to my original point - although the underlying choice > of tools is important, its what you do with them which counts > the most. > > Tim C > > > > At 12:20 AM 12/30/01, you wrote: > > >Tim Churches wrote: > > > > > >>However, there was a big stink a few years ago when > someone claimed they > > >>had discovered a fundamental flaw in the Jet database > engine, which is > > >>what MS Access uses for its storage. It turned out that > the "problem" > > >>was caused by the programmers in question treating the > Jet engine as if > > >>it were a much simpler dBase-style ISAM data store and > thus assuming > > >>that records would always be in the same order as they > were when they > > >>were added. In fact, the Jet engine is much more > sophisticated than that > > >>and it constantly re-organises records internally - it > only guarantees > > >>that results obtained via SQL statements will be correct > - if you bypass > > >>SQL you might be surprised. > > > > > >The problem is that Access messes up references ("foreign > keys"). I wrote > > >a quite extensive system for our surgical department 5 > years ago. It kept > > >track of all procedures performed, associated codes (ICD, > procedural > > >codes, outcome codes, procedure log for surgeons in training, > > >complications registry etc.) To my dismay, once we had a sizeable > > >database, it did not seem to count my procedures correctly. I had > > >estimated that I already had done all hernias required for > my traiing, but > > >the database suggested far less. > > > > > >After extensive debugging and creating large regression > test databases we > > >found out that you can quite predictably mess up > references simply by > > >crashing the system while entering data. Apparently, > Access is/was not > > >capable of rolling back transactions correctly and > allocated foreign keys > > >fairly randomly after a crash without complaining; even > more surprising > > >was our finding that even "old" record sets that were NOT > used at the time > > >of the crash could be corrupted that way. > > > > > >Following our own testing, trawling the web for similar experiences > > >yielded enough links to make us stop using Access forever. > Haven't touched > > >it since. We referred to Access as the "data lottery > system". Pity, as I > > >was very fond of the user interface and the ease of producing user > > >interfaces to a database. > > > > > >>Thus, I suspect that the problem related in this story, > if true, is > > >>caused by the programmers doing a join between tables using direct > > >>programmatic access to the Jet tables and bypassing the > SQL interface. I > > >>dimly recall a note in a Microsoft knowledgebase about > this very issue. > > >All our joins were created "visually" using the tools > provided by Access. > > >These visual tools produce SQL statements. Whether they were used > > >internally, I do not know. > > > > > >Horst >
