Maintaining AutoNumber fields in DB Conversion 2

[gchaves]

I have a MySQL database which has several tables. In some of the tables, the primary key is an autonumber. I need to convert this db to an Access db and maintain the ID for the table as that ID is related to other tables. Is there a way to convert MySQL to Access and still maintain the integrity of the ID field with the field set to AutoNumber? If I try to import the data, I can set the ID field as the PK, but I cannot set it to AutoNumber so that it will continue to increment as rows are added.

Any help would be greatly appreciated!

 

[BNPMike]

If you declare a Jet field as autonumber you can load in your own numbers as long as they don't conflict. After you have done that, Jet will allocate new numbers starting above the top of the range you have inserted. Import the data into an existing (empty) table.

 

[gchaves]

Not sure what you mean by a Jet field?

 

[BNPMike]

You said you wish to convert your database to Access. Access is a programming environment. Its native databases are Jet and MSDE (MS SQL Server) although of course it will work happily with MySQL.

 

[gchaves]

That is what I thought you meant. I performed a data dump from my MySQL database and then tried each of the following:

1.) Importing the data from .csv files. I set up my ID as the primary key, but Access would only let me set it as a number, rather than an autonumber. Once set up and the data imported, Access will not let me switch the field to an autonumber.

2.) Copying and pasting the data from my .csv files into my tables. However, once pasting the information, the autonumber ID field was reset to whatever row it was pasted into.

Is there a better way to perform this conversion? (It is my first one.) Any help you can give would be greatly appreciated!

Thanks!
G

 

[BNPMike]

1) Import the data into a staging table, with your autonumbers

2) Create your desired Access jet table with an autonumber field.

3) Design an append query to insert the data from the staging table into the target table. Jet will accept your numbers in its autonumber field. For safety, order the query in ascending autonumber order.

 

[gchaves]

Awesome! Your tip worked perfectly! Thanks!!!!