Programatically Setting Boolean Format To Yes/No

[Mark H.]

Hello:

I am able to add 2 boolean columns to an existing Access
2000 table but how do I set the format of the boolean
type to Yes/No? When I do it progrmatically the columns
get added but the format in the tab on the bottom is
blank whereas when I enter via Access directly it's set
to Yes/No. How do I set this programatically? Here is
the code I am using now. Thank you in advance for your
response!

Private Sub UpdateSecurityTblViewEditSharedFileErrors
(ByRef objConn As ADODB.Connection)

On Error GoTo DbError

Dim strDB As String
Dim catCurr As ADOX.Catalog
Dim objRs As ADODB.Recordset

Set catCurr = New ADOX.Catalog

'catCurr.ActiveConnection = objConn
strDB = "Data Source=" & App.Path
& "\RxScriptTracker.mdb;"
catCurr.ActiveConnection
= "Provider=Microsoft.Jet.OLEDB.4.0;" & strDB

With catCurr.Tables("Security")

' Add the new fields for viewing and editing Shared
File errors

.Columns.Append "ViewSharedFileErrors", adBoolean, 1
.Columns.Append "EditSharedFileErrors", adBoolean, 1

End With

Set catCurr = Nothing

' Now update the Administrator Security class so that
these new options are enabled by default

Set objRs = New ADODB.Recordset
objRs.Open "Security", objConn, adOpenForwardOnly,
adLockOptimistic
If objRs.EOF = False Then
With objRs
Do Until .EOF = True
If !ClassName = "Administrator" Then
!ViewSharedFileErrors.Value = True
!EditSharedFileErrors.Value = True
End If
.MoveNext
Loop
End With
End If
objRs.Close

Exit Sub

DbError:

Dim strErrMsg As String

strErrMsg = "Error creating Shared File Errors Table" &
vbCrLf & vbCrLf & Err.Description
MsgBox strErrMsg, vbOKOnly, "Shared File Error Table"

End Sub

 

[Allen Browne]

AFAIK, the Format and DisplayControl propertiescan only be set using the DAO
library. Kinda limits the usefulness of ADOX, doesn't it.

The Default property of a column can be set with ADOX, though it may not
work correctly with version prior to MDAC 2.6.

 

[Tim Ferguson]

I am able to add 2 boolean columns to an existing Access
2000 table but how do I set the format of the boolean
type to Yes/No? When I do it progrmatically the columns
get added but the format in the tab on the bottom is
blank whereas when I enter via Access directly it's set
to Yes/No. How do I set this programatically?

The answer to all this UI-in-the-table stuff is nearly always the same...
You Don't.

Remember that the one and only job of the Format property is to set the
default for controls that are bound to that column. So either your users
can use the UI or not:-

If your users are using the UI to create forms, then they can access the
table design to change the Format for fields in the tables, and that in the
end is going to cause far less heartache than messing about with the
Properties collections etc etc.

If your users do not have access to the UI to alter the fields' Format
properties, then they can't create forms or reports, so setting the
property won't matter to them anyway.

The reason that ADO cannot see the Format property is that ADO(X) is about
managing the data model, and the Format property has absolutely nothing to
do with the data in the table -- it's just a convenience for the developer.
On that point, it really only takes one mouse click to set the Yes/No
format in the Forms design property window anyway, so the amount of effort
is saves is pretty minimal.

Hope that helps


Tim F