bind checkbox with a bit column in a SQL Server linked table

Question

I'm trying to bind a checkbox with a bit column in a SQL Server linked table.

I had an error about the type at first, then I went in my linked table in access and changed the display control of the column to checkbox instead of textbox.

Now I have a write conflict error when I save the record.

I tried the solution about adding a timestamps in the table but I got the same problem

Is it possible without VBA?

Thank you

The code of my save button

Private Sub btnSave_Click()
  On Error GoTo Err_btnSave_Click
      DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
      DoCmd.Close
  Exit_btnSave_Click:
      Exit Sub

  Err_btnSave_Click:
      MsgBox Err.Description
      Resume Exit_btnSave_Click

End Sub

I don't think there's anything wrong there. I got the error only when there's a checkbox bound to a bit column

I tried to bound a textbox to the same column. The value is false by default and I don't have the error if I don't change is value.

but when the form is dirty, even if I change the value of an another control, I got the error.

I don't understand. It's like if Access doesn't understand that there's only one user when there's a control bound to a bit column.

The last time I had this problem, I gave up and I change the type for a small int but I'm curious. Is there a way to make it worth with a Bit. all I want is a Boolean.

Troostite

Are you just displaying the table in datasheet mode and then editing it? Or are there forms that are running code? – Redpencil

I'm using the Form View. It's a checkbox in a form – Troostite

This box pops up whenever you check/uncheck the box or when you move to a new record? – Redpencil

Save the record or move to the next record. It does nothing when I check. – Troostite

It sounds like there is code that is running on the save event of the form that makes this happen. This kind of error can be very frustrating – Redpencil

Answer 1

Check if the bit field in SQL server is nullable. If it is, make it non-null and set the default value in SQL server to 0 (false). There is a discussion of this issue with nullable bit fields here: Nullable bool fields in MS Access linked tables

Clipfed

this was the correct answer for me. I had my bit columns nullable and with no default value. making this change fixed it after refreshing the linked tables. – Viquelia

Answer 2

Bit field when linked to Access , you can not have NULL values. So we need to make sure that bit field has a default value of either 0 or 1 so when new record is added to table, it will have default value and not NULL. This is because the ODBC driver do not know NULL values for bit fields.

Schacker

Answer 3

I had an error about the type at first...

This is your first clue. Go back to SQL Server and make certain that your bit is actually a bit. Assuming you have to change the data type in your database, you will need to either relink to the table or delete the table and link again.

If you cannot change the data type of the column in SQL Server, then you may need to use VBA. MS Access 2010 might have a wizard on configuring a checkbox to a text data type field. However, if the database ever stores a value other than the 2 (hopefully not more) that you configured, Bad Things will likely happen.

I went in my linked table in access and changed the display control of the column to checkbox instead of textbox...

This has no bearing on read/write. It simply determines the default control when creating forms with that column.

Foreword

thank you. Unfortunately, I can't reproduce the first error and I don't remember what it was. I can confirm that it was a bit when I had this error. In access it's converted to a Yes/No type – Troostite

hmm.. it could be any number of things then. perhaps you had the table open/locked at the same time you were trying to "test" things in your form? Could other people have been accessing the same information at that time? – Foreword

No, it's local on my computer. the error goes when the checkbox or textbox bound to a bit is removed. – Troostite

Can you try making a entirely new, simple form for just that table and seeing if the bit is writable then? When you tried to reproduce the error were you linking the table to MS Access (again)? – Foreword