Duplicate auto numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What causes the table to produce duplicate auto numbers? Is it because if that number is deleted from the table, does the table uses that number again? My ID (primary, auto number) is duplicating and I can't figured out why. Once the field name "Status" is complete, the record appends to "Archive" table and it gets deleted from the original table. Now I have same ID number in the main table and the "Archive" table. "Archive" table is formatted the same. I copied and paste structure only from the main table. Please help

Thank you!
 
Georgia

If you execute a query that directly inserts a value into the Autonumber
field, then you could rightfully insert a duplicate value. Do you know if
you have any queries that perfom this action?


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Georgia said:
What causes the table to produce duplicate auto numbers? Is it because if
Click to expand...
that number is deleted from the table, does the table uses that number
again? My ID (primary, auto number) is duplicating and I can't figured out
why. Once the field name "Status" is complete, the record appends to
"Archive" table and it gets deleted from the original table. Now I have
same ID number in the main table and the "Archive" table. "Archive" table
is formatted the same. I copied and paste structure only from the main
table. Please help.
 
Yes, when the status changes to "Complete" it appends to "Archive" table and deletes it from the original table. Which that inserts ID numbers directly in the Archive table. What do you suggest that I should do

Thank you
 
Is it the Archive table that is receiving the duplicate autonumbers?
If so, it sounds like you are compacting and repairing the database sometime
after you are deleting the records out of the master table. In which case,
the autonumber would then be reset back to 1.

Let me know if this is the senario that is happening for you.

Thanks,
Rob

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Georgia said:
Yes, when the status changes to "Complete" it appends to "Archive" table
Click to expand...
and deletes it from the original table. Which that inserts ID numbers
directly in the Archive table. What do you suggest that I should do?
 
The assumption I have is that the Master table and the Archive table are
duplicate in structures. The Master table, I assume, has a Primary Key field
that is of type 'autonumber'. The equivalent field in the Archive table does
not necessarily have to be 'autonumber' but it should be of type 'Long'.
Based on this, there should be no concern/issue with the moving of records
from the Master to the Archive, that is, the records from the Master are
copied and deleted.

It is my belief that there is 1 of 2 things happening in the application:
1. Some process is compacting and repairing the database some time after
transfer from the
Master table to the Archive table occurs; or

2. Some process is occuring to reset the autonumber in the Master table.

If #2 is occurring, then I would suggest for this to not occur... unless
there was some absolute necessity for this to occur.

If #1 is occurring, then find out WHEN it occurs.

See what information you can provide back and I will see what I can best to
to help you continue from here.

Rob

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
ACCESS said:
The numbers were repeating in the master table or one in master table and
Click to expand...
one in archive table. (in this case, it would not append to the archive
because the number already exist in the archive table).
What would you recommend to fix this? should i have more than one primary
Click to expand...
keys or no primary keys at all??
 
I am going to set the "ID" to number instead of "Autonumber" as you suggested and see how it will do.
The database is compacting and repairing sometimes. I get an error messages when I log into the database that says something like " Someone shut the database unproperly and need to run compact/repair "....if i don't click yes, it will not let me log into the database. Hopefully setting the Automober in my archive table will help

Thank you so very much for your assistance
 
Just let me know how things go.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Georgia/Access said:
I am going to set the "ID" to number instead of "Autonumber" as you
Click to expand...
suggested and see how it will do.
The database is compacting and repairing sometimes. I get an error
Click to expand...
messages when I log into the database that says something like " Someone
shut the database unproperly and need to run compact/repair "....if i don't
click yes, it will not let me log into the database. Hopefully setting
the Automober in my archive table will help.
 
Back
Top