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