Corruption, Network delays....best practices?

[DataMiner]

Hi,
After years of things running OK, I am now having almost daily db corruption. I believe it's due to some sort of write-delay issues on our network, over which I have no control. For example,here's a code snippet that sometimes works and sometimes doesn't.

WkSpace.BeginTrans
Trans = True
DB.Execute "delete * from latestrevmaster;", dbFailOnError
DB.Execute "latestrev2", dbFailOnError
WkSpace.CommitTrans
Trans = False

RefreshNounsTested

WkSpace.BeginTrans
Trans = True
DB.Execute "delete * from OppsPerFinalsProduct;", dbFailOnError
DB.Execute "oppsperfinalsproduct2", dbFailOnError
WkSpace.CommitTrans
Trans = False

The queries re-fill the tables with new data. When it fails, it usually fails at "delete * from OppsPerFinalsProduct", because the DB I am referring to (DB is NOT the current db)has suddenly become corrupted.
RefreshNounsTested does a bunch of writes to DB also.

I think my best bet to solve this issue is to convert to SQL Server. However, I am RETIRING in 31.5 days (but who's counting???) and I'm pretty sure that's not enough time to take on that project. (My boss thinks the db is reacting to my endorphin levels... the higher they get, the less stable the systems become...)

So, short of that, is there a more robust way I can structure this to make this problem less likely?

One thing I'm wondering about: If I want to run a bunch of action queries on a foreign db, which is a more robust approach:
set MyDB=opendatabase(foreigndb)
mydb.execute(myquery)

OR
Link the tables in MyDB to the CurrentDb.
Add the queries to CurrentDB and run them from CurrentDB.

Any ideas sincerely appreciated.

 

[boblarson]

What is the current setup right now? Is it a split system with a frontend on the users' computers and the backend on the server?

 

[DataMiner]

Yes. Access 2002, each user has their own copy of the FE, backend resides on server.

 

[boblarson]

Okay, due to network issues, it may make sense to move to a SQL backend and try to move as much of the processing to the server.

I believe that it would probably at least be safer, even if you just upsized and moved it, but depending on other design issues, it could actually slow performance. So, probably some tests are in order.

 

[rolaaus]

you mention this database running for years, makes me wonder if you are reaching some of the limits/capcity for MS Access. How big is the actual MDB file getting that is getting corrupted? Are you performing regular compact/repairs? (you shouldn't have to run many compact/repairs if the only thing you are doing is adding records into a BE database, but it should probably be run on occasion anyways, especially if you are doing any kind of record deletes).