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]

Text is a reserved word in Access. You can surround the field name with brackets and use it (as in: [Text]) but I strongly suggest not using any reserved words as column names in order to avoid problems like you are now encountering.


Mark

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

 

[farnumt]

I found Mark's code today while looking for a solution for listing all column attributes (table name, column name, data type and length if relevant, if required, Caption, and Description) in defined table column sequence for table names beginning with TemplateData_.

It has been quite a while since I wrote Access Basic code.

Below are two possible solution functions (untidy because they don't have return values but they are part of a bigger rfequirement). Function TableColums returns the column number sequence in the table which Mark's function (modified to solve the problem with access using the connection string) does not do.

I still have a problem though because neither returns the Caption column property. Can anyone help with this?

Thanks.

Tony

Public Function TableColumns()
Dim cnn As New ADODB.Connection
Dim tbl As New ADODB.Recordset
Dim col As New ADODB.Recordset
Dim fld As ADODB.Field


Set cnn = Application.CurrentProject.Connection

Set col = cnn.OpenSchema(adSchemaColumns)

Do Until col.EOF
If Left(col!TABLE_NAME, 13) = "TemplateData_" Then
MsgBox "[" & Nz(col.Fields.Item(2).Value, "") & "].[" & Nz(col.Fields.Item(3).Value, "") & "], " & _
Nz(col.Fields.Item(6).Value) & ", " & Nz(col.Fields.Item(10).Value) & ", " & _
Nz(col.Fields.Item(11).Value) & ", " & Nz(col.Fields.Item(13).Value) & ", " & _
Nz(col.Fields.Item(27).Value)

End If
col.MoveNext
Loop

End Function

Public Function TableProperties(TableName As String) As String
' Project reference Microsoft ADO Ext. 2.x for DDL & Security
Dim oCat As New ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oProp As ADOX.Property
Dim strCol As String
Dim dcat1 As ADODB.Connection
Dim dfld As ADODB.Fields
Dim dtab As ADODB.Recordset


oCat.ActiveConnection = Application.CurrentProject.Connection
For Each oTable In oCat.Tables
If oTable.Type = "TABLE" Then
If UCase(oTable.Name) = UCase(TableName) Then
For Each oColumn In oTable.Columns
strCol = "[" & oColumn.Name & "] (" & oColumn.Type & " " & oColumn.DefinedSize & ")"
MsgBox oColumn.Properties.Item(1)
For Each oProp In oColumn.Properties
strCol = strCol & ", " & oProp.Name & "=" & oProp.Value
Next
MsgBox strCol
Next
End If
End If
Next
Set oProp = Nothing
Set oColumn = Nothing
Set oTable = Nothing
Set oCat = Nothing
End Function

 

[amigo02]

Hi there
I have a master database (ms access 2000) that I use to develop my application and I modify it almost daily. My application is being used by several client companies already and I want to reflect those master database changes on their current databases too.

To accomplish that I wrote the following vb.net code
The code supposed to find the new tables in master table and replicate them in current client database

catMaster.ActiveConnection = oConnMaster
catCurrent.ActiveConnection = oConnCurrent

On Error Resume Next
For Each tblmaster In catMaster.Tables
i = 0
If tblmaster.Type = "TABLE" And InStr(tblmaster.Name, "~") <= 0 And InStr(tblmaster.Name, "TempTable") <= 0 And tblmaster.Name <> "Paste Errors" Then ' type 1 is the user tables
For Each tblcurrent In catCurrent.Tables
If tblmaster.Name = tblcurrent.Name Then
'exists
i = 1
End If
Next
If i = 0 Then 'table does not exists in current database
Label1.Text = Label1.Text & "TABLE =" & tblmaster.Name & " is being appended<br>"
tbltemp = New ADOX.Table()
tbltemp.Name = tblmaster.Name
For Each col In tblmaster.Columns
colnew = New ADOX.Column()
colnew.Attributes = col.Attributes
colnew.DefinedSize = col.DefinedSize
colnew.Name = col.Name
colnew.NumericScale = col.NumericScale
colnew.ParentCatalog = col.ParentCatalog
colnew.Precision = col.Precision
For Each p In col.Properties
colnew.Properties(p.Name).Value = p.Value
Next
tbltemp.Columns.Append(colnew)
Next
catCurrent.Tables.Append(tbltemp)
End If
End If

Next


above code gives me the following error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

 

[fredericofonseca]

Amigo02,

Please raise a new question instead of posting on an existing one.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com