Autonumber ID Field

[DreamGenius]

I'm designing a database into which a lot of Excel data will be imported. The tables I've created in Access have Autonumber primary key fields. I've normalised the data in Excel, using ID numbers to tie the tables together.

I know that I can't turn Autonumbering on and off. Can I change the ID field to an Integer, import the data and change it back to Autonumber when all of the data has been imported?

If I can't do this, is there an easier way? I've got nearly 11k lines of data to import into separate tables, the ID on one of which is the foreign key to all the others. I don't really want to import the data and then have to tie it all back together again.

Struggling on a Sunday with a deadline leaning over my shoulder. Thanks in advance for your help.

 

[RainLover]

Alan

Using Autonumber is my prefered way of doing things. However to overcome your problem you will need to go through a couple of extra steps.

Create a tempory field in your table and call it something meaningfull. Import your data from excel but place the Excel Primary key in this field. Do this to all your imported tables.

you should have now Primary keys and where applicable Foregin Keys in all your tables. But Autonumber is not acting at this stage as your Primary Key.

Now create an Update query to replace the foregin key in each table with the Autonumber in the parent table.

You do this in the query grid. Add both Tables and join them via the excel PK and FK. Then update the FK to that of the proper PK which is the Autonumber. Once finished all the Tables then go to > Tools > Relationships and create your relationships. Making sure you enforce referential integrity. If you can't enforce referential integrity then you have a fault somewhere.

Finally when successful delete that tempory field which was the PK from excel.

Good luck with the project

 

[neileg]

I don't really follow what RainLover is advising, so sorry if this duplicates.

You can't change a number field to an autonumber field, Access won't let you. What you can do is append records to a table with an autonumber field and the table will accept existing values so long as they are unique integers. So massage your data in Excel, create empty tables with the approriate autonumber fields in Access and then import your Excel data and append it to your empty tables.

 

[RainLover]

Sorry Neil.

What part do you not understand. What I was attempting to do was to create new Relationships Via the Autonumber.

 

[Atomic Shrimp]

It always boils down to this:

• If you want unique numbers you can't fully control (i.e. that are meaningless beyond their use as a key), use an autonumber
• If you want meaningful numbers you *can* control, use something else (you can have them in addition to an autonumber)
• If you want to control autonumbers to make them meaningful, change your mind and do one of the above instead

 

[neileg]

Sorry Neil.

What part do you not understand. What I was attempting to do was to create new Relationships Via the Autonumber.

I don't understand why you want to ditch the existing PK values from the Excel files. You can retain them and use the automunber for new records.

 

[RainLover]

I don't understand why you want to ditch the existing PK values from the Excel files. You can retain them and use the automunber for new records.

That sounds good.

So how does one use the old PK for old records and the new PK for new records.

My appologies this is new to me. Please explain.

 

[neileg]

Create your table with an autonumber PK. Append your old data to the table including the existing PK values. The autonumber PK field will accept the existing values so long as they are integer and unique. New records will autonumber following on from the highest value in the appended data (probably, subject to Access's usual vaguaries).

 

[RainLover]

Thanks for that Neil.

I must give that a try.

 

[Pat Hartman]

Mike,
DreamGenius isn't trying to control the autonumber, he is trying to import existing values so that he doesn't have to change the foreign keys in the related tables. Access supports this via an append query.

 

[Atomic Shrimp]

Fair enough... as long as the key values in Excel are indeed unique and compatible with the target autonumber field.

 

[RainLover]

Neil

I tried and you were correct.

Thanks

 

[neileg]

Neil

I tried and you were correct.

Thanks

I knew I knew, but now you know, you know I knew and now I know you know.

 

[DreamGenius]

I'm coming back to this thread late, having just read that you've all been suggesting and investigating the very same solution that I discovered by accident.

I had assumed, and I know what 'assume' does, that Autonumber was solely system-generated. Of course, it isn't and you can import data into any such a column provided of course that the data follows the rules. This is the same as for any other data type and, having learned the lesson the hard way, I'm fairly sure that I won't forget it in a hurry!

I think I raised a related but different query on another thread, but this same knowledge helped me in another area. For another customer, I have a master database and six slaves, that I inherited. The slaves have data entered into them on site, which later needs to be imported into the master.

Replication wasn't on the cards when I started and it isn't that easy to apply retrospectively. However, by applying a large seed, unique to each slave, when importing the data, I can preserve the data integrity. Thus:

• Slave1 - Autonumber + 100,000
• Slave2 - Autonumber + 200,000
• Slave3 - Autonumber + 300,000
• Slave4 - Autonumber + 400,000
• Slave5 - Autonumber + 500,000
• Slave6 - Autonumber + 600,000

This prevents me writing over any existing data, by making Record 864 from Slave1 into Record 100864 while the same record from Slave2 is 200864. Doing it this way, I can still have an Autonumber key which is Unique.

I should perhaps make clear that this distinction is only made at the import stage. Otherwise, the table design is exactly the same in the Master and Slave versions. I don't think you can force a seed on an Autonumber field - refer to Mike Gurman's post above!