Opening MDE file more than once

[kb178]

I'm coming across a problem I have never seen before... Records are being mysteriously deleted in an Access database. Can records be lost any other way than using a delete command or going to Edit->Delete? I'm not really even getting any error records... I look at the autonumber ids, and some are just missing.
In regards to the subject, I noticed someone had the database opened twice on their desktop (because I have the database broken up into two sections, and so she wouldn't have to flip back and forth she opened it twice). Could doing that have a bad affect on the data?

Thanks,
Kristen

 

[LittleSmudge]

Depending on how you create new records - it is quite easy to get missing numbers in an autonumber sequence but that does not mean that data records have been deleted.

Try this :-
Note the highest value of AutoNumber in existance.
Open your form and create a new record.
Start typing in the first bound control.
Change to another control.
Hit the escape key twice and close the form.
Repeat the above five times
Now create a new record, complete all controls and save the new record.
Compare the AutoNumber of the result with the initial value.


Now go and ask the users if they are actually noticing missing records.

It the answer to the first experiment is a gap of 6 and the enswer to the second is NO then:-

Don't sweat the small stuff.


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[psemianonymous]

Some answers:

1. Yes, there are many many ways to delete records, including directly via table view, using DELETE queries, using SELECT queries by deleting an record from the query, deleting items from tables with cascading deletes attached to relationships with other tables, etc.
2. But what you're probably seeing is that someone starts a record and cancels it before saving the record. This may "use" an AutoNumber and skip it when generating for the next item. Thus the gaps you see. This is normal behavior for autonumbers, so you must "bend your mind around the spoon" instead of attempting to "bend the spoon with your mind". Enough zen, onwards...


3. One great thing about Access is that it is multi-user by design. Of course it's terrible compared to client-server DBMS's, but what I'm trying to tell you is that it's fine if they open up two Access windows, as long as they don't try and edit the same record in both windows at the same time (and yes, Access has a way to handle that, too).

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[kb178]

Thanks for the help, but unfortunately the users are noticing missing records. They actually have printouts of some of these missing records.

 

[psemianonymous]

Maybe to answer another question, when an Access MDB goes corrupt, it won't start deleting records--you will know in no uncertain terms that the file is corrupt. So what you're seeing is a result of the database usage/design/flaws, and that means that you're going to have to figure out what's causing the error.

 

[kb178]

Thanks Foolio - actually I have a save record command after one of the main controls on the form that they choose first in order to enter a record (it's a selection that creates a job code, and because there were problems with duplicating job codes and getting record lock erros, I have the record being saved as soon as a job code is created).