I have a database with several bit fields (ACCESS Yes/No). Each time I revised the table in ACCESS I get errors on the bit fields, have to make it local, revise it and then upload it again to SQL. I can't change the fields in SQL to not "Allow Nulls", the table has to be deleted and recreated. I always have the default value in ACCESS set to 0, but on later revisions I still get these errors. Any way in ACCESS to make it not "Allow Nulls"?
Sami Shamma
@Reply 18 months ago
SQL Server Bit fields cannot be Null.
Kevin Robertson
@Reply 18 months ago
Set the Default Value on the Server to 1 or 0.
For existing records run an UPDATE Query on the server to set their values.
In Access, set the Default Value of the field on the Form to either Yes or No.
Robert RaceOP
@Reply 18 months ago
Thanks. After researching it again, I cannot change the column in SQL to "Allow Nulls Values" without deleting and recreating the table in SQL, but as Kevin noted, I can change the Default value to 0 for all the bit fields in SQL that should solve the problem. Bit fields seem to be an issue between SQL and ACCESS.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access SQL Server Lessons.
