How to change Property

[Guest]

Hi
Please help me to change the property .
I have created table by code which is as follow:

Set tbl = New ADOX.Table
With tbl
tbl.Name = strTable
With .Columns
.Append "TrnDate", adDate
.Append "TrnType", adInteger
.Append "RefNo", adInteger
.Append "CustomerID", adInteger
.Append "Details", adWChar, 50
.Append "Balance", adCurrency
With idx
.Name = "Primary"
.IndexNulls = adIndexNullsDisallow
.Columns.Append "RefNo"
.Columns.Append "CustomerID"
.PrimaryKey = True
.IndexNulls = adIndexNullsDisallow
.PrimaryKey = True
End With
tbl.Indexes.Append idx
End With
cat.Tables.Append tbl
Set cat = Nothing
End With
End Sub

when it creates table by default all property fields are coming requred Yes .
How I will change the property of each field?

 

[Brendan Reynolds]

The code below worked for me, but you may have problems with it if you are
using an earlier version of ADOX. See the KB article at the following URL
for more information ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;272001

Public Sub CreateTable()

Dim tbl As ADOX.Table
Const strTable As String = "MyNewTestTable"
Dim idx As ADOX.Index
Dim cat As ADOX.Catalog

Dim col As ADOX.Column

On Error Resume Next
CurrentProject.Connection.Execute "DROP TABLE " & strTable
On Error GoTo 0

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
With tbl
tbl.Name = strTable
With .Columns
.Append "TrnDate", adDate
.Append "TrnType", adInteger
.Append "RefNo", adInteger
.Append "CustomerID", adInteger
.Append "Details", adWChar, 50
.Append "Balance", adCurrency
End With

.Columns("Balance").Attributes = adColNullable

Set idx = New ADOX.Index

With idx
.Name = "Primary"
.IndexNulls = adIndexNullsDisallow
.Columns.Append "RefNo"
.Columns.Append "CustomerID"
.PrimaryKey = True
.IndexNulls = adIndexNullsDisallow
.PrimaryKey = True
End With
tbl.Indexes.Append idx
End With
cat.Tables.Append tbl
Set cat = Nothing

End Sub

 

[Guest]

Thank you very much, it works.

The code below worked for me, but you may have problems with it if you are
using an earlier version of ADOX. See the KB article at the following URL
for more information ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;272001

Public Sub CreateTable()

Dim tbl As ADOX.Table
Const strTable As String = "MyNewTestTable"
Dim idx As ADOX.Index
Dim cat As ADOX.Catalog

Dim col As ADOX.Column

On Error Resume Next
CurrentProject.Connection.Execute "DROP TABLE " & strTable
On Error GoTo 0

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
With tbl
tbl.Name = strTable
With .Columns
.Append "TrnDate", adDate
.Append "TrnType", adInteger
.Append "RefNo", adInteger
.Append "CustomerID", adInteger
.Append "Details", adWChar, 50
.Append "Balance", adCurrency
End With

.Columns("Balance").Attributes = adColNullable

Set idx = New ADOX.Index

With idx
.Name = "Primary"
.IndexNulls = adIndexNullsDisallow
.Columns.Append "RefNo"
.Columns.Append "CustomerID"
.PrimaryKey = True
.IndexNulls = adIndexNullsDisallow
.PrimaryKey = True
End With
tbl.Indexes.Append idx
End With
cat.Tables.Append tbl
Set cat = Nothing

End Sub