Search code examples
oracle-databasems-accesscheckboxbooleanlinked-tables

Microsoft Access support for checkbox column against linked Oracle table

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? Any help would be greatly appreciated.

Solution

  • If you're opposed to using a form with a check box control for the linked table, you can modify the linked table's TableDef. Add a DisplayControl property to your numeric field with property value as 106 for a check box.

    I ran the following example in the Immediate window. dbo_foo is linked to a SQL Server table since I don't have Oracle available. The field a_number is interpreted by Access as Long Integer (not Yes/No data type).

    set db = currentdb
set fld = db.TableDefs("dbo_foo").Fields("a_number")
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, 106)
' verify property created with correct value ...
? fld.Properties("DisplayControl")
 106

    If for some reason your target field already has a different DisplayControl value set, just change that value (instead of .Append with .CreateProperty).

    fld.Properties("DisplayControl") = 106

    After setting the property, my a_number field is displayed as a check box when dbo_foo is opened in Datasheet View. See whether that approach gives you what you want with your Oracle table.