Records Getting Deleted from Key Table

[BrianDP]

I have a database with a split front end/back end. There is a key
table in the back end called Catalog, and it is sort of a central key
table for all sorts of things. It's a list of all the jobs that have
ever been worked on at our company. Records are getting lost out of
this table, but I have no way of figuring out how they're being
deleted. Records should NEVER be deleted out of this table. They can
be marked as inactive, or something like that, but nowhere, in any code
do I ever display this table for the users, and allow them edit access,
and nowhere in the VB code I have written do I ever delete records out
of that table.

One hint, I don't know if this means anything, but the records always
seem to dissapear off the TOP of the table. It's always the first
record in the table that gets deleted. We put 20 dummy lines at the
top of the table just to keep real jobs from dissapearing. This works
pretty well. I also have written a routine that makes a copy of the
catalog table, and then the next time you go into the table, it
compares the current table with the snapshot that was made, and if it
finds records in the older snapshot that aren't in the current table,
then it re-appends these records in.

This whole thing is a little too flaky for my liking. I'm unaware of
any sort of tracking that Access has for this type of thing, like SQL
has some sort of mechanism that you could figure out who was deleting,
or what program, or workstation, or at least some clue as to what was
happening. I am without a paddle in this situation. It's an important
table, and my fixes of having dummy records, and the "did any records
dissapear?" routine, while they do seem to be working, I'd much rather
dig down and find out what is happening here.

Any suggestings would be helpful!

-BrianDP

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sounds like a corrupted table. Run Compact & repair on the back end.
And/or create an new, blank DB file and import the tables from the old
Db. Or, recreate the tables in the new DB & link the old tables to the
new DB. Then use append queries to get the data from the old tables
into the new tables.

If you want to be sure no one is slipping in and deleting records set up
User Security and RWOP queries. Deny access to the tables and access
the data thru the RWOP queries (do not allow Delete permissions on the
table to any user but the Administrator). Read the Access Security FAQ
for details (on the MS site).
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAYKU4echKq OuFEgEQLZfACdHT FWwPehNKltGfarS IqtxSIWx5EAoLh6
lR2AmB/5y3i1JOpo5w7WH0 gP
=aU2k
-----END PGP SIGNATURE-----

BrianDP wrote:

I have a database with a split front end/back end. There is a key
table in the back end called Catalog, and it is sort of a central key
table for all sorts of things. It's a list of all the jobs that have
ever been worked on at our company. Records are getting lost out of
this table, but I have no way of figuring out how they're being
deleted. Records should NEVER be deleted out of this table. They can
be marked as inactive, or something like that, but nowhere, in any code
do I ever display this table for the users, and allow them edit access,
and nowhere in the VB code I have written do I ever delete records out
of that table.

One hint, I don't know if this means anything, but the records always
seem to dissapear off the TOP of the table. It's always the first
record in the table that gets deleted. We put 20 dummy lines at the
top of the table just to keep real jobs from dissapearing. This works
pretty well. I also have written a routine that makes a copy of the
catalog table, and then the next time you go into the table, it
compares the current table with the snapshot that was made, and if it
finds records in the older snapshot that aren't in the current table,
then it re-appends these records in.

This whole thing is a little too flaky for my liking. I'm unaware of
any sort of tracking that Access has for this type of thing, like SQL
has some sort of mechanism that you could figure out who was deleting,
or what program, or workstation, or at least some clue as to what was
happening. I am without a paddle in this situation. It's an important
table, and my fixes of having dummy records, and the "did any records
dissapear?" routine, while they do seem to be working, I'd much rather
dig down and find out what is happening here.

Any suggestings would be helpful!

-BrianDP

[inkman04]

Hello.

Just a point of reference. Is there any records that are being
deleted in any other table. If so, maybe you can check in the
relationship window and see if a link has been created between
that table and the main table of reference. If so, then check
and see if the "Cascade Delete Related Records" has been
selected. If so, deselect it.

Just my two cents worth.

Regards

[BrianDP]

No, no other records that I can tell, are being deleted from any other
tables.

There is one program that is run every morning, where they enter labor
transactions, and as it does, it opens this catalog table, and verifies
that the transaction they are entering actually has a job that exists
in the catalog table.

That program appears to be the culprit, because it's only in the early
morning when they're running that application this happens. The woman
who runs the program is some sort of speed demon as well, and gets 5
fields ahead of the entry program, so if something happens, and the
program breaks in the middle of her entry, we can't see where it broke.
I think it's that program, and I think something is happening that she
types SO fast that she doesn't see if she makes a little mistake, the
program breaks, and somehow she over-writes that record. I can't see
it any other way.

I suppose a solution would be for them to match the jobs against a copy
of the master table, that way if the records got deleted, they would
only be from a copy of the real table, not from the real thing.

-Brian