Converting from Access to SQL

[crcomputer]

Currently I'm performing a migration from a microsoft access database to an SQL Express 2010 database.

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)

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.

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.

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.

I can't understand why....

Any help would be greatly appreciated.

 

[gmmastros]

In SQL Server, auto-number is called "Identity". I only mention this because it will help you with your google searches.

You do not need an identity column in all tables, however, when you link tables from Access, you do need a primary key on the table. Does your Items table have a primary key?

If you are not sure, you can run this in SQL Server Management Studio:

sp_helpindex 'Items'

Look closely at the index_description column. There should be one that says 'clustered, unique, primary key'.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[crcomputer]

yes the primary key is itemID and it is autonumbered (has an identity which increments by 1.

Should i disable the identity feature?

 

[gmmastros]

When you add a new customer, I assume you are getting a recordset of items from the items table. Are you including the identity column from the Items table in the recordset you are returning from the table?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom