SQL Server Question

[Brian]

I know that this is probably the wrong place to put this, but I don't know
where the SQL server communities are. Therefore, I am hoping someone can
help me anyway.

I am very new to SQL server, but have extensive experience in Access. I am
now moving an Access database to SQL Server. One of my tables has about
34000 records. I moved this to SQL and then added a new field called
bit_Supply. Now I cannot modify any of the values of the table in Access.
Here is the problem.

For some reason, Access says the data in the field is "0" (zero). SQL
Server says the data in the field is NULL. The data should be FALSE. If I
delete this field, I can modify the records in Access. If I add it back in,
I can't modify anything in Access. If I manually change the data in SQL to
FALSE, I can modify those records in Access, but can't modify any record in
Access that I did not change to FALSE. Problem is that I have 34000 records
to change...impractical to manually change.

And in SQL, I have the "Allow Nulls" box checked, but I'm guessing because
the data type is "bit", Access is burping at this.

Can anyone offer any help? Thanks.

(and sorry for posting in the wrong spot)

Brian

 

[Rick Brandt]

I know that this is probably the wrong place to put this, but I don't
know where the SQL server communities are. Therefore, I am hoping
someone can help me anyway.

I am very new to SQL server, but have extensive experience in Access.
I am now moving an Access database to SQL Server. One of my tables
has about 34000 records. I moved this to SQL and then added a new
field called bit_Supply. Now I cannot modify any of the values of
the table in Access. Here is the problem.

For some reason, Access says the data in the field is "0" (zero). SQL
Server says the data in the field is NULL. The data should be FALSE.
If I delete this field, I can modify the records in Access. If I add
it back in, I can't modify anything in Access. If I manually change
the data in SQL to FALSE, I can modify those records in Access, but
can't modify any record in Access that I did not change to FALSE.
Problem is that I have 34000 records to change...impractical to
manually change.

And in SQL, I have the "Allow Nulls" box checked, but I'm guessing
because the data type is "bit", Access is burping at this.

Can anyone offer any help? Thanks.

(and sorry for posting in the wrong spot)

Brian

You have already surmised the situation. While SQL Server bit fields now allow
Nulls (older versions did not), Access Yes/No fields do not. Either disallow
Nulls on your SQL Server table or switch to an Integer DataType instead of a
bit.

I'm not sure, but this might be one of the situations where adding a Timestamp
field to the table solves the problem. Easy enough to try at least.

 

[Sylvain Lafontaine]

And don't forget to specify a default value (0 should be fine here) for the
new field when you uncheck the "Allow null" box; otherwise you won't be able
to add the new column to a table with existing data.