Access support for checkbox against linked table

[jprotivnak]

I have an access file that is using a linked Oracle table.
When I open the linked table in Access, I want to have a column display as a checkbox like the YES/NO Access datatype does.
Does anyone know how I can accomplish this?

[June7]

Can't modify the table. Could join in query to an Access table that has Yes/No field. Would have to be a one-to-one relationship. Why do you need this? What are you really trying to accomplish?

[jprotivnak]

This is part of a large Access application that has pathways directly to the tables.
We are trying to phase out the Access application, the first step being moving the local tables to be linked Oracle tables.
We are trying to leave everything as close to the same as we can during the DB conversion.
End users hand enter data into the tables, and having a checkbox is a lot easier than a 1 or 0.

I don't fully understand how the join query would work.
Would I need to create a local access table that has a foreign key relationship to the Oracle table?

Additionally, If we were to move to a query we'd have to change the Access application everywhere it refers directly to the tables.

[June7]

Yes, pk/fk one-to-one relationship with local Access table.

Yes, change wherever need the Yes/No info.

If Oracle does not have yes/no field type, only other option I see is a text field in the Oracle table that is set up with a combobox on Access form that has only two values as options - Yes and No.

[jprotivnak]

I like the combobox idea. I will try that first.
That does raise an additional question for me:
In Oracle I have the field as a varchar2(1) - accepting 0 or 1 as that is what the Yes/No datatype exports as,
The application has multiple forms that also have checkboxes, will Access know to display the form checkboxes as checked or unchecked without the YES/NO datatype?
Probably something I'll have to experiment with to see if I can find a datatype and value that the Access forms will recognize.

[June7]

With a Yes/No field clicking a checkbox in Access will set the checkbox to -1 or 0. If the checkbox is checked, its value is -1, if it is not checked, its value is 0. Then with a text field a checkbox can have triple state, meaning a null can be committed to the field.

I just experimented with a text field by manually entering 1, 0, -1 into records. The 1 and -1 both set the checkbox as checked. However, clicking on the checkbox will modify the data to -1 or 0 (or null if TripleState is allowed).

I rarely use Yes/No fields for user data entry and have never built a web database.

[alcapps]

To use the least amount of space you should use a CHAR field constrained to 'Y' or 'N'. Oracle doesn't support BOOLEAN, BIT, or TINYINT data types, so CHAR's one byte is as small as you can get. I googled bit fields in Oracle. Looks like you would have to map Y to -1 and N to 0. for oracle to store the same info. hope this helps

[jprotivnak]

The combobox solution does work. However I found a way to display a checkbox:

You can modify the linked table's TableDef. Add a DisplayControl property to your field with property value as 106 for a check box.

You can execute the code through the Immediate window.
(If you are not not familiar with the Immediate window. Open it with Ctrl+g. Enter a line of code press Enter, and so on for each line.)

Since Oracle has no SQL boolean type the field in Oracle should be something like a number(1,0) or varchar2(2)
*Note* Access saves "No" or unchecked as 0 and "Yes" or checked as -1.

set db = currentdb
set fld = db.TableDefs("dbo_foo").Fields("a_number")
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, 106)

"dbo_foo" being the linked table and "a_number" the field you want to checkboxify.

If for some reason your target field already has a DisplayControl value set already, just change the last line to only set the value:

fld.Properties("DisplayControl") = 106

' verify property created with correct value ...

?fld.Properties("DisplayControl")

After setting the property, my a_number field is displayed as a check box when "dbo_foo" is opened in Datasheet View.

Additionally, you can set other properties the same way instead of "DisplayControl" you could use:
"RowSourceType"
"RowSource"
"BoundColumn"
and so on...