:rolleyes:

I have searched the internet to find the best solution to use as primary key. I have heard that autoNumber might cause different problems, like it will not allow more than one user to open/ and add data at a time.

What could be an alternative to autonumber?

I've never had any problems...

What are your sources that say it's a bad idea?

I read some forum on the internet - sorry, but I can't remember where...

but another thing it said if you have to recreate a table, the autonumber field will end up with a different value, so if used in another table as a foreign key -> big problem...

* and if you one day need to merge two tables, you can't because you cannot have a table with two autoNumber fields..

..just got me a bit concerned about using autoNumber fields...

Apparently you can easily do the key programatically, but then what is the best solution...

Will be grateful for some help! :-)

if you have to recreate a table, the autonumber field will end up with a different value, so if used in another table as a foreign key -> big problem... - absolutely wrong!

If you have data that has an existing numeric unique identifier, you can append those records to a table with an autonumber primary key and map the old pk to the autonumber. The old pk will be used as the autonumber value provided it does not duplicate an already existing value. When your append is done, the next available autonumber will be changed if necessary. So if your present table contains values under 5000 and you add a bunch of records in the 10000-11000 range, the autonumber will be changed to assign values higher than the highest value in the table. So the next autonumber will always higher than the highest table key value.

and if you one day need to merge two tables, you can't because you cannot have a table with two autoNumber fields.. - this would be a problem no matter what. You can't add duplicate keys to any table. If the numbers are not in the same range, they can be added as I said above.

Apparently you can easily do the key programatically, but then what is the best solution... - There are certain situations where you need to assign your own "autonumbers". The most common are - replicas where you can't use a random autonumber and check numbers where you can't have any missing values. In most other cases, I would go with the autonumber.

PS, I would shy away from that website if this is the kind of information you get there.

I have not had any problems with Autonumber fields used as keys. I have merged tables (yes sometimes it requires some manual intervention, depends on the requirments), appended data (as Pat said, it handles it just fine). But autonumbers are not required, just easy. If you have your own primary key (like an item number, or unique employee number as an example) you can use that as the primary key instead. The same problems can arise with using those keys as well. Example, my company aquired another, we merged their employees with ours on our hr/payroll system, but first we had to scan for duplicates emplyee numbers and manually handle about 50 of the 300+ employees because of duplicate employee numbers. I have read on some other sites (and a couple posts here beleive it or not) that say autonumbers are bad. But our ERP system uses autonumbers for internal keys, it allows your own keys as well (like invoice number, or order number etc.) but it still uses the autonumber as the internal key and foreign keys on other tables pointing back. In MSaccess I like autonumbers because it seems to deal with those as a primary key better in most instances (like it was designed or optimized for those), so I would not worry to much about it. Besides the problems you pointed out out are either very rare, or issues with any type of key you choose to use. Just my 2 cents worth.

In a new table a foreign key is no longer treated as unique and will allow duplicates. This is the basis of forming a relationship where one field can link to records of another table and the basis of one to many relationships.