How to change Property

W

Wahab

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?
 
B

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
 
W

Wahab

Thank you very much, it works.

Brendan Reynolds said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top