More on this whole story...
I've found that in some instances I was referencing table and fieldnames
with cased names, where all items in my db are lowercase. I changed that.
I also found that when checking for primary keys the program was
surrounding the data values with square brackets [] - a legacy from sql
server.
I also found that when passing strings to the database they weren't
enclosed in apostrophes. I.e. Joe Bloggs instead of 'Joe Bloggs'
I noted in some cases my program was trying to do an AddNew when the
record did in fact exist in the database, which I assume to be due to
the casing of field names in the primary key. However in those cases the
ODBC driver was receiving an error rather than crashing the program.
I don't know if any of these would have been the central problem, but I
changed them all to more appropriate behavior and the incidences of
crashes have somewhat dropped. Rather than crashing with every second or
third update it is now able to carry on for a couple of dozen or more
before it goes kaput....
Now I'm more intrigued.
I am in the process of converting the updates and inserts to executes on
the database object rather than using an intermediary recordset, so I'll
see if that changes anything - it'll let me get rid of a significant
block of code anyway.
P.
--
Paul Lambert
Database Administrator
AutoLedgers