Setting Db field properties with vb code at runtime

[VisualLite]

I need to set some field properties for the table i created, such as allowing zero length and alowing duplicates. Have searched everywhere.

 

[evalesthy]

Some snippets from working code

Set fld7 = tdf.CreateField("RID_Text", dbText, 255)
fld7.AllowZeroLength = True


fldQuantity.ValidationRule ' ">=4"
fldQuantity.ValidationText = "Qty must be 4 or more items."


and from Access help:

This example creates a new table called MyTable with two text fields, a Date/Time field, and a unique index made up of all three fields.

Sub CreateTableX2()


Dim dbs As Database


' Modify this line to include the path to Northwind

' on your computer.

Set dbs = OpenDatabase("Northwind.mdb&quot


' Create a table with three fields and a unique

' index made up of all three fields.

dbs.Execute "CREATE TABLE MyTable " _

& "(FirstName CHAR, LastName CHAR, " _

& "DateOfBirth DATETIME, " _

& "CONSTRAINT MyTableConstraint UNIQUE " _

& "(FirstName, LastName, DateOfBirth));"


dbs.Close


End Sub

Hope this gets you started.

 

[VisualLite]

I'm using ADO. Here is a line from my code..

.CommandText = "CREATE TABLE tblInventoryList (InventoryListID Counter, InventoryItem Text(254), Category Text(50), QuantityLeft Single, Price Currency, Constraint constr PRIMARY KEY (InventoryListID))"
.Execute

For example say I wanted the field QuantityLeft to allow zerolength and allow duplicates. How can I achieve this in this situation?

 

[cmmrfrds]

I don't believe zero length is a property of a number field only on text.

Here is an example using the ADOX library. You can probably extract what you need out of this function. Iterate through the properties collection to see the name of the property.


Function catalogInfo()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As ADOX.Column
Set cg.ActiveConnection = CurrentProject.Connection

''tb.Name = "Test"
''tb.Columns.Append "col1", adInteger
''tb.Columns.Append "col2", adVarWChar, 50
''Debug.Print "table = "; cg.Tables("Test&quot.Name
''cg.Tables.Append tb
''Exit Function
'-rename a column
Set tb = cg("test&quot
Set cl = tb("col2aa&quot
Debug.Print cl
cl.Properties("Jet OLEDB:Allow Zero Length&quot = True
''cl.Name = "col2aa"
Exit Function

Set cg.ActiveConnection = CurrentProject.Connection
Set tb = cg.Tables("dbo_categories&quot
Debug.Print "table name = "; tb.Name
'''Set cg.Procedures("myproc&quot = "select * from customer"
Dim pp As Property
''Debug.Print "column = "; tb.Columns("Description&quot.Properties("default&quot.Value
''Exit Function
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
Debug.Print "type = "; cl.Type
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value

Next
Next

End Function

 

[dilettante]

Did you maybe want ADOX's objColumn.Properties("Nullable&quot instead?

 

[VisualLite]

Thanks for the information everyone. I believe this will work. I will get busy on it and let you all know how it is going.
Thanks again.
VisualLite

 

[VisualLite]

It works, but one problem. All the tables that need to be created and added to are on our clients machines. The program that uses the database does not have reference to ADOX. We would have to create all new installables to add those files to our clients' PC's, but thanks anyway its always good to learn something new.
VisaualLite