2

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.

Improve this question
3
  • 2
    Can you post the table def for Items? Commented May 23, 2011 at 12:44
  • Are you trying to add records in forms that are bound to SQL statements that have more than one table in them? Commented May 28, 2011 at 23:46
  • Also, be sure each table has a timestamp field. You might have had better results if you'd used the SSMA for Access to migrate your data, as it's smart enough to convert things from Jet/ACE to SQL Server in ways that minimize the possibility of things breaking (and it adds the timestamp fields for you automatically). Commented May 28, 2011 at 23:49

3 Answers 3

Reset to default
2

Well, just manually adding record direct in the items view should tell you if the autonumber is working. You MUST get the autonumber working when you edit + use in direct table view.

As always these kinds of issues comes down to the details. One thing that's different when using a SQL based backend compared for access applications is the generation of auto numbers (primary key) does not occur on server based systems until record is actually saved. When working with the jet based back end, the auto number is available the instant the record is dirtied.

So I would check if you have some type of code or event running in the application that is attempting to use as primary key value before the record been actually saved.

Usually access does a pretty good job. For example when you build a form in access, and then have a sub form in access to edit child records (and a child table), then as a rule when the focus switches from a main form to a sub form, access will force a save of the main record. This thus means the primary key (auto number column) is now available for correct functioning of the relationship. Access can and will use this PK value and insert this value into the foreign key value column in this child table for you.

However access will only do above for you WHEN you correctly set up the link master and link child settings in the sub form control. As a general rule when building forms in regular access, Access can detect the settings required and insert the correct values into the link master and link child settings for you. However, the detection of the FK column will not occur with linked tables.

So when you use SQL server, you have to edit and set these values manually in the sub form control. So I would check your link master and link child settings in the sub form you're using to edit this data, and ensure that the correct values are set. If this is VBA code, then ensure the record is actually saved before attempt to use and grab a PK value.

I should point out that even in non SQL server based applications, it is the setting up of the link master + child settings in the sub form that allows access to setup and maintain this foreign key value for you. So access is always had the ability to insert these values for you, and it'll do so with you about having to write any code at all. So during the editing process to insert and maintain these values Access does all of the work for you (so it's not the data engine that inserts these FK values for you, but the user interface or in some cases code you write)

So access will not setup and insert these correct values unless you set up the link master + child settings in that sub form control.

I would simply check if your link master and child master settings are correct in any sub form control you are using here.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

that is exactly the problem, i didnt realize SQL would not manage the auto increment in the same way access does. Do you have any sample code that would manage this process?
Albert just explained how to manage it by designing your UI in a way that Access can take care of the problem for you. There's no code required.
1

This sounds like a stupid answer but check the Items table to be sure that auto-numbering is turned on.

Improve this answer

1 Comment

As I mentioned above, in the SQL DB I have set the primary key in the items table as a column named "itemID" and it is has an identity which incremented by 1
1

One of the things I would suggest whenever you migrate a Jet/ACE database to SQL Server is to thoroughly review the database design, e.g.: the implementation of keys and constraints, choice of data types, choice of indexes, etc. Jet/ACE is a very different thing to most SQL DBMSs so you shouldn't assume that a database design that worked well for Jet/ACE is automatically suitable for a SQL DBMS. Upsizing wizards won't always identify every possible issue.

In SQL Server the nearest equivalent of an "auto-number" is the IDENTITY property. Check to be sure which columns are IDENTITY in your tables and create an IDENTITY column if you need one.

Improve this answer

1 Comment

In the SQL DB I have set the primary key in the items table as column named "itemID" and it is has an identity which incremented by 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.