Access Autonumber Problem

[Sarah Marriott]

Hi all,

I know this has probably been seen before, and I know there are lots of info
on the web about the issue of the autonumber field not creating a unique
record.
However, these all tend to be about when data is appended from one table to
another.

This is not the same as my problem.
I ,simply, have a front end db, which links to a back end db. The form in
the front end prompts the user for data and then an SQL statement is used to
INSERT the record to the linked table. Note: the SQL statement does NOT
reference the Autonumber field. This field is also the primary key, so it is
erroring.

This code has been working for MONTHS and suddenly the table is now not
generating unique seeds, virtually everyday. The strange thing is I have NOT
changed any code in the front end - or the structure in the back end.
To solve this problem when it first arose a couple of days ago, I created a
new database and imported the tables. This should have (I think) cleared
this problem!
The code provided by MS in KB884185 works, but for how long?

This is driving me mad, and it is a business essential application so its
not fun!

Do you guys think I should rebuild the front end?
Anyone else have this? Or know where I can find more info?

Cheers

S.

 

[Arvin Meyer [MVP]]

You don't mention your version. This may be a now fixed bug with an Access
2000 format table structure. Make sure that you are fully patched.

Now WORKING ON A COPY OF YOUR DATABASE:

The way to fix the problem is to rename the existing autonumber column in
your main, and any related tables. It should only be an autonumber in the
main table, the others should be of type long integer. Change the column in
the main table to long integer. Add a new column to each table affected, and
name it to the original column name. In the main table, the new column
should be of type autonumber. Maintaining the original links, use update
queries to populate the data in the new foreign key columns of the related
tables. After you are sure that everything is now OK, delete the original
columns and compact the database.

Again, make sure every computer that uses the database is fully patched.