472,119 Members | 940 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Access 97 Record Edits Not Saving to Backend Consistently

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!

Nov 15 '07 #1
4 2266
3,080 Expert 2GB
Check the references on the "error machines".

Open some VBA code (e.g. by pressing [CTRL+G]) and select Tools/References.
Each libraryname starting with "MISSING" must be unchecked and (e.g. microsoft DAO version x.xx) replaced when necessary.

As access doesn't cleanup added libraries, often unnecessary refenreces are present.
Just delete as many as possible without getting an error executing "Debug/compile all modules"

Nov 17 '07 #2
3,532 Expert 2GB
As to the question of "Close" buttons causing dirty records to be dumped, you might want to read the following:

It has long been recommended that the code

If Me.Dirty Then Me.Dirty = False

be inserted before using


to close a form because of a quirk in Access. When DoCmd.Close is used, Access closes the form regardless of whether a validation rule or required field rule has been violated! If one of these rules has been violated, Access will simply dump the record, close the form, and not tell the user that the record has been dumped!

If Me.Dirty Then Me.Dirty = False forces Access to attempt to save the record, and if a violation has occurred, will throw up a warning message allowing correction to be made before closing the form.

Welcome to TheScripts, Corey!

Linq ;0)>
Nov 17 '07 #3
Thanks to both of you, Nic and Linq. I've made the modifications you both suggested. Also took the machine that was not reading the record updates in a timely fashion off the network and haven't had any problems for 3 days now. Still curious as to why that one machine operated the way it did though...

Nov 20 '07 #4
3,080 Expert 2GB
I've read that sometimes a network card caused Access trouble, so perhaps it has been a hardware problem...

Glad it's working OK now :-)

Nov 21 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.