How to get field description from an Access table using ADO 6

[Weebairnes]

I am unable to figure out how to get the description reference to return more than a Null value. In frustration/ confusion/ investigation I wrote the following 'for loop' to see what properties exist and what values they would spit out.

This is what I tried.
____________________________________________________
Function mytry()
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim intI As Integer

On Error Resume Next
Set conn = CurrentProject.Connection
Set rs = conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "tblCRF_BaselineIv&quot)
While Not rs.EOF
For intI = 1 To 100
Debug.Print intI & vbTab & rs(intI).Name & ": " & rs(intI)
Next intI
Debug.Print
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Function
____________________________________________________
Here are the results for one of the fields in the table (notice that description is null, but my table design has a description comment.) I can get the field description with DAO, but I want to use ADO. Can you help me?


1 TABLE_SCHEMA:
2 TABLE_NAME: tblCRF_BaselineIv
3 COLUMN_NAME: BIv_ID
4 COLUMN_GUID:
5 COLUMN_PROPID:
6 ORDINAL_POSITION: 1
7 COLUMN_HASDEFAULT: False
8 COLUMN_DEFAULT:
9 COLUMN_FLAGS: 90
10 IS_NULLABLE: False
11 DATA_TYPE: 3
12 TYPE_GUID:
13 CHARACTER_MAXIMUM_LENGTH:
14 CHARACTER_OCTET_LENGTH:
15 NUMERIC_PRECISION: 10
16 NUMERIC_SCALE:
17 DATETIME_PRECISION:
18 CHARACTER_SET_CATALOG:
19 CHARACTER_SET_SCHEMA:
20 CHARACTER_SET_NAME:
21 COLLATION_CATALOG:
22 COLLATION_SCHEMA:
23 COLLATION_NAME:
24 DOMAIN_CATALOG:
25 DOMAIN_SCHEMA:
26 DOMAIN_NAME:
27 DESCRIPTION:

 

[MarkSweetland]

You can use ADOX to query the column properties. The function ColumnProp below will return a column property value. Just change the ActiveConnection to point to your mdb file and change the table and column names. You can use the TableProperties routine to loop through all the columns in a table and retrieve properties and their respective values:

Option Explicit

[green]'
' Project References Microsoft ADO Ext. 2.x for DDL & Security
'[/green]
Private Sub Form_Load()
Debug.Print ColumnProp("TABLE1", "SUBMIT", "Description&quot
Debug.Print ColumnProp("TABLE1", "SUBMIT", "Jet OLEDB:Allow Zero Length&quot
TableProperties ("TABLE1&quot
Unload Me
End Sub

Private Function ColumnProp(TableName As String, _
ColumnName As String, _
ColumnProperty As String) As Variant
Dim oCat As New ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oProp As ADOX.Property
oCat.ActiveConnection = "provider=Microsoft.jet.oledb.4.0;" & _
"data source = c:\db2.mdb"
Set oTable = oCat.Tables(TableName)
If Not oTable Is Nothing Then
Set oColumn = oTable.Columns(ColumnName)
If Not oColumn Is Nothing Then
Set oProp = oColumn.Properties(ColumnProperty)
If Not oProp Is Nothing Then
ColumnProp = oProp.Value
End If
End If
End If
Set oProp = Nothing
Set oColumn = Nothing
Set oTable = Nothing
Set oCat = Nothing
End Function

Private Sub TableProperties(TableName As String)
[green]' Project reference Microsoft ADO Ext. 2.x for DDL & Security[/green]
Dim oCat As New ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oProp As ADOX.Property
oCat.ActiveConnection = "provider=Microsoft.jet.oledb.4.0;" & _
"data source = c:\db2.mdb"
For Each oTable In oCat.Tables
If oTable.Type = "TABLE" Then
If UCase(oTable.Name) = UCase(TableName) Then
For Each oColumn In oTable.Columns
Debug.Print "=======Column: '" & oColumn.Name & "' =========="
For Each oProp In oColumn.Properties
Debug.Print "Property: [" & oProp.Name & "], Value: [" & oProp.Value & "] "
Next
Next
End If
End If
Next
Set oProp = Nothing
Set oColumn = Nothing
Set oTable = Nothing
Set oCat = Nothing
End Sub


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Weebairnes]

Hi Mark,

Thanks for the explanation and example. I especially appreciate you including the correct DLL/olb for me to connect to.

I tried the first function(s) and no success

At the line
Set oColumn = oTable.Columns(ColumnName)
the code stops and the following error occurs.

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

WHAT IS THE PROBLEM? This has been an adventure in frustration - lots of hours playing with collections and syntax.

In the last function, Function TableProperties(), the following line does not give an error, but it never has any value, so the code does not execute the debug.print statement.
"For Each oColumn In oTable.Columns"

any ideas? - thanks so much for you help so far.

 

[MarkSweetland]

You probably just need to make sure you change a couple of things to match your environment.

Private Sub Form_Load()
[green]' Call the Sub with the name of the table you want to get the properties of[/green]
TableProperties "tblCRF_BaselineIv"
End Sub

Private Sub TableProperties(TableName As String)
[green]' Project reference Microsoft ADO Ext. 2.x for DDL & Security[/green]
Dim oCat As New ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oProp As ADOX.Property
oCat.ActiveConnection = "provider=Microsoft.jet.oledb.4.0;" & _
&quot;data source = [red]<insert path and file name to your mdb here>[/red]&quot;
For Each oTable In oCat.Tables [green]'loop through all tables[/green]
If oTable.Type = &quot;TABLE&quot; Then [green]'skip views etc.[/green]
If UCase(oTable.Name) = UCase(TableName) Then [green]' if the table name = passed argument table name[/green]
For Each oColumn In oTable.Columns [green]' Loop through columns in the table[/green]
Debug.Print &quot;=======Column: '&quot; & oColumn.Name & &quot;' ==========&quot;
For Each oProp In oColumn.Properties [green]' loop through each property and print to immediate window[/green]
Debug.Print &quot;Property: &quot; & oProp.Name & &quot;, Value: &quot; & oProp.Value & &quot; &quot;
Next
Next
End If
End If
Next
Set oProp = Nothing
Set oColumn = Nothing
Set oTable = Nothing
Set oCat = Nothing
End Sub


Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[Weebairnes]

Sorry, no go. Are there problems with Access? Is ADOX meant for non-access databases? Perhaps a problem with Access XP?

I place a break point in the code and watch it execute line by line. Like I said, the following does not give an error, but 'oTable.Columns' never has any value, so the code does not execute the debug.print statement.
&quot;For Each oColumn In oTable.Columns&quot;
It seems like there are no columns in the collection oTable.Columns.

When debugging, I float over it with the mouse to see the values. I can use debug.print and print the table name to confirm that I have the right table.
The table name is correct, but the column = nothing.

Any other ideas?

 

[MarkSweetland]

Hmmm.... The code works on my environment, XP Pro, Access 2003, ADO Ext. 2.8 for DDL & Security. Not sure why it would not work on Access XP. One more thing to try. Put a refresh statement just before the columns loop (in red below). Maybe it needs a kick start...


Private Sub TableProperties(TableName As String)
[green]' Project reference Microsoft ADO Ext. 2.x for DDL & Security[/green]
Dim oCat As New ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oProp As ADOX.Property
oCat.ActiveConnection = &quot;provider=Microsoft.jet.oledb.4.0;&quot; & _
&quot;data source = <insert path and file name to your mdb here>&quot;
For Each oTable In oCat.Tables 'loop through all tables
If oTable.Type = &quot;TABLE&quot; Then 'skip views etc.
If UCase(oTable.Name) = UCase(TableName) Then ' if the table name = passed argument table name
[red]oTable.Columns.Refresh[/red]
For Each oColumn In oTable.Columns ' Loop through columns in the table
Debug.Print &quot;=======Column: '&quot; & oColumn.Name & &quot;' ==========&quot;
For Each oProp In oColumn.Properties ' loop through each property and print to immediate window
Debug.Print &quot;Property: &quot; & oProp.Name & &quot;, Value: &quot; & oProp.Value & &quot; &quot;
Next
Next
End If
End If
Next
Set oProp = Nothing
Set oColumn = Nothing
Set oTable = Nothing
Set oCat = Nothing
End Sub


Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[Weebairnes]

Thanks again for your help.

I tried adding the suggested line of code. No go. Although it was a refreshing try. chuckle chuckle.

Here is an interesting twist. I tried pointing to an old Access 97 table and - SUCCESS! As you knew, and I suspected, all your code is fine, but something is goofy with my set up. If you have time, would you consider the following?

Now after considerable fiddling.
You examples work fine with Access 97, 2000, XP when I point to independent databases (all these db reside on my local harddrive). But for some reason, it doesn't work in the two scenarios below.
a) Code is running in myDB1.
b) Code is pointing to a table in myDB1.
Or
a) Code is running in myDB1.
b) Code is pointing to a table in myDB2.
c) myDB1 has tables linked to myDB2
---again, it is as if the collection of columns is empty.---

(Of course there may be another factor that I am overlooking.)


Thanks much for the help

 

[Weebairnes]

Say... could it possible that all my woes are related to a secure database? I am using an mdw file of my own and have set permissions on the tables.

This explains why I can see the tables, but can't see the content. After more fiddling (trials and errors), I figured out the proper connection string. And walla.

Thanks Mark.

(look for your nomination you for the people's choice award - again, Mucho Gracias, Merci Beaucoup, Danke, Grazie, and heap big thanks for your help)

 

[MattHen001]

I'm using similar code, setting my connection to an .ADP file. I specifically need to write the description property to a table, but get the following error:

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

Like Weebairnes, I also tried running the code on an old MDB file, and it worked!

Could my problem also be with my connection string? We're using SQL 2000, Win NT Authentication, and an Access 2002 Project.

Here's what my connection string looks like:

Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=oacdata;Integrated Security=SSPI;Initial Catalog=kolko_skip;Data Provider=SQLOLEDB.1

Thanks,
Matt

 

[MarkSweetland]

An .adp Access file connects to a Microsoft SQL Server database through the OLE DB component. The .adp contains only code-based or HTML-based database objects: forms, reports, macros, modules, and shortcuts to data access pages. It does not contain any data or data-definition-based objects: tables, views, database diagrams, or stored procedures. These database objects are stored in the SQL Server database. If you need to modify the table structure you'll have to connect directly to the SQL Server. This limitation would still hold true if the "old" .mdb file contained links to tables in other databases and you wanted to change the structure of the linked table.





Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[NJLDoc]

Great help even though it was not my question. I have a question about the table properties.

In your example, changing the:
For Each oProp In oColumn.Properties
to
For Each oProp In oTable.Properties

the Debug.print provides a list of properties but is missing the table Description. How does one read and or set the Description property?

 

[MarkSweetland]

You can use the ADO connection to open the table schema and read the description property long with the name, date modified, etc.

Private Sub Form_Load()
    ListTableSchema "[your filename here]"
    End
End Sub

Sub ListTableSchema(strMDBFile)

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    ' Open the connection.
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open strMDBFile
    End With

    ' Open the tables schema
    Set rs = cn.OpenSchema(adSchemaTables)

    ' Loop through the recordset and print
    With rs
        While Not .EOF
            If (rs.Fields("TABLE_TYPE") = "TABLE") Then
                For i = 0 To rs.Fields.Count - 1
                    Debug.Print .Fields(i).Name & vbTab & .Fields(i).Value
                Next
            End If
            .MoveNext
        Wend
    End With
    cn.Close
    Set cn = Nothing
    rs.Close
    Set rs = Nothing
End Sub

Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[NJLDoc]

Wow! Thanks so very much. I have wondered for ages how to retrieve this information. Is there anyway to set the value programmatically?

 

[MarkSweetland]

I believe the Table description property is read-only through the ADODB.Connection object. If you are going to modify the description of the table you can get there through DAO:

Private Sub Form_Load()
    ' Set project reference to Microsoft DAO 3.6 Library
    setTableDescription "[FileName]", "[Table Name]", "This is a test description"
    Debug.Print getTableDescription("[FileName]", "[Table Name]")
    End
End Sub

Private Function getTableDescription(strSourceFile As String, strTableName As String) As String
    Dim ws As New DAO.DBEngine
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim prop As DAO.Property
    
    Set db = ws.OpenDatabase(strSourceFile)
    Set tbl = db.TableDefs(strTableName)
    Set prop = tbl.Properties("Description")
    getTableDescription = prop.Value
    
    Set ws = Nothing

End Function

Private Sub setTableDescription(strSourceFile As String, strTableName As String, strDescription As String)
    Dim ws As New DAO.DBEngine
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim prop As DAO.Property
    
    Set db = ws.OpenDatabase(strSourceFile)
    Set tbl = db.TableDefs(strTableName)
    Set prop = tbl.Properties("Description")
    prop.Value = strDescription
    
    Set ws = Nothing
End Sub

Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[NJLDoc]

Awesome. Thank you so very much. This works wonderfully.

 

[newc0548]

Thank you very much as well - I was trying to find out how to get descriptions from Access as well
peter

 

[topone]

Hello,
I was reading all the help that you gave to the other members. Thanks very much, but I do have a question of my own.
The database that I use has a password, and I am trying to change one of the properties of a field called "Read_Code".
I get a RTE that says "object required". Do you have any suggestions?
Most of the variables are declared as Dim xx without any other property as integer, string, etc,
The refernce to the Microsoft Ado library is selected.
Thanks
Paolo

Set cat = New ADOX.Catalog
'
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DSM.dat;" & "Jet OLEDBatabase Password= xxx;" & "Jet OLEDB:Engine Type=5;"

Set tbl = cat.Tables("Formulary")

Set Col = Formulary.Columns("Read_Code")
Col.Properties("Jet OLEDB:Allow Zero Length").value = True

 

[MarkSweetland]

[green]'Set Col = Formulary.Columns("Read_Code")[/green]
Set Col = [red]tbl[/red].Columns("Read_Code")




Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[topone]

Thanks Mark, it work fine.

 

[topone]

Hello,
I have another question. I am trying to add a table to an existing database. The code is quite straight forward and there are no problems, but I have been asked to call one of the table's fields Text. When i put this in the string to execute generates an error.
anyway of bypassing this problem.
Single or double quotes will make a difference ?
I also insert the code for reference to anyone who may need it even if a doubt it.
thanks
paolo

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Jet OLEDBatabase Password= xxx;" & "Persist Security Info=False"
conn.Open

On Error Resume Next
conn.Execute "DROP TABLE DefExempt"
On Error GoTo 0
conn.Execute _
"CREATE TABLE DefExempt(" & _
"ID INTEGER NOT NULL," & _
"Text VARCHAR(40) NOT NULL)