I have recently been tasked with helping a user correct a database problem they have been having with an Access97 database. Almost all of the tables are linked, most of the tables linked are Access tables and a few are on Sybase. At least one Sybase table has over 2 million records (only select queries are run against this table). The entire application is Macro driven - no code at all. Here are the problems (other than the macros):
1) records are disappearing. 45 records will be added and viewed in a report, then an hour later, the same report will only show 6 of the 45. This only occurs on one table, in access, that has 42,000+ records.
2) file locking problems - Error message "Could not use; file already in use" The .ldb file is not going away on its own and must be deleted. Repair and compact is then performed.
3) "unrecognized database format" or "<filename> needs to be repaired or is not a database" - I assume users are powering off when the app is open and are corrupting the database, again repair and compact.
4)#ERROR is appearing across rows in a table and "Not a valid Bookmark" errors are occuring. The rows cannot be deleted. We replace the table with a copy and have had instances where these will turn into records. I assume this is another corruption issue.
5) Reserved error (-1601) is occuring - According to microsoft we should just copy the database and import all the objects. It works.
I have been reading a lot on the exchange where people have apps with over 300,000 records and 50 users with minimal problems. Am I missing something? We basically consider Access to be somewhat unstable and best suited to small apps with a few users. This app has 5 users, and many other of our access apps see the need to compact and repair more often (due to problems 2&3) than the nightly process we already have automated.
Sorry about the low pointage, but this is my first time out and don't have many to give.