How do you modify/create field properties in ACCESS using ADO

[WebCodger]

I am attempting to migrate from DAO to ADO. I can create a table and add fields using the Catalog set, but all fields have the "Required" property set to true. A listing of the properties for one of the new fields doesn't show "Required" as a property, so I suppose I need to create it. If I try to redefine its value to false, the error message says the property is not recognized.

I have searched numerous books for the code structure in ADODB and ADOX to create field properties, but I must be missing something. Can't find it. Any help?

 

[vbajock]

.Columns(fldname).Properties("Nullable&quot = False

 

[jopaumier]

vbajock,

I too am trying to do the same thing, but when I try your example I get error 3265: Item cannot be found in the collection corresponding to the requested name or ordinal. The table is created just fine. I have also tried placing the .Properties right after the .Append in the first 'With' block and got the same error. I also noticed that when I typed the equal sign (after ("Nullable&quot, VB did not offer the autocomplete choices of true or false.

Here is a reduced version of the code, with only one field:

Private catMDB As ADOX.Catalog

Public Sub CreateAnalysisTable(ByRef strMDBName As String)
' This routine opens an existing database and creates a new table in it using ADO

Set catMDB = New ADOX.Catalog
catMDB.ActiveConnection = "Provider=Microsoft.Jet.oledb.4.0;" & _
"data source=" & strMDBName

Dim TBL As ADOX.Table
Set TBL = New ADOX.Table

With TBL
.Name = "AnalysisTable"
.Columns.Append "MyField1", adVarWChar, 5
End With

catMDB.Tables.Append TBL

With TBL
.Columns("MyField1&quot.Properties("Nullable&quot = False
End With

Set TBL = Nothing
Set catMDB = Nothing

What am I missing?

Jim

 

[vbajock]

The equal sign is correct - I've got a bunch of programs executing that syntax all the time. Here is a complete code frag out of one these programs where I create a composite primary:

Case ssFld = "Itemkey"
SStbl.Columns.Append ssFld, adVarWChar, 15
SStbl.Columns(ssFld).Properties("Jet OLEDB:Allow Zero Length&quot = False
SStbl.Columns(ssFld).Properties("Nullable&quot = False

Case ssFld = "Location"
SStbl.Columns.Append ssFld, adVarWChar, 3
SStbl.Columns(ssFld).Properties("Jet OLEDB:Allow Zero Length&quot = False
SStbl.Columns(ssFld).Properties("Nullable&quot = False

Here is a frag where I create non-keyed fields:

If ssFld = CurmoStr Then
SStbl.Columns.Append "CurrentOpenBookUnits", adInteger
SStbl.Columns("CurrentOpenBook&quot.Properties("Nullable&quot = True
SStbl.Columns(ssFld).Properties("Default&quot = 0

SStbl.Columns.Append "CurrentOpenBook$", adCurrency
SStbl.Columns("CurrentOpenBook$&quot.Properties("Nullable&quot = True
SStbl.Columns(ssFld).Properties("Default&quot = 0
End If

This is just a quick guess, but one diffrence with my code is that I append the table last

catMDB.Tables.Append TBL
probably should come after, not before

With TBL
.Columns("MyField1&quot.Properties("Nullable&quot = False
End With

try changing that.

 

[cmmrfrds]

At the point you are after the Append, I believe you have lost the reference to the table.

Here is an example of renaming a column after the table was created.

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

'-rename a column later.
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"
'done.

 

[TimKThailand]

Try this out.

' set the required property to NO.
TableName.Columns(FlagCol).Attributes = adColNullable

 

[MarkDicken]

How to change Field Properties using ADOX

I am trying to use ADOX to create fields in a Table.

I cannot see how to modify the following properties :-

* Required (Yes/No)
* Unicode Compression (Yes/No)
* Allow Zero Length (Yes/No)

Thanks In Advance

Mark Dicken

http://www.MarkDicken.com

 

[cmmrfrds]

One way to find the names is to iterate through the properties collection for the table/column.


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

Set cg.ActiveConnection = CurrentProject.Connection
Set tb = cg.Tables("Categorys&quot
Debug.Print "table name = "; tb.Name
Dim pp As Property
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