Import Autonumber Problem

[David Gibson]

I'm trying to import a table with a serial number field from a Dbase app into
Access 2007, but I want to use the autonumber field in Access. I know I can't
change the imported serial number field to an autonumber field. Is there any
way to import this table so the serial number field becomes an autonumber
field? Or is there any way to get the autonumber field to match the value in
the serial number field?

 

[Jeff Boyce]

David

You are (mostly) correct in assuming that you can't stuff your serial
numbers into an Access Autonumber field ... after all, "autonumber" means
Access automatically assigns the number!

But there's no reason you couldn't have an Autonumber field in Access that
you (and Access) would use as a primary key, and a second field of a data
type compatible to your serial number, which you COULD stuff. I mention
"compatible" because some folks would call "A1Z339#2" a "serial number",
even though you'd never try to do math on it (therefore, it isn't really a
"number", but a text string).

And you can use indexes to ensure that no two serial numbers are the same...

(however, and you haven't described your situation enough for us to know
this, if you used items from more than one manufacturer, each manufacturer
could decide to use "A1Z339#2" as a serial number -- are you quite confident
that your "serial numbers" are already unique, and will always be unique?)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Clifford Bass]

Hi David,

Yes. Create a link to the external table and then create an append
query that uses the linked table as the source and appends into the Access
table. Map the serial number field from the dBase table into the Access
table's autonumber field. As long as the numbers do not already exist, they
will be retained.

Clifford Bass

 

[David Gibson]

Thanks for both replies. Your solution is very close. The problem I have is
the existing serial number field is not sequential. They were incremented by
one, but with deletions, some numbers are missing. I think I can solve that
problem by inserting blank records in the old table to fill in the missing
serial numbers. Then the old serial number field will match the new
autonumber field, and I can delete the blank records from the new table.

One other problem on the append query. There will always be a 1 in the first
record of the new blank table, so I'm assuming my old table cannot have a 1
as a serial number?

 

[Clifford Bass]

Hi David,

The missing numbers should not matter. And a number 1 is not important
as long as you have not added any records. The append query will just import
the values as-is. And it will not matter where it starts in the old
database. It will reset the next number to use to the number just after the
highest number appended. Give it a try in a test database. If there are
problems, let me know.

 

[David Gibson]

Thanks, Clifford. You are correct. The missing numbers don't matter. The
problem with the number 1 is, if I have a record with a serial number 1 in my
import table, Access won't allow duplicates in the autonumber field, so the
append query won't run. It's a simple solution to renumber the 1, do the
append query, then correct the single record data after. Thanks.

 

[Clifford Bass]

Hi David,

Yeah, existing records with values in your import table, whether 1 or
100, will cause problems. Oh well, all that matters it that you can
accomplish what you need to do.

Glad to help!

Clifford Bass