title: Converting Access db to SQL
body: <p>Currently I'm performing a migration from a microsoft access database to an SQL Express 2010 database.</p>
  
  <p>Basically, I have an Access application that searches a customer database. The access app is developed in 2 parts. An access front end on each client called application.mdb and a data backend on a windows 2008 server called data.mdb. The application.mdb has 3 linked tables to data.mdb. which holds customers and contracts and items. The customer table relates to the contracts table (one to many) and the contracts table relates to the items table (one to many)</p>
  
  <p>I imported the tables from the data.mdb into the sql tables by the same name and created the same relationships and configured them to cascade. I then created an obdc connection on the clients and updated the 3 linked tables in application.mdb to point to the tables on the sql server.</p>
  
  <p>I start the application and everything seemed to work great, I can see all the data perfectly and the performance increase was well worth the effort.</p>
  
  <p>Then I found a problem, when I add a new customer to the database it autonumbers the customer table and the contracts table but not the items table.... Thus if I attempt to alters any of the items in the items table for new customers I can not. I get the following error "cannot add record(s); primary key for table "items" not in recordset" which makes sense because SQL had not autonumbered the items table.</p>
  
  <p>I can't understand why....</p>
  
  <p>Any help would be greatly appreciated.</p>