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.
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.
Posts
OK I definitely wouldn't have figured this one out on my own, THANK YOU SO MUCH!!!
ReplyDeleteLegend! Cheers
ReplyDeleteYou are a goddess for posting this - thanks. It would have taken a month of sundays to find.
ReplyDeleteThank you - this post saved a lot of head scratching
ReplyDeleteThank you !!!!
ReplyDeletemy goodness, thank you
ReplyDeleteThis post is still helping people... :)
ReplyDeleteThanks!
Did you ever know that you're my hero?!?!
ReplyDeleteSeriously. I only wish I had found this post earlier....found several other unhelpful posts first.
That's really sweet, thanks!
DeleteThe best part is that you included the right text in the title and article that we could find it in a search! Thanks
ReplyDeleteThank you for such a finding !!!
ReplyDeleteReally saved me a lot of hassle and grief. Thanks Stacy. You are a legend.
ReplyDeleteAwesome
ReplyDeleteThank 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.
ReplyDeleteThanks! This saved me time! :-)
ReplyDeleteThis is the only post I've found that tells me where and how to display a bit field as a checkbox!!! Thank You!
ReplyDeleteThis even works for Access ADP projects! Thanks a million for posting this tip! - Adam
ReplyDeleteStill helping people!! Saved me a ton of time! Thanks for posting!
ReplyDeleteYOU ARE THE BEST. THANK YOU VERY MUCH!
ReplyDeleteThanks, saved me a ton of time.
ReplyDeleteThanks Stacey, I am now in love with you, but don't tell the wife. xx
ReplyDeleteThis post is still valid! Thank you!!
ReplyDeleteBut now I get the message that a other user has changed my record and I can't store any changes.
ReplyDeleteBut now I get the message that someone else has changed my record and I can't store my changes.
ReplyDelete