Import

[bladelock]

Here's a piff:
I have a .dbf file that I imported into Access, in the .dbf I have a unique field and a text field called "ID" (1-100) and "Names". After I import the file to Access, I want to change the ID to autoincrements. Only problem, Access want s you to Delete the field and recreate a New autoincrement field, which is fine with me, but when I recreate the field the numbers are not the same as the ID field was. The numbers are off. Everything was in order before I recreated the field, what can I do?

 

[Dirk Goldgar]

Here's a piff:
I have a .dbf file that I imported into Access, in the .dbf I have a
unique field and a text field called "ID" (1-100) and "Names". After
I import the file to Access, I want to change the ID to
autoincrements. Only problem, Access want s you to Delete the field
and recreate a New autoincrement field, which is fine with me, but
when I recreate the field the numbers are not the same as the ID
field was. The numbers are off. Everything was in order before I
recreated the field, what can I do?

You should be able to do the following:

1. Create a new, empty table with the fields defined the way you want
them, including ID as an autonumber field.

2. Create a query that selects all the records from the old table and
uses the CLng function to convert the ID field to a long integer.

3. Change that query into an append query, appending each of the fields
(including the converted ID field) to its counterpart in the new table.
The SQL might look something like this:

INSERT INTO NewTable (ID, Names, OtherField)
SELECT (CLng(ID) As NumID, Names, OtherField)
FROM OldTable;

4. Run the append query, and it should create records in the new table
that match the old one.

5. Rename or delete the old table, and rename the new table to the old
name. If the old table had any enforced relationships to other tables,
you'll have to delete those relationships first, then restore them
afterward.

 

[Norman Yuan]

First of all, if you want to use AutoNumber column as ID, then you should
not care whether the ID value is in order or not. The only purpose to use
AutoNumber is to guarantee a unique value for the column in entire table,
thus, "AUTO" number, so that you do not have to roll out your own algorithm
to get an unique value.

If the table with ID column you imported is not linked to other table (or
you do not import other related tables to this database) then you are going
to be fine to use a newly created AutoNumber column as ID. If you really
want ID value in certain order, than you cannot use AutoNumber and, of
course, you must have some way to ensure the values in that order and keep
its uniqueness (say, if you want to delete a row in the table, how do you
fill the value gap?).

Here's a piff:
I have a .dbf file that I imported into Access, in the .dbf I have a

unique field and a text field called "ID" (1-100) and "Names". After I
import the file to Access, I want to change the ID to autoincrements. Only
problem, Access want s you to Delete the field and recreate a New
autoincrement field, which is fine with me, but when I recreate the field
the numbers are not the same as the ID field was. The numbers are off.
Everything was in order before I recreated the field, what can I do?