SQL Server Migration - Issue with AutoNumbers

[MSAccessRookie]

Recently, I became involved in a project that required the Migration of an Access 2003 Database to SQL Server 2008. I tried to use the Upsize Wizard, but that left me with too many issues to contend with. I got SSMA installed and working, and the project was listed as having been migrated succesfully. As far as I can tell, the Identity Fields that were created should work properly.

During the testing I discovered that AutoNumbers were not being updated when new records were created on a Form that was bound to a table, and as a resulkt, I cannot add new records to any SQL Server Tables. I do not recall having this issue on a previous project that used SQL Server 2005.

As far as I can tell, the Identity Fields that were created by SSMA should work properly. Does anyone have any ideas as to what I should be looking for?

-- Rookie

 

[mdlueck]

Have you checked the usual suspects, such as nullable bit fields in SQL Server?

 

[MSAccessRookie]

Have you checked the usual suspects, such as nullable bit fields in SQL Server?

I checked all of the issues that I encountered in my last post, and none of them seemed like they would have any effect on Auto Number Fields.

http://www.access-programmers.co.uk/forums/showthread.php?t=224766

Since I do not recall having this issue on a previous project that used SQL Server 2005, I was wondering if there was something different about SQL Server 2008.

-- Rookie

 

[SQL_Hell]

Assuming the identity field is your primary key, how is it possible for the records to be inserted without the primary key being updated?

 

[MSAccessRookie]

Assuming the identity field is your primary key, how is it possible for the records to be inserted without the primary key being updated?

The database exists and is functioning properly under Access 2003. It also functions properly when the Front End (.MDB) is run under Access 2007 or Access 2010. It is after I use SSMA to Migrate the Tables to SQL Server 2008, that it no longer functions properly.

When a new record is added, the AutoNumber Field does not update and remains NULL, so the record cannot be saved. Because of the way that the Field is defined, it cannot be updated using Access, which is what I would expect from an AutoNumber Field.

SQL Server does not appear to be updating the Field, and I am not sure what needs to be changed in order to get it to do so.

 

[mdlueck]

Because of the way that the Field is defined, it cannot be updated using Access, which is what I would expect from an AutoNumber Field.

Do not take Access's word for it. Check the filed in SSMS and make sure it has the correct IDENTITY property set on it.

 

[MSAccessRookie]

Do not take Access's word for it. Check the filed in SSMS and make sure it has the correct IDENTITY property set on it.

My apologies. That is what I meant. I used the SQL Server Management Studio to view the Properties of the Field, and as far as I can tell, it is defined in a way that I would expect to work as an AutoNumber.


In Access

• The Data Type is Long Integer
• New Values is set to Increment
• Indexed is set to Yes (No Duplicates)
• There is no Lookup Defined

On SQL Server

• The Data Type is int
• The Condensed Data Type is int
• The Identity Specification is set to YES
• The Identity Increment is set to 1
• The Identity Seed is set to 1
• The Size is set to 4

I was looking to see if I could find out what was wrong, so that I could get SSMA to properly Migrate this and similar Fields.

-- Rookie

 

[mdlueck]

All right then, try to issue an INSERT via SSMS and see if the record is assigned an IDENTITY number.

 

[MSAccessRookie]

All right then, try to issue an INSERT via SSMS and see if the record is assigned an IDENTITY number.

Thanks for the idea. I used SSMS to insert a record, and noticed that the proper ID was created, but it was not Updated until the record was completed and the next record had been selected. I guess this means that I will need to modify all inserts to keep track of the record that is about th be entered so that the number can be displayed for the user, or is there another (better?) way?

-- Rookie

 

[mdlueck]

and noticed that the proper ID was created, but it was not Updated until the record was completed and the next record had been selected

What in the world...???

A bare INSERT SQL should instantly add a new record to the table grabbing a unique IDENTITY number for the key column.

and the next record had been selected

A bare INSERT SQL does not have capabilities to "SELECT next record"

 

[MSAccessRookie]

What in the world...???

A bare INSERT SQL should instantly add a new record to the table grabbing a unique IDENTITY number for the key column.

A bare INSERT SQL does not have capabilities to "SELECT next record"

The bare INSERT SQL did add a new record to the table, but it also did not seem to reserve a new IDENTITY number for the key column, and the value in that column did not show up until the cursor was moved into another record.

-- Rookie

 

[mdlueck]

The bare INSERT SQL did add a new record to the table, but it also did not seem to reserve a new IDENTITY number for the key column

Then what ended up in the column executing the SQL INSERT via SSMS?

and the value in that column did not show up until the cursor was moved into another record.

This sounds like you are attempting via the Access GUI... SSMS does not have an interactive add-hock interface when you are executing a bare SQL INSERT.

You are correct, via a Linked Table in Access, the IDENTITY column when adding a new record will be NULL at first, SQL Server will process the INSERT and return the IDENTITY value to the Access GUI Linked Table. This is working as designed.

 

[MSAccessRookie]

Then what ended up in the column executing the SQL INSERT via SSMS?

This sounds like you are attempting via the Access GUI... SSMS does not have an interactive add-hock interface when you are executing a bare SQL INSERT.

You are correct, via a Linked Table in Access, the IDENTITY column when adding a new record will be NULL at first, SQL Server will process the INSERT and return the IDENTITY value to the Access GUI Linked Table. This is working as designed.

It looked that way to me too. Thanks for confirming what I thought. I will have to check it out before the demo tomorrow, and may need to find a way to let the users see the value anyway.

-- Rookie

 

[mdlueck]

and may need to find a way to let the users see the value anyway.

Then you will have to have inserted the record into SQL Server, received back the ID number, and refresh the Access display.

You can not know in Access the ID number SQL Server is going to assign the record BEFORE actually performing the INSERT operation. Chicken and egg situation... Chicken definitely needs to come FIRST.

There are various posts in this forum for obtaining the IDENTITY number assigned back from a SQL BE DB. It all depends on if you are using Pass-Through SQL or executing a Stored Procedure what exact techniques will be available to you.