"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
