|
|
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?
|
|
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
http://mvp.support.microsoft.com/
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
"David Gibson" <DavidGibson[ at ]discussions.microsoft.com> wrote in message
news:424A17EF-A7DE-4889-92C6-B524841CBEAD[ at ]microsoft.com...
[Quoted Text] > 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?
|
|
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" wrote:
[Quoted Text] > 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?
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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?
|
|
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" wrote:
[Quoted Text] > 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" wrote:
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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" wrote:
|
|
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
"David Gibson" wrote:
[Quoted Text] > 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.
|
|
|