Autonumber Primary Key Dupes (1 Viewer)

[TheSearcher]

Hello All,

I have a program in which users create progress notes for clients with disabilities. When they begin writing a new note a Note_Id is assigned to it. This along with the client’s basic infoformation is then appended to a table called tbl_BasicInfo.
Also, for each new note, five goals specific to that client are appended to the tbl_Goals table via an Insert query. Tbl_Goals has an autonumber as a primary key.
Occasionally, when the five goals are appended each one is duped on tbl_Goals (even though it has an autonumber as a primary key). The seeding of the autonumber is correct. I have code that checks this.
So, the two issues are:
• Autonumber primary key allows dupes.
• After the dupes are allowed the primary key disappears.
I resolve the immediate issue by deleting the dupes, recreating the primary key and compacting the database.
Does anyone have any ideas on why an autonumber primary key would allow dupes?

Example of dupes on tbl_Goals:

ID_PrimaryKey	Task_No	Note_Id	Client_Name	Goal	Assistance_Needed
41479	1	6451	Mr. Client	Effective Communication/Self-Confidence	Leading Questions
41479	1	6451	Mr. Client	Effective Communication/Self-Confidence	Leading Questions
41480	1	6451	Mr. Client	Functional Memory	Verbal Instructions
41480	1	6451	Mr. Client	Functional Memory	Verbal Instructions
41481	1	6451	Mr. Client	Information Processing/Following Verbal Directions
41481	1	6451	Mr. Client	Information Processing/Following Verbal Directions
41482	1	6451	Mr. Client	Planning
41482	1	6451	Mr. Client	Planning
41483	1	6451	Mr. Client	Problem-Solving/Decision-Making	Leading Questions
41483	1	6451	Mr. Client	Problem-Solving/Decision-Making	Leading Questions

Thanks in advance for your help!
TS

 

[The_Doc_Man]

Usually there is an issue of some type of corruption, since autonumber PKs aren't supposed to do this. However, there are exceptions to everything. I think I recently saw that it is possible using an INSERT INTO with explicit field values for everything to override an autonumber. If you have a bound form, this wouldn't be likely to happen because the automation behind the scenes is unlikely to trigger explicit insertions on a PK. But if you were for some reason bypassing the automation and using explicit INSERT actions, that might do it.

My first bet is on corruption. The other situation is less commonly seen.

 

[TheSearcher]

Hi Doc_Man - It's definitely corruption. That's how I'm alerted to the problem. The users can no longer use the application.
The question is: did the fact that the primary key allow dupes create the corruption - Or did the corruption cause the dupes? Either way it's a problem - and it's occurred several times already.
I am using an INSERT INTO with explicit field values in an unbound form - but I'm not inserting a number for the autonumber field.

 

[Pat Hartman]

@TheSearcher See if you have any mainform/subform pairs bound to the same table. This has been know to break the seed. The only other issue I've seen, and I think this one has been fixed, is if you have an autonumber in a table but do not have a unique index on it. Instead you have some other field or multiple fields defined as the PK. C&R sorts all tables on PK when it rewrites the data into the new database and for some reason, this confuses the autonumber because the last record is no longer necessarily the one with the highest value.

 

[The_Doc_Man]

To answer your direct question, the corruption corrupts the PK. If you don't have corruption, you are unlikely to get dups on an autonumber.

 

[TheSearcher]

Is anyone aware of a diagnostic tool that can be used to trace activity and help determine what's causing the corruption in MS databases? I remember using two tools many years ago to trace an issue with SqlServer.

 

[JonXL]

Is ID_PrimaryKey the autonumber field for tbl_Goals?

 

[TheSearcher]

Yes. As you can see, it has duplicate entries.

 

[ebs17]

I mean the table is corrupt => rebuild table, transfer contents via append query

I have no idea how a unique index could allow duplicates, nor how a unique index should go away on its own.

What is also strange: not only the ID is duplicated, but also content. A self-doubling of entire records is even more unlikely than just damaging an increment's value. A multiple execution of the same append query, on the other hand, is obvious.

 

[JonXL]

I was thinking in similar terms to @ebs17 that the table possibly doesn't have the validation OP thinks it does and that the query process is responsible for the duplicates. It could be getting run multiple times or possibly the underlying query has some JOIN statements that are causing records to return more than once (and thus insert more than once).

Certainly know I'd be running down that possibility before rebuilding everything.

 

[The_Doc_Man]

Is anyone aware of a diagnostic tool that can be used to trace activity and help determine what's causing the corruption in MS databases? I remember using two tools many years ago to trace an issue with SqlServer.

Unless Microsoft has one, the odds are kind of low to get such a thing. Part of that is because MS Office code is not open-source. If we HAD open sourcing, you can bet that diagnostic tools would have been generated in a heartbeat after the sources became open.

The only way to trace activity is therefore to "instrumentalize" your code. That is, create an event table and build some logging code that you can call from anywhere in your project (implying, of course, that we are talking about recordset operations from a general module). Then put calls to the logging routine in all of your form class module code. You can use conditional compilation to compile in your testing or not compile it. See, for example,

Understanding conditional compilation (VBA)

Office VBA reference topic

learn.microsoft.com

You can build the app with testing enabled. Then when it is working and you don't need to track things, change the "conditional" flag from 1 to 0 and do a decompile and recompile.

How to Decompile a Database: Microsoft Access Tips and Tricks from DataGnostics

It is possible to leave the code non-conditional if you don't mind accumulating a lot of data, but eventually you would have to export and delete the logged data (after which you would then need to do a compact & repair.)