How to add field properties to MDB with ADOX using VB???

[Hexman]

I'm creating a new Access table using ADOX. I can add columns and
indexes, but I'm baffled on how to change field properties. Can
someone give me a hand?

Want to change properties such as: Description (Column property?),
Decimal Places, Caption, Default value, Required indicator, Format,
etc....

TIA,

HexMan

Stripped Code:

Sub CrtTbl1()

Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As
ADOX. Table, objKey As ADOX.Key
Dim col As ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
objTable = New ADOX.Table
objKey = New ADOX.Key
col = New ADOX.Column

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\WOMSTR.mdb")

'Open the Catalog
Cat.ActiveConnection = Cn

'Create the table
objTable.Name = "WorkOrder"

'Create and Append a new fields to the "Work Order"
objTable.Columns.Append("WODate", DataTypeEnum.adDate)
objTable.Columns.Append("WOInnInd", DataTypeEnum.adInteger)
objTable.Columns.Append("WOTot", DataTypeEnum.adInteger)
objTable.Columns.Append("WORS1", DataTypeEnum.adVarWChar, 20)
'Create and Append a new primary key
objKey.Name = "PrimaryKey"
objKey.Type = KeyTypeEnum.adKeyPrimary
objKey.Columns.Append("WODate")
objKey.Columns.Append("WOTInnInd")
objTable.Keys.Append(objKey)

-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
want to change WODate.format = Short Date
change WODate.caption = "Work Order Date"
change WODate.required = True
want to change WOTot.format = General Number
change WOTot.decimalplaces = 2
change WOTot caption = "Total Qty."
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------

'Append the newly created table to the Tables Collection
Cat.Tables.Append(objTable)

' clean up objects
objKey = Nothing
objTable = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
End Sub

[Cor Ligthert [MVP]]

Hi Hexman,

You only need ADOX to create the database, the rest you can do with ADONET

Have a look at this sample,

http://www.vb-tips.com/default.aspx?...1-d6dda3c888c8

I hope this helps,

Cor
"Hexman" <He****@Binary.com> schreef in bericht
news:8j********************************@4ax.com...

I'm creating a new Access table using ADOX. I can add columns and
indexes, but I'm baffled on how to change field properties. Can
someone give me a hand?

Want to change properties such as: Description (Column property?),
Decimal Places, Caption, Default value, Required indicator, Format,
etc....

TIA,

HexMan

Stripped Code:

Sub CrtTbl1()

Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As
ADOX. Table, objKey As ADOX.Key
Dim col As ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
objTable = New ADOX.Table
objKey = New ADOX.Key
col = New ADOX.Column

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\WOMSTR.mdb")

'Open the Catalog
Cat.ActiveConnection = Cn

'Create the table
objTable.Name = "WorkOrder"

'Create and Append a new fields to the "Work Order"
objTable.Columns.Append("WODate", DataTypeEnum.adDate)
objTable.Columns.Append("WOInnInd", DataTypeEnum.adInteger)
objTable.Columns.Append("WOTot", DataTypeEnum.adInteger)
objTable.Columns.Append("WORS1", DataTypeEnum.adVarWChar, 20)
'Create and Append a new primary key
objKey.Name = "PrimaryKey"
objKey.Type = KeyTypeEnum.adKeyPrimary
objKey.Columns.Append("WODate")
objKey.Columns.Append("WOTInnInd")
objTable.Keys.Append(objKey)

-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
want to change WODate.format = Short Date
change WODate.caption = "Work Order Date"
change WODate.required = True
want to change WOTot.format = General Number
change WOTot.decimalplaces = 2
change WOTot caption = "Total Qty."
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------

'Append the newly created table to the Tables Collection
Cat.Tables.Append(objTable)

' clean up objects
objKey = Nothing
objTable = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
End Sub