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.

24 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. my goodness, thank you

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

    ReplyDelete
  6. 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
  7. 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
  8. Thank you for such a finding !!!

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

    ReplyDelete
  10. 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
  11. Thanks! This saved me time! :-)

    ReplyDelete
  12. 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
  13. This even works for Access ADP projects! Thanks a million for posting this tip! - Adam

    ReplyDelete
  14. Still helping people!! Saved me a ton of time! Thanks for posting!

    ReplyDelete
  15. YOU ARE THE BEST. THANK YOU VERY MUCH!

    ReplyDelete
  16. Thanks, saved me a ton of time.

    ReplyDelete
  17. Thanks Stacey, I am now in love with you, but don't tell the wife. xx

    ReplyDelete
  18. This post is still valid! Thank you!!

    ReplyDelete
  19. But now I get the message that a other user has changed my record and I can't store any changes.

    ReplyDelete
  20. But now I get the message that someone else has changed my record and I can't store my changes.

    ReplyDelete