Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JL_Forum
Starting Member
2 Posts |
 Posted - 2004-07-23 : 20:06:47
|
| 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 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-25 : 13:37:53
|
| Is there a primary key defined on the table in SQL Server? If not, you will need to create one.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-25 : 19:47:12
|
| Also - you need to set the row as Identity. This is equivalent to Access' autonumber....I'm sure that was what Derrick meant to say.... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-25 : 20:10:54
|
| blah,blah,blah :) Thanks TimmyMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
JL_Forum
Starting Member
2 Posts |
Posted - 2004-07-26 : 14:58:03
|
| Hi rm, 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 |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-07-27 : 06:10:10
|
| I could be wrong, but I think that you would be better off using pass through queries than linked tables.-------Moo. :) |
|
|
|
|
|
|
|
|
Cannot insert the value NULL into column