Records Being Deleted Automatically

[SamDemon]

I have a table, tblCompanyDetails which holds all our clients details, name, account number.. extra. I am finding now that some of our clients records are being deleted for no aparent reason! I can not put this down to some deleting them manually because it is happening when you go out of the database and then go back in. I am also finding account numbers moving around. Has anyone else seen anything like this?

Sam

It's just common sense, shame sense isn't common!

 

[Crowley16]

sounds strange...

maybe there's some code running at startup...

or maybe someone's linking to your db

--------------------
Procrastinate Now!

 

[SamDemon]

Where would i check the code that is running at the startup?

It's just common sense, shame sense isn't common!

 

[Remou]

Hi. You could check for an autoexec macro. If you have not done any coding, have a look at modules and see if there are any. Check to see if there is any code attached to any forms. Password protect your database, in case someone is linking. Can't think of anything else at the moment!

 

[ZmrAbdulla]

Yes, can't say anything without knowing any more.
But can say DO A BACK UP, COMPACT & REPAIR DATABASE.
If Access DB behaves something unusal then it is time to hold it more firmly.

Zameer Abdulla
Visit Me (New Look & style)

 

[willir]

Personally, if things are benig "deleted" and "account nubmers "moving around", I would suspect you have a data integrity problem. The problem could be end user (procedural problem), a problem with the front end (forms, forms / subforms, bound controls - text, list or combo boxes), or a database design problem.

If the database was corrupt, you would more likely be experiencing crashes and access errors, not intermittant records "disappearing".

(Sidebar: In days of old, the problem you discribe could also indicate a problem with indexes where a database was not shut down correctly, or exceeded the size limitation of a database. The resolutions were to recreate the indexes, or delete / purge records. However, I have never seen Access exhibit this behaviour.)

Here are things I have seen....
- The form displays reocrds. Instead of querying a record, the end user over-writes the existing record with what they are trying to query.
- A form / subform is used where the form is not linked to the subform via the LinkChild / LinkMaster property of the subform, or through code.
- The wrong column in a combo box is bound to the control. For example, SELECT CustomerID, InvoiceID, InvoiceDetailID where the bound column should be InvoiceID (column 2) but instead CustomerID (column 1) is bound.
- A misnamed variable or improperly linked control on a form. For example, the invoice number for the invoice detail is linked to the customer number and not the invoice "header" record. This mistake resides in a query or a form.
- A mistake in the design where the wrong variable is used to link between tables.
- A poor design where either the foreign key is on the wrong side of the relation (a foreign key should be on the "many" side), the wrong fields are linked, a many-to-many relationship exists but the design uses a one-to-many relationship.

One other unique situation: Lookup fields are created in the table design view.

...What to do
Can you determine when the problem started? Do you know what changed about or just before the problems started?

Interview your end users. How do they enter records? How do they lookup records?

You need to create some basic queries to "look" at your table.

Start with queries to look at each of your tables. Are the records missing or just don't appear on the form? Are the records with bad data (moving account numbers) affected at the table level?

Then look your relationships with queries. For example, a customer and their orders. Invoice "header" and details. Do you the proper matches? Are their orphans -- the detail or child records are present but the master record is missing.

And, yes. Zameer is absolutely correct. Backup, backup and backup. With a database, it is not the same as loosing a document or spreadsheet, you can loose days / weeks / years of work.

BTW, If you do not understand some of the terms I have used, you may do yourself a favour by reading some reading...

Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard

 

[SamDemon]

Willir thank you very much for the detailed post!!

I think I may have found where the problem may be occuring. Within the database I have a form, frmGroupBookings. On this form there is a button which opens up frmCompanyDetails. I had originally used this as a short cut for the end user to enter any new operators that were not already in the database. It appears, that entering 'new' operators via the group bookings form causes the said problems. I have now removed this button, so any additions to the database must be done through a seperate form I might be wrong, but everything appears to working ok now. Will continue to monitor.

Cheers!!

Sam

It's just common sense, shame sense isn't common!