Access 2000: ID autonumber dillemma

D

DianaS

Hello,

I am transferring the contents of an old database (software called "RCC") to
a Microsoft Access database. The old database uses a field called ID# to
identify customers. In the new database, I want this ID# field to also have
AutoNumber capabilities. In other words, I want to be able to auto-generate
unique ID#s whenever I make a new record. At the same time, I want to keep
the old ID#s from previous customers. Is there a way to do this?

Thank you,
Diana
 
G

Graham R Seach

Dianna,

All you have to do is import the data, IDs and all. Once you've done that,
you need to check the highest number ID, and add a new record to the table,
but add 1 to the ID. For example, if your highest ID = 123, then add a new
record with an ID of 125. Then delete the new record.

The above procedure resets the Autonumber mechanism.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
 
D

DianaS

Hi Graham,

I have not yet imported the data, but I created a sample database with the
same fields and entered about 10 fake records. When I followed your
instructions (add new record with specified ID# and then delete) I did not
get the desired results. Instead, when I create a new record, the ID# field
does not auto-generate a new unique ID#. I must manually enter in a new ID#.

When you said to "add 1" to the ID#, did you actually mean "add 2"? Because
in your example, you went from ID# 123 to 125. I tried adding both 1 and 2,
but neither method worked. Am I doing something incorrectly?

Thanks,
Diana
 
J

Joan Wild

DianaS said:
Hello,

I am transferring the contents of an old database (software called "RCC")
to
a Microsoft Access database. The old database uses a field called ID# to
identify customers. In the new database, I want this ID# field to also
have
AutoNumber capabilities. In other words, I want to be able to
auto-generate
unique ID#s whenever I make a new record. At the same time, I want to
keep
the old ID#s from previous customers. Is there a way to do this?
Click to expand...

Using an append query, you can append the ID# to an autonumber field. When
you start adding records it will start at the next number.
 
G

Graham R Seach

Dianna,

Is your ID field an Autonumber datatype? If not, then make it so.

Then use a query to import some data from another table, but make sure to
import the IDs as well. For example:
INSERT INTO tblMyTable
SELECT * FROM tblOtherTable

Then, assuming the highest value of the ID is 123, create a query like the
following, to apend a new record:
INSERT INTO tblMyTable (ID, Field1, Field2)
VALUES (124, "dummy text", "more dummy text")

Then delete the record just added. The next time you add a new record, the
ID will be 125.

Autonumber only ever creates sequential numeric values. If your ID is
non-numeric, you'll have to write code to create the new ID.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------
 
J

Joan Wild

Graham R Seach said:
Then, assuming the highest value of the ID is 123, create a query like the
following, to apend a new record:
INSERT INTO tblMyTable (ID, Field1, Field2)
VALUES (124, "dummy text", "more dummy text")

Then delete the record just added. The next time you add a new record, the
ID will be 125.
Click to expand...


Shouldn't neet to do this, though.
 
G

Graham R Seach

There have been a few reports where the Autonumber field hasn't reset, so I
always advise people do do this.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------
 
Top