Resetting a field property from text to Autonumber in Access 2002

[Guest]

Hi!
I'm trying to reset property values of a field from text to Autonumber and
make it the primary key. I've tried a couple of different things:

I've exported the table to excel, changed the field to a number value,
copied & pasted special, then reimported into Access.

I've also created a new field in design view (as an autonumber value),
exported to excel, copied & pasted special (to hold the number value), then
imported it back in. Seems as if the AutoNumber resets all the IDs to their
own number.

There have been several other variations I have tried to no avail. Would
there be a way to do this in Access without getting into Excel? Are there
any suggestions on how to "trick" Access into re-configuring a field to
AutoNumber?

 

[Allen Browne]

You can create a table with the same structure, add the AutoNumber, and use
an Append query to populate it.

1. On the Tables tab of the database window, select the table.
Copy (Ctrl+C), and paste (Ctrl+V).
Choose the option for "Structure Only."
You now have a copy of the table without the data.

2. Open the new table in design view.
Delete the text field. Save.
Add an AutoNumber field. Mark as primary key.
Save. Close.

3. Create a query using the old table.
Change it to an Append query (Append on Query menu.)
Specify the table when Access asks which table to append to.
Access adds an Update row to the design grid.

4. Add each of the fields (not the "*") to the query grid.
Change the old Text field by adding Val() around it.
For example, if it was named "ID", you want:
Val([ID])

5. In the Update row under this expression, choose the Autonumber field.
(The other fields should automatically go to the Update row.)

6. Run the query (Run on Query menu.)

After verifying that the new table is correct, you can delete the old one.

 

[Guest]

Thank you Allen for your posting! The query idea is a great approach...helps
me to stay in Access.

Also was not clear on where to put in "Autonumber". I put it in the
Criteria field of the first column
Everything went smoothly until I ran the query and got the following error:
'Extra ) in query expression 'PROFILE.Val([AGENCY_PROFILE_ID])]' error. I
did type only the in the Val ([ID]) syntax, so I'm not sure how to
troubleshoot on this one.

Any further suggestion? Thanks in advance!

You can create a table with the same structure, add the AutoNumber, and use
an Append query to populate it.

1. On the Tables tab of the database window, select the table.
Copy (Ctrl+C), and paste (Ctrl+V).
Choose the option for "Structure Only."
You now have a copy of the table without the data.

2. Open the new table in design view.
Delete the text field. Save.
Add an AutoNumber field. Mark as primary key.
Save. Close.

3. Create a query using the old table.
Change it to an Append query (Append on Query menu.)
Specify the table when Access asks which table to append to.
Access adds an Update row to the design grid.

4. Add each of the fields (not the "*") to the query grid.
Change the old Text field by adding Val() around it.
For example, if it was named "ID", you want:
Val([ID])

5. In the Update row under this expression, choose the Autonumber field.
(The other fields should automatically go to the Update row.)

6. Run the query (Run on Query menu.)

After verifying that the new table is correct, you can delete the old one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi!
I'm trying to reset property values of a field from text to Autonumber and
make it the primary key. I've tried a couple of different things:

I've exported the table to excel, changed the field to a number value,
copied & pasted special, then reimported into Access.

I've also created a new field in design view (as an autonumber value),
exported to excel, copied & pasted special (to hold the number value),
then
imported it back in. Seems as if the AutoNumber resets all the IDs to
their
own number.

There have been several other variations I have tried to no avail. Would
there be a way to do this in Access without getting into Excel? Are there
any suggestions on how to "trick" Access into re-configuring a field to
AutoNumber?

 

[Allen Browne]

There does look like an extra square bracket in the error message you posted
(at the end, inside the single-quotes.)

I'm sure you will be able to sort out your matching brackets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen for your posting! The query idea is a great
approach...helps
me to stay in Access.

Also was not clear on where to put in "Autonumber". I put it in the
Criteria field of the first column
Everything went smoothly until I ran the query and got the following
error:
'Extra ) in query expression 'PROFILE.Val([AGENCY_PROFILE_ID])]' error.
I
did type only the in the Val ([ID]) syntax, so I'm not sure how to
troubleshoot on this one.

Any further suggestion? Thanks in advance!

You can create a table with the same structure, add the AutoNumber, and
use
an Append query to populate it.

1. On the Tables tab of the database window, select the table.
Copy (Ctrl+C), and paste (Ctrl+V).
Choose the option for "Structure Only."
You now have a copy of the table without the data.

2. Open the new table in design view.
Delete the text field. Save.
Add an AutoNumber field. Mark as primary key.
Save. Close.

3. Create a query using the old table.
Change it to an Append query (Append on Query menu.)
Specify the table when Access asks which table to append to.
Access adds an Update row to the design grid.

4. Add each of the fields (not the "*") to the query grid.
Change the old Text field by adding Val() around it.
For example, if it was named "ID", you want:
Val([ID])

5. In the Update row under this expression, choose the Autonumber field.
(The other fields should automatically go to the Update row.)

6. Run the query (Run on Query menu.)

After verifying that the new table is correct, you can delete the old
one.

Hi!
I'm trying to reset property values of a field from text to Autonumber
and
make it the primary key. I've tried a couple of different things:

I've exported the table to excel, changed the field to a number value,
copied & pasted special, then reimported into Access.

I've also created a new field in design view (as an autonumber value),
exported to excel, copied & pasted special (to hold the number value),
then
imported it back in. Seems as if the AutoNumber resets all the IDs to
their
own number.

There have been several other variations I have tried to no avail.
Would
there be a way to do this in Access without getting into Excel? Are
there
any suggestions on how to "trick" Access into re-configuring a field to
AutoNumber?