Access 2000: ID autonumber dillemma

[Guest]

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

 

[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)

 

[Guest]

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

 

[Joan Wild]

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?

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.

 

[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)
---------------------------

 

[Joan Wild]

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.

Shouldn't neet to do this, though.

 

[Joseph Meehan]

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

You have not said so, but just incase the exact order of your ID#'s is
important, you should not be using autonumber as you can not trust it to
provide consecutive numbers. They can get strange for a number of reasons.

 

[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)
---------------------------