Cannot insert the value NULL into column

[JLForum]

Hi guys, I did a DTS conversion of my Access 2K database into SQL Server. All the data went through fine as far as I can tell. I then created an ODBC connection to it and am trying to use my old Access functions. I took my old Access database, replaced all the old tables with linked ODBC tables with the same name, etc. My forms open up correctly, I can browse through them properly, etc. However, if I try to enter a record, it states that I "cannot insert the value NULL into column". This occurs if I try to create a new record. Please note that this record used Autonumber from Access 2K and was the primary key. How do I get the SQLServer to recognize it? The autonumber usually displays on the form I am using once I begin to enter information, but there is no number appearing. When I try to save the record, I get that message. Please help!!! Thanks.
Sinceerly,
JL

 

[Krickles]

Open Enterprise Manager --> Databases --> Open your database --> Right click on table name --> Choose Design

Left click on the ID field. It should be either numeric or int. At the bottom left under Columns, Identity should be Yes.

I hope this helps.

Krickles | 1.6180

 

[JLForum]

Hi, thanks for the tip. It seems to write properly into the linked table if I access it straight and add a value to first and last name. When I try and create a new record after that, it will then add the primary key value. My question now is how do I get the forms to recognize that? If I try and add the first name/last name via a subform, it does not work. Normally it would add the autonumber primary key to the record. However, now it does not. How would I get Access (or SQL Server) to generate the proper new primary key value and insert it in. It currently is the same null problem on top.
One additional question. The reason I am doing this is because we have multiple sites accessing this database. Originally, we had Access databases that used linked tables back to a central Access database. Data was then entered and pulled through this method. This was not the most reliable way, as it caused a lot of network traffic and was ridiculously slow (as I understand it, Access pretty much sends the entire database across the network despite the fact that you just want to query it, etc.). I am hoping this is not the case with SQL Server. I am currently wanting SQL Server to host the data through the DTS conversion. I then replaced the local tables on the Access database with SQL Server linked tables of the same name. Will this cause it to be slow because of network traffic as well? If so, what is the correct design method for this? Thank you guys so much.
Sincerely,
Jon