ADOX and Access Required and Format column properties

I have extensive experience with ADOX (using ADO Ext. 2.8 for DDL and Security).

In Microsoft Access, when you define a column in a table, you have access to various properties for that column. Most of those properties (name, data type, Unicode compression, etc.) can be easily defined using ADOX. However, I have encountered two issues.

Issue 1:
For most data types, it is possible to use (all examples assume a With clause, which I haven't shown because my issues have nothing to do with syntax):

      .Columns("AccountTo").Properties("Nullable") = True
      
In this case, the "AccountTo" column will be created with a "Required = False" in Access. However, if the data type of the column is adBoolean, then this "nullable" property is not honored. Go ahead and try it.

On some Google boards, some people have suggested that this is because a Boolean field should never be nullable. This is utter nonsense and those people clearly do not understand the meaning of null. Null means no data was provided; if data IS provided, however, it must be 0 or 1 for adBinary columns. And to suggest that adBinary columns can't be null is also wrong because you can easily create a binary column in Access that has its Required property set to False.

So, without Access on the users machine (with Access you can use automation, of course), how can a VB6 program modify the Required attribute for an Access column?

Issue 2:
Access also shows a "Format" attribute. For adBinary, you can set this to "Yes/No", and whenever you open the table in Access, you will see "Yes" or "No" instead of 1 or 0. I realize setting this from VB would have no effect on how VB interacts with the Access database, but I want to create tables and columns using VB that MAY be opened by users who own Access--and I want them to see properly-formatted columns.

In general, I want to be able to create from VB tables and columns EXACTLY as they are when I create them by hand in Access. Why Microsoft did not make this easier is infuriating.


Any experts out there know how to solve these issues?

' Extended Properties for column AccountTo
      .Columns("AccountTo").Properties("Autoincrement") = False
      .Columns("AccountTo").Properties("Default") = ""
      .Columns("AccountTo").Properties("Description") = "Account To"
      .Columns("AccountTo").Properties("Nullable") = False
      .Columns("AccountTo").Properties("Fixed Length") = False
      .Columns("AccountTo").Properties("Jet OLEDB:Column Validation Text") = "Value must be between 1 and 100"
      .Columns("AccountTo").Properties("Jet OLEDB:Column Validation Rule") = ">=1 And <=100"
      .Columns("AccountTo").Properties("Jet OLEDB:IISAM Not Last Column") = False
      .Columns("AccountTo").Properties("Jet OLEDB:AutoGenerate") = False
      .Columns("AccountTo").Properties("Jet OLEDB:One BLOB per Page") = False
      .Columns("AccountTo").Properties("Jet OLEDB:Compressed UNICODE Strings") = True
      .Columns("AccountTo").Properties("Jet OLEDB:Allow Zero Length") = False
      .Columns("AccountTo").Properties("Jet OLEDB:Hyperlink") = False

Select allOpen in new window