Records getting deleted, Need Help!!! (1 Viewer)

[kasmax]

Hi Everyone
I need some help from experts here.

I have an MS Access frontend linked to Sql server backend.
There is no relational integrity defined.
There is an Auto increment field for AutoID in the table.

So the issue is that sometimes some records are getting deleted.
and when i look in the table i can see that some autoid missing in sequence.

i have looked through the code and can not find anywhere a code that would delete from the table.
i also looked at any delete queries and any macros, but there is none to delete data from table.

so what could be the cause of deletion? is there a way i can track how its deleting records.
have i missed something that would cause the delete?

Thanks for the help!

 

[Galaxiom]

Auto incrementing Ids are allocated once and not reused if the record they were applied to is started but not saved.

 

[kasmax]

Auto incrementing Ids are allocated once and not reused if the record they were applied to is started but not saved.

Records are saved as they are visible for a while before disappearing

 

[Galaxiom]

Records are saved as they are visible for a while before disappearing

What is in place to stop a user deleting the records?

 

[arnelgp]

and when i look in the table i can see that some autoid missing in sequence.

you "google" what is an autoid and why it is has broken series/sequence.

 

[Gasman]

Are you sure you have autoID as incrementing and not random?

 

[ebs17]

So the issue is that sometimes some records are getting deleted.

Do you work with open queries or with forms? Records can also be deleted through manual actions, especially if there is no user control.

linked to Sql server backend

There may also be others involved who act.

 

[Pat Hartman]

There is no relational integrity defined.

Why did you choose to not allow the database engine handle RI? Are you really handling RI in code every place you need to?

I would start with enforcing RI. If you get any errors, you are already in trouble. You need to clean up your data before moving forward.

When you use an autonumber, if a user starts to create a record but doesn't complete it, the autonumber gets discarded so you will have a gap. Gaps in autonumbers are quite frequent and never anything to worry about. If it is a problem to you, then you are misusing the autonumber and probably need to rethink how you want to generate your unique ID if it cannot have any gaps.

 

[GPGeorge]

Hi Everyone
I need some help from experts here.

I have an MS Access frontend linked to Sql server backend.
There is no relational integrity defined.
There is an Auto increment field for AutoID in the table.

So the issue is that sometimes some records are getting deleted.
and when i look in the table i can see that some autoid missing in sequence.

i have looked through the code and can not find anywhere a code that would delete from the table.
i also looked at any delete queries and any macros, but there is none to delete data from table.

so what could be the cause of deletion? is there a way i can track how its deleting records.
have i missed something that would cause the delete?

Thanks for the help!

The first question is "Why does it even matter if there are gaps in the sequence of values in that field?" That's how the auto incrementing process works; sometimes values are generated but not saved, so they are lost.

Gaps in the sequence may or may not imply someone is assisting you by randomly deleting records. Or it may simply be a byproduct of how those values are generated.

 

[ebs17]

Records are saved as they are visible for a while before disappearing

This should mean that the creation of the records does not fail and this creates gaps in the autonumbering, but that there are real deletions of actually existing records.

 

[Pat Hartman]

Records are saved as they are visible for a while before disappearing

If you are using a subform and don't have the master/child links properly set, you will "lose" records. They will actually be in the table but since their FK is not valid, they "disappear" from the form. RI handles parts of this problem in that it won't let you delete parents with "children" unless you specify cascade delete and it won't let you add "children" without valid foreign keys that point to their parent.

 

[tvanstiphout]

Auto incrementing Ids are allocated once and not reused if the record they were applied to is started but not saved.

That is certainly true for an Access BE, but not for the OP's situation: "I have an MS Access frontend linked to Sql server backend."
I don't want to imply with this response it's a good idea to rely on identity values to be sequential. It is not.
In modern versions of SQL Server we have the Sequence object for that.

 

[LarryE]

I wonder what makes the OP believe records have been deleted? So, I'll ask:
@kasmax :
What makes you beleive that records have been deleted? You stated "Records are saved as they are visible for a while before disappearing". Well, this makes no logical sense. It sounds like the records were never really saved to begin with. The user may have gone to a new record but never completed making it. In other words, no form text boxes were ever filled or checkboxs checked or whatever. That would cause gaps in sequence to occur. "Visible for a while..."? I don't beleive it. Not without some person deliberately doing it.

 

[GPGeorge]

It occurred to me that perhaps we should also pin down WHERE the records as visible?

Can you see them in the form while you are entering them, but not later in the form?

Or, can you see them in the table as well as the form, and they are later disappearing from the table as well?

Several of the possibilities raised previously depend on what you mean when you indicate that the records are visible for a while, but later are not visible.

 

[gemma-the-husky]

Even if you don't have code to delete records, users can just select a record in a form and press the delete key to delete it. If they can open tables directly they can do the same thing, with even less control. If they can design new queries or even add code they could also do it.. You can control these things, but it needs attention.

Adding relational integrity actually helps prevent inadvertent deletions.

 

[Pat Hartman]

Looks like @kasmax has lost interest.

 

[spaLOGICng]

That is certainly true for an Access BE, but not for the OP's situation: "I have an MS Access frontend linked to Sql server backend."
I don't want to imply with this response it's a good idea to rely on identity values to be sequential. It is not.
In modern versions of SQL Server we have the Sequence object for that.

I rely on SQL IDENTITY values and have never had an issue.

Access AUTO NUMBER columns on the other hand do have a history of not keeping track of the seed value but I have not had that issue in quite some time. The issue usually occurred while compacting and repairing in older version databases.

In either case i will never advise not to use them. The known issues have been resolved, assuming the DB's have been upgraded from the dark ages.

The OP's main concern is records getting deleted. He provided enough information in his initial post and his replies before this to conclude that the IDENTITY column itself is not the issue.

 

[spaLOGICng]

Looks like @kasmax has lost interest.

He may have thrown in the towel.

I wonder how many folks have their hands on the data? But one thing is evident, there is no control over the data. Maybe he discovered the issue and it is him and is a little embarrassed. I think we have all been there, but we learn from our mistakes and move on.

 

[kasmax]

What is in place to stop a user deleting the records?

There is no button on user form to delete records

 

[Gasman]

No delete key on the keyboard either?