Access is deleting records

[Paul]

Hi
We have an Access 2003 database with a VB 6.0 front-end interface. It is
used on a server where multiple individuals can access it at once. It has
many tables but the one in question has a primary key that is text and is
sequentially incremented by the VB interface.

It appears that records are being periodically deleted by either the
interface or Access without the users consciously doing it.

Has anyone ever heard of this happening? Any thoughts on whether it is
Access or VB?

Paul

 

[Jerry Whittle]

I doubt seriously that Access is deleting records by itself. About the only
exception would be if the database is getting corrupted. If you have compact
on close enabled, turn it off as it can fix corruption, and maybe delete
records, without telling you about it.

If you are seeing gaps in the primary key of the table in queston, there's a
good chance that the code doing the incrementing has problems. It may be
skipping the incremented values leading you to believe that records had been
deleted. Has anyone said that something they typed in has gone missing?

Then the VB code might be deleting records.....

Can users delete records? That would be worth considering.

 

[Gina Whipp]

Paul,

What leads you to belive the records are being deleted? The reason I ask is
because if you are using Autonumber then there are two reasons, that I can
think of right off the top of my head, the records would *appear* to be
deleted but in fact no deletions have been made. For example...

1. You started to enter a record and backed out. Access will not reuse
that record.
2. Access didn't *feel like* keeping the same sequence. Autonumbers are
guarenteed to be unique but not sequential.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi
We have an Access 2003 database with a VB 6.0 front-end interface. It is
used on a server where multiple individuals can access it at once. It has
many tables but the one in question has a primary key that is text and is
sequentially incremented by the VB interface.

It appears that records are being periodically deleted by either the
interface or Access without the users consciously doing it.

Has anyone ever heard of this happening? Any thoughts on whether it is
Access or VB?

Paul

 

[Steve]

I ran into something similar and was never able to resolve it. Records seemed
to just disappear from time to time. Are you by any chance using any append
and/or delete queries to move records? In "Options", on the advanced tab,
what are your settings for "Default Open Mode" and record locking, etc?

 

[Roger Carlson]

Access doesn't just delete records for no reason unless there's corruption.
VB doesn't do ANYTHING you don't tell it to do. The fault is most likely
the front-end application, which means it's really the developer's fault.
One scenario I run into frequently is an application that opens to an
existing record and the user types new values into it, thinking it's a new
record. Naturally the old record is "deleted" when in fact, it's been
over-written by the user. Another possiblity is that the code isn't
sequentially incrementing properly or two different users are getting the
same number so the last one that saves over-writes the other.

Just some possibilities.

 

[Dirk Goldgar]

Access doesn't just delete records for no reason unless there's
corruption. VB doesn't do ANYTHING you don't tell it to do. The fault is
most likely the front-end application, which means it's really the
developer's fault. One scenario I run into frequently is an application
that opens to an existing record and the user types new values into it,
thinking it's a new record. Naturally the old record is "deleted" when in
fact, it's been over-written by the user. Another possiblity is that
the code isn't sequentially incrementing properly or two different users
are getting the same number so the last one that saves over-writes the
other.

Just some possibilities.

I've also seen users think that records in forms, records in query
datasheets, and records in tables are all separate things, and so they would
happily delete most of the records returned by a query "so that I can just
see the ones I'm interested in."

 

[Paul]

Compact on close is not selected and there are many gaps in the PK, but
without knowing more on how it is incremented in VB, I would not think this
is unusual.

Users can delete records, but only 2 have this authority and state they have
not, but records are definitely missing.

 

[Jerry Whittle]

Users can delete records, but only 2 have this authority and state they
have
not, but records are definitely missing.

Rule #1. Users lie.

Rule #2. When they swear about something, see Rule #1.

Oh! Often records seem to be missing with doing a query that joins more than
one table. This is because an inner join will only return record when all the
tables involved have a matching record. You may need to convert the queries
to left or right joins.

 

[Paul]

Default mode open is: shared
Default record locking is: no locks

The records are being deleted a few weeks after they are created, so it
shouldn't be any type of a conflict.

 

[Paul]

We aren't using autonumber actually, the VB increments the PK. The records
are also being deleted a few weeks after creation. Its only 3 so far though.

 

[Paul]

Users can edit existing records, but not the PK. The records are also being
deleted a few weeks after they are created so it is not related to
overwriting new records at creation.

 

[Paul]

I've checked the DB myself just using text filters in the PK field itself and
they are definitely missing.

The odd thing is that the missing records are probably several weeks old at
the time they are deleted. So, it would not be a new record overwriting them
due to there many newer records.

The only thing I could think of is that a record in an ancilllary table that
is connected to this one in question was deleted and the delete cascaded to
the parent or related case record somehow. But this does not seem possible
either.

Paul

 

[Gina Whipp]

Paul,

Do you have it set for Cascading Deletion?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I've checked the DB myself just using text filters in the PK field itself
and
they are definitely missing.

The odd thing is that the missing records are probably several weeks old at
the time they are deleted. So, it would not be a new record overwriting them
due to there many newer records.

The only thing I could think of is that a record in an ancilllary table that
is connected to this one in question was deleted and the delete cascaded to
the parent or related case record somehow. But this does not seem possible
either.

Paul