Setting the Required Property to False

  • Thread starter Thread starter George
  • Start date Start date
G

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
 
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
 
My problem is that the Required property is
set to true.
Click to expand...

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
 
Back
Top