I've been having all sorts of fun adventures working with a Microsoft Access database linked to SQL Server 2008 tables. And by "fun adventures", I really mean to say "irritating issues". Thankfully, I don't let them stop me... I just dig around until I can figure out how to get my way!

A bit field in one of my linked tables was showing up as a text box in forms. It was also showing up as text while viewing the table in Datasheet view. My first thought was, "Well this is completely useless. I don't want to see -1 and 0. I want a nice little checkbox!"

Generally, when you want to change a bit field (or in Access terms, a 'Yes/No' field) to display a checkbox control, it's easy enough to just go into the table design and make that change there. Yes, this is still the case for linked tables... but be forewarned that the UI in Access, for whatever reason, lies to you and attempts to trick you into thinking that you can't. Don't be fooled!

Right-click on the linked table that contains the bit field, and select 'Design View'. You're going to see the following message: "Table 'dbo_YourTable' is a linked table whose design can't be modified. If you want to add or remove fields or change their properties or data types, you must do so in the source database. Do you want to open it anyway?"

While this is mostly true, it fails to tell you that there are still some properties that you can alter that only really affect your Access file and that yes, you are free to make and save those changes here. Do yourself a favour and ignore this warning and proceed by clicking on the 'Yes' button.

Now, select your bit (Yes/No) field. At the bottom of the window in the 'Field Properties' area, click on the 'Lookup' tab. You're going to see a property called 'Display Control'. Change this to 'Check Box'. Now close the table, and click 'Yes' to save your changes.

It really is that simple. Unfortunately for me, this initially caused me grief because the warning was giving me the impression that I shouldn't even bother to try changing the table properties. Sheesh! Here's hoping that this post can help someone else (or several other people) save time.

16 comments:

  1. OK I definitely wouldn't have figured this one out on my own, THANK YOU SO MUCH!!!

    ReplyDelete
  2. You are a goddess for posting this - thanks. It would have taken a month of sundays to find.

    ReplyDelete
  3. Thank you - this post saved a lot of head scratching

    ReplyDelete
  4. This post is still helping people... :)
    Thanks!

    ReplyDelete
  5. Did you ever know that you're my hero?!?!

    Seriously. I only wish I had found this post earlier....found several other unhelpful posts first.

    ReplyDelete
  6. The best part is that you included the right text in the title and article that we could find it in a search! Thanks

    ReplyDelete
  7. Thank you for such a finding !!!

    ReplyDelete
  8. Really saved me a lot of hassle and grief. Thanks Stacy. You are a legend.

    ReplyDelete
  9. Thank you! I had been futzing around on SQL Mgmnt Studio for an hour trying to figure out why this was the only one that wasn't a checkbox when all other properties were the same.

    ReplyDelete
  10. This is the only post I've found that tells me where and how to display a bit field as a checkbox!!! Thank You!

    ReplyDelete