2
I'm a very low-level developer with limited VB knowledge but nonetheless was able to put together a very user-friendly and extremely helpful Access 97 database for my company some 10 years back. We use the database for inventory, job costing, employee records, timecard recording, nearly everything except the hard core accounting. The database is shared by 20 users over a network. Every 3 or 4 years I archive and purge the data to keep the tables from becoming too large. Currently there is information in the database from 2003 forward and several of the tables have more than 100,000 records, but that has never been a problem before and from what I've read, should not be a big deal. The entire database is only about 80 MB after compression. We have had very few problems with the database over the 10 years since I first set it up, until just recently...
About a month ago the database began corrupting daily, often more than once. All of a sudden someone would call me to say that they couldn't open Access because of some error message and then I'd begin the process of having everyone exit, copying the DB to my desktop and then repairing and compacting or doing whatever else was necessary to get it back up and running. Usually the compacting would not work the first time and I'd have to go into the tables and find the corrupted records and delete them. After a week or so of this I went the route of starting with a new blank database and then importing everything from a good copy of the old database. When that didn't fix the problem I read up on things and decided to split the database with the backend on the server and a front end on each desktop. Things worked a bit better but we're still having problems at least every other day.
Last week I removed all the CLOSE buttons from my forms after reading that closing that way could cause new "dirty" records to be unintentionally lost. That didn't help anything...
I forgot to mention that one of the other things we did at the outset was to get all the workstations up to date with the SR-2 patch for Access and the latest 3.51 Jet update. That didn't seem to help the problem one way or the other. I should also mention that 15 or so of the computers are running XP Professional but there are still a couple Windows 98 and Windows 2000 computers on the network. Not using those computers for a couple days didn't seem to eliminate the corruption...
That brings me up to today, and I have come across something that is really strange to me and I can't find anything about it on the internet so far. I opened the DB on 8 workstations and then opened a table where we keep inventory records and put it up on each monitor in datasheet view. Then on one computer I opened our "Pull Inventory" form and recorded a transaction. The transaction recorded in the table immediately on 7 of the 8 computers, but on one of the computers the new record did not show in the table. I exited Access and then re-entered and the record STILL did not show. I re-sorted the table columns to be sure and noted the total record count. Sure enough, 7 of the computers showed 151,751 records but that one computer showed only 151,750 records, even 15 minutes after the data was entered and the application was exited and re-entered two or three times. The problem computer is fairly new and is running XP Pro.
At this point I opened the "Inventory Pull" form on the problem computer and entered a new record but when I went to exit I got the "record cannot be saved because of duplicate values in the index or primary key" message, which makes sense since the index field is an incremental autonumber field, and the next number had already been taken by one of the other computers.
Anyone got any idea what could be going on? Why would one of the workstations not be reading and/or writing to the server the same way that the others are? I'm not an IT guy by any stretch of the imagination, but for the life of me I can't figure out what is going on here...
Thanks for any help!
Corey
