Using ADO to set field as index (key)? 1

[Dan01]

Is it possible to use ADO in to set a field as a key? Thanks, Dan.

 

[cmmrfrds]

I believe so, but you will need to iterate through the properties collection of the table to find the correct property name to set the primary key. Here is a function with some code commented out that you could modify. You will need an adox library reference.


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
''Set zero length property on col2aa.
''cl.Properties("Jet OLEDB:Allow Zero Length&quot = True
''cl.Name = "col2aa"

Set tb = cg.Tables("Categorys&quot
Debug.Print "table name = "; tb.Name

'- Iterate the property collection.
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

 

[Dan01]

Thanks cmmrfrds. This code will be quite helpful. Dan.