Import data with autonumber column

[BSman]

Does anyone know of a way to import data into a table from a table (with the same layout) in another Access database where the table has an autonumber column? The data in the two tables does not contain any duplicate numbers.

I know this can be done if you have an SQL Server back end, but I don't know of a way to do it in Access.

Thanks,
Bob

 

[Remou]

When you say 'no duplicates', do you mean that the autonumbers are not duplicated? If so, impoting all the fields may suit, if not, you will need to leave the autonumber out when importing. Either way, here is a suggestion:
[tt]strSQL = "INSERT INTO tblTable ( ID, Field1, Field2 ) " _
& "SELECT tblTable.ID, tblTable.Field1, tblTable.Field2 " _
& "FROM tblTable IN 'C:\Docs\Tek-Tips.mdb';"
DoCmd.RunSQL strSQL[/tt]

 

[BSman]

You're right, I ran an experiment and they can be inserted without a problem as long as the autonumbers are not duplicated, which I thought they weren't in the two databases I need to combine. Of course, now that I looked closer it turns out that they are duplicated, so I'll have to change the incoming values (which are only referenced in one other table) in the main import table by adding a high enough number to avoid duplication with the autonumber values in the main database.

Thanks.