Missing Records

[Brian]

OK...Don't know where to start...

We have an Access database back-end only about 50MB in size. This consists
of about 60 tables of which one has about 28,000 records. The data entry is
controlled by an Access front-end with forms, queries, reports, and modules.
And alot of VBA code on the forms. The users do not have access to the raw
Access data tables except through the forms.

The form that controls the data entry for the table with 28,000 records has
not change in a very long time. However, we have recently noticed that some
of the records in the table are disappearing with alarming regularity.
Always the same records which are in the beginning of the table when I open
the table.

My thought is that it can't be the programming because it hasn't changed and
if it was wrong, we would have noticed the missing records before now. Plus
I looked at the programming again and can't see anything obviously wrong.
But I am hard-pressed to determine what else can be wrong, unless I've come
to the point where I should be looking at a SQL back-end.

Does anyone have any thoughts on this?

Thanks in advance to anyone who can offer suggestions, help, or just plain
hope!

Brian

 

[Bill Mosca, MS Access MVP]

Brian

Since we're talking SQL Server, the first thing I would do is revoke
everyone's permissioins to delete records. That will at least give you some
time to research and will also triggar error messages when an attempt to
delete a record occurs.

As to the VBA programming not being responsible because it never did this
before doesn't really mean anything. If something is limiting the number of
records, it might have just kicked in because a table has finally reached
that number.

If you have looked through all the code and macros then it's probably time
to look elsewhere.

If records are disappearing by themselves, it could be a scheduled job in
SQL Server. Take a look at those, triggars and all the stored procedures.

 

[Tom Wickerath]

Hi Brian,

Are your tables related (Tools > Relationships) with referential integrity
enforced? Have you set the foreign key fields as required? Here are some
articles written by Access MVP Allen Browne that you may find helpful:

The Query Lost My Records!
http://allenbrowne.com/casu-02.html

Common Errors with Null
http://allenbrowne.com/casu-12.html

Losing data when you close a form
http://allenbrowne.com/bug-01.html

Incorrect filtering
http://allenbrowne.com/bug-02.html

Also, are users allowed to delete data? You may have a user who is
inadvertently deleting data. I usually provide a command button captioned
"Delete", which simply toggles a boolean active record field. Thus, the
record is not really deleted, but it appears to have been to the user. I also
timestamp the record with the NTUserID of the person who "Deleted" the
record, the machine name, and the date & time that this occured.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Van T. Dinh]

50 MB is nothing for an Access Database ... I have one that is close to 1 GB
and others use JET BE well above that.

From your description, is it possible that the bound Form opens at the
"first" Record at the beginning of the Table and some user accidentally
deletes the starting record without knowing? Do you have new users on the
database recently? Do users have access to the DatasheetView of the Table?

IMHO, I don't think the problem is in the database size or your programming
as you wrote ... It is more likely to be users' unintentional actions but
flaky network connections / NIC can give similar random problems but in this
case, you will need the network people to monitor the traffic ... on the
network, probably with special equipment to record dropped / erroneous
packets, etc ...

Keep compact the BE and regular back-ups for the moment ...

 

[Bill Mosca, MS Access MVP]

Looks like I misread your post. I thought you said you were using an SQLS
backend.