I have several test stations with Access databases that have identical structures (12 related tables each). They are independent and all internal PK & FK values (Autonumber PK & Long Integer FK) can duplicate from station to station. The first table in each station has Part Number, Serial Number & Time Stamp fields that, across all stations, will be unique. (no one unit PN/SN can be on two stations at the same time).
The goal is to push all the test data from all the test stations to a SQL Server database. The original PK & FK values do not have to be retained. I am familiar with Access and a total newbe with SQL Server. I have tried to get this working using SSMA & SSMS. I have migrated a single Access sample with partial success. The SQL PK values will increment correctly but the FK values are either NULL or if not allowed, '0'. I would expect there is a standard approach to this issue but to date, I haven't found it.
mendosi, Here is where my ignorance jumps to the top. How do I publish the SQL table definitions?
peter, I checked the 'Link Tables' box and my Access front end is connected. I have several combo boxes that select query criteria that, ultimately, will need pass through queries and / or stored procedures to populate their lists. But until I get passed the PK / FK issue those will have to wait.