Setting the Required Property to False

[George]

I'm using the following VB coding to create a new table in
Access 2000. My problem is that the Required property is
set to true. How can I set the required property to false?

Dim tblPointFlowCurr As New ADOX.Catalog
Dim tblPointFlow As New ADOX.Table
tblPointFlowCurr.ActiveConnection =
CurrentProject.Connection

With tblPointFlow
.Name = "MyTable"
.Columns.Append "Year", adInteger
.Columns.Append "Month", adInteger
.Columns.Append "Day", adInteger
end With

tblPointFlowCurr.Tables.Append tblPointFlow

Thanks for any help

 

[Douglas J. Steele]

Not all DBMS allow you to do this, so it's not a standard Column property in
ADOX. You'll have to create the field and change its property before you
append it to the table.

Dim tblPointFlowCurr As New ADOX.Catalog
Dim tblPointFlow As New ADOX.Table
Dim colPointFlow As New ADOX.Column
tblPointFlowCurr.ActiveConnection =
CurrentProject.Connection

With tblPointFlow
.Name = "MyTable"
With colPointFlow
.Name = "Year"
.Type = adInteger
.Property("Jet OLEDB:Allow Zero Length") = True
End With
.Columns.Append colPointFlow
With colPointFlow
.Name = "Month"
.Type = adInteger
.Property("Jet OLEDB:Allow Zero Length") = True
End With
.Columns.Append colPointFlow
With colPointFlow
.Name = "Day"
.Type = adInteger
.Property("Jet OLEDB:Allow Zero Length") = True
End With
.Columns.Append colPointFlow
end With

tblPointFlowCurr.Tables.Append tblPointFlow

To see what other Provider-specific properties you can set for Jet
databases, check out
http://msdn.microsoft.com/library/en-us/dndao/html/daotoadoupdate_topic14.asp

 

[Tim Ferguson]

My problem is that the Required property is
set to true.

ALTER TABLE MyTable
ALTER COLUMN Year INTEGER NULL

but bear in mind that Year, Month and Day are all reserved words in VBA and
these names are likely to cause you some grief further down the road.

HTH

Tim F