Deleted files from table

[Lena]

Is it possible for a record (row) to be deleted from an Access table without
any prompting or input from the user.

This is the scenario: I have a user who swears she did not delete any
records from her database, yet her report shows inconsistent totals.

I checked the file# (autonumber field, which I am trying to presuade them to
do away with), there were file numbers out of sequence. I went back to her
backup, found two of the missing files and restored them to production.

A day later she noticed another file was also missing from the database. I
went back through her backups for two months and the file# was not to be
found. She stated she has not deleted the record. I asked if she had any
other information about the record I could query against and she stated she
did not know what was in the record, only that the number was missing.

My question, is it possible for Access to randomly delete records (rows)
from a table that uses the autonumber field to identify a file#?

Any help asap would be greatly appreciated as they are waiting my response.

Thank you all

 

[Dale Fye]

Lena,

I've never encountered what you are talking about. I assume that when you
say "file was missing from the database", what you really mean is that a
"record is missing".

1. Is this user the only one using the database?
2. Is she working in the tables, or is she using forms to enter and update
data?
3. Do you have any table relationships setup in the database? If so, are
any of these setup with referential integrity and cascading deletes?

It sounds like the application uses the Autonumber field as some sort of key
that the user sees. Most developers will tell you that the primary reason
for having an autonumber field is to have a unique identifier for each record
in your table.

Personally, I "NEVER" expose the autonumber field of any of my tables to my
users. If you need a field for a File# or something like that, then you can
generate one manually.

HTH
Dale

 

[Lena]

There is one thing I forgot to mention, this database is used in a shared
enviroment. There are two FE associated with the tables in the back and each
group has their own permissions to access the data in the table. Only one
person has all permissions associated with the database.

 

[aaron.kempf]

yes, I have seen Access randomly, incorrectly-- delete records

I moved to SQL Server because Access couldn't keep it up.. if you know
what I mean.

-Aaron

 

[Lena]

I apologize, I meant record. This database has really frazed my nerves. The
person using it has started creating her own queries, reports, etc.

Please see my reply post regarding shared database. No she is not working
in the table. By the most part I have the table hidden to avoid users keying
data directly into the table and base everything on forms. However, she does
know how to bypass this option to get to the tables. This is what she has
used to search for her lost records or to check the sequencing of the records.

No there are no referential integrity setups or cascading deletes in the DB.
The autonumber field has been used to create file# for each record. Because
there are one-to-many instances in the DB, but they are not associated with
the file# only by the client name and number, which does not generate any
other reports or forms, the database is sort of a data storage of how many
times a client needed assistance.

I have offered to remove the autonumber field and reset a new one, but then
her records or hardcopy files would be off by one number. Not sure what I
can do for her at this point. My suggestion is to move on and be very
careful about using the table or actually paying closer attention to possible
deletion of records. Totally stumped.

 

[vbasean]

arron...

I can thouroughly understand your excitement and loyalty to SQL server
but the honest truth is that MS Access and a fine tool for many office
solutions.

 

[aaron.kempf]

I disagree.

Sorry.

Access is a great tool for working with SQL Server databases.

but using Access as a database?

ROFL

It isn't reliable enough-- it isn't scalable enough-- it isn't
consistent enough; or easy enough.
anyone that tells you that Access is _EASIER_ than SQL Server is
trying to sell you on something.

Either that; or they are an Oracle idiot.

-Aaron

 

[Lena]

Is there a way to avoid this from happening? This is a very critical
database and correct record counts EXTREMELY important to my client. I have
requested SQL for years, but not permitted to use this product.

 

[aaron.kempf]

SQL Server is a _FREE_ download-- MSDE 2.0 or SQL Server 2005 Express.

You don't need to _REQUEST_ SQL Server, you just need to install it.

Are you 'not permitted to use it' so that they can keep your salary
down?
Or is it because you work at an Oracle shop?

MSDE 2.0 is an optional component of Microsoft Office. It is even
included on the Office disks; and it has been since Access 2000.

-Aaron

 

[aaron.kempf]

VBA Sean;

I can thoroughly understand your _LAZINESS_.

Is that why you haven't graduated from the first grade of the database
world?

Honestly-- WTF is wrong with you?

Are you mentally retarded?

Why would you possibly give mis-information?

SQL Server is _THE_ reccomended best practice for working with MS
Access.

-Aaron

 

[Gina Whipp]

Lena,

Yes, it is possible to delete a record from a table with no message.
However, Access does not delete them, there must be an end-user action or
something in the code.

First place to check is Tools|Options|Edit/find tab. In the section labeled
Confirm make sure all the check boxes are checked. However, you should
note, they can be easily unchecked. The next place to check is to make sure
there is nothing in the code that says DoCmd.SetWarnings False before an
action, such as delete action.

Also, autonumber does not ALWAYS increment by a value of one. So using that
is your check to insure no missing records is not always reliable.

Lastly, please ignore Aaron, his solution for EVERYTHING is an SQL Server.

HTH,
Gina Whipp

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

 

[Gina Whipp]

Aaron,

This is an ACCESS newsgroup, please stop from
insulting/degrading/belittling/etc... the people that come here for help and
supoort. PLEASE take your medication and move along.

--
Gina Whipp

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

VBA Sean;

I can thoroughly understand your _LAZINESS_.

Is that why you haven't graduated from the first grade of the database
world?

Honestly-- WTF is wrong with you?

Are you mentally retarded?

Why would you possibly give mis-information?

SQL Server is _THE_ reccomended best practice for working with MS
Access.

-Aaron

 

[Lena]

Is there any other way to increment the files? To manually implement can
cause errors in the numbering of the files (record). I found a code to
create a button that would automatically increment the numbering, but in
essence, this is just creating an autonumber field that would be generated by
using a push button. Also, in speaking with the client at this moment,
another record (that I had intially restored back to production from backup)
has disappeared again. This database has been working fine until
approximately three weeks ago. The only hardware changes were a router
switch out, but this problem existed before this switch out. We have had no
power outages, no lightening strikes or any outside interference with this
database. This database resides on its own server. There are no other files
stored on this server, only the fe and be of the database.

 

[Jerry Whittle]

Rule #1: Users lie.
Rule #2: When users swear that they didn't do something, see Rule #1.

It is possible for Access to lose records IF the database is corrupt. You
usually get a warning message on startup that the database is corrupt and to
do a Repair. After the repair Access should tell you that records were
deleted. HOWEVER all bets are off if you have Compact on Close enabled.
That's one reason that I suggest turning that feature off.

Also there have been a couple of bugs over the years; however, if the
computer is patched properly, that shouldn't be an issue.

If you have code or a macro that sets warnings to off but later forgets to
turn warnings back on, a user could delete a record and not get a prompt.
Also check for code or delete queries that could be running without the
user's knowledge.

Autonumbers are not guaranteed to be sequential, just unique, so gaps can
happen. If you open up a new record, but decide against adding data (by
hitting the ESC key a couple of times for example) , the autonumber is
"burned" and there will be a gap. In a nutshell, you can NOT depend on
autonumbers if you need sequential numbering for records.

However since you found some missing records in a backup, well, seems that
someone or something did delete the records.

Good job on having backups. With a client like this, I recommend nightly
backups.

 

[Lena]

ROFL

Okay here we are with information from the user. She states that there are
times when she accidentally hits the roll button on the mouse and it causes a
new record. She puts something in the record to hold the space and then goes
back to fill the record with appropriate information. She NEVER uses the ESC
key. In the past we did run into this problem with missing numbers.

Thanks for the chuckle, I truly needed it.

 

[Gina Whipp]

Lena,

You might want to ask the end-user if she is deleting ANYTHING, depending on
your relationships it might be a 'Delete cascading records' issue. Also
make sure the database is not set to 'Compact on Close' (see Jerry's reply)
and make sure 'Name Auto Correct' is UNchecked.

 

[Lena]

I suggest that they manually key the file# in each record. One of their
concerns is that they may not key the correct sequential number. My solution
is to keep the autonumber field, not making it related to anything in the
database, keep the file# field as it is as a primary key, but use the
autonumber field to help guide them in numbering records correctly only.
Would we still run into problems with dropped records?

 

[Lena]

User states she is not deleting anything. No there are no cascading in this
database.

Thanks Gina.

 

[Jerry Whittle]

I would have to say yes. Autonumbers can be 'burned' many different ways.
Heck I've even seen Access choose a negative number for a autonumber. It was
unique.

If there is only one person adding records, you could do a DMax to find the
previous high number and add one to it. If a multi-user environment, you
could do the same thing; however, you need to do it in an event like After
Insert. Both require data import though a form and not directly into a table
or query.

 

[BruceM]

You're way out of line. Opinions, even obstinate ones, are OK, but personal
invective is not.

VBA Sean;

I can thoroughly understand your _LAZINESS_.

Is that why you haven't graduated from the first grade of the database
world?

Honestly-- WTF is wrong with you?

Are you mentally retarded?

Why would you possibly give mis-information?

SQL Server is _THE_ reccomended best practice for working with MS
Access.

-Aaron