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.

  • If it's not too late (i.e. if you haven't solved this already), would you post the definitions of your parent table and a child table as SSMA has created them in SQL Server. – mendosi Nov 27 '16 at 23:42
  • Have you considered having the data in SQL and just attaching access as a front end? Let SQL manage the primary keys with identity columns. Google access linked tables. – Sir Swears-a-lot Nov 28 '16 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.