Help with Access Table Field Description 1

[bigfoot]

I noticed there is a place for a row (field) description in Access. How do I get to this in VB6?

Name, Type, Description

I am building an ad-hoc query tool, and I could really use the description field being filled in so my customers know what the field is for.

Thanks

 

[zemp]

You canuse the ADO .openSchema method.

Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset

Set rs = Conn.OpenSchema(adSchemaTables, Array(Empty, Empty, "Table1&quot)

While Not rs.EOF
Debug.Print rs!table_name
Set rs2 = Conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & rs!table_name & "&quot)
While Not rs2.EOF
Debug.Print " " & rs2!column_name
Debug.Print " " & rs2!data_type
Debug.Print " " & rs2!Description
rs2.MoveNext
Wend
rs.MoveNext
Wend
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing

Where 'Conn' is a valid open ADO connexction and 'Table1' is the name of your access table.
Thanks and Good Luck!

zemp

 

[bigfoot]

Try as I may, I can not get this to work. I get either nothing or NULL. What am I doing wrong? I am using this on an Access 97 database. Would that make any difference.

and I HATE MICROSOFT'S HELP!!! Or lack of it!

 

[zemp]

It maybe because of Access 97. The code I posted works on Access 2000 with ADO 2.6.

Make sure the name of your table replace 'Table1' in my code and similarily the name of your connection object replaces 'Conn'.

Are all values Null or nothing or just the description? Make sure that you have entered a description for the fields in the Access table while in design mode. Thanks and Good Luck!

zemp

 

[bigfoot]

GOT IT!!!!

The connection string needs to be:
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestStuff\SchemaTest\SchemaTest97.mdb;Persist Security Info=False"

Not:
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\TestStuff\SchemaTest\SchemaTest97.mdb;Persist Security Info=False"

Even though it's an Access 97 database, the provider needs to be 4.0 and not 3.51.

Thanks for all of the help.

P.S. I posted the answer in case anyone else had the same trouble.

 

[zemp]

Glad you got it working. And it is great that you posted the solution that worked for you so others, including myself, can learn from it.

That is how we want our forums to work. A star for you. Thanks and Good Luck!

zemp

 

[CCLINT]

>Even though it's an Access 97 database, the provider needs to be 4.0 and not 3.51.

It can also be 3.51.
This provider just needs to be installed on your system (MDAC 2.1 and JET 3 SP3).

The 3.51 provider will work better, and faster, with a JET '97 MDB because when reading and writing the data, string conversions to not need to take place each time. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[Weebairnes]

I Can't get the Description to display.

Following Zemp's example, I was able to see the debug.print display values for the COLUMN_NAME and the data_type, but just as Bigfoot mentioned, I cannot see the Description. It returns Null for every field in the table.

My connection string is
Provider=Microsoft.Jet.OLEDB.4.0; blahblahblah

Access xp on Windows xp. Can you think of anything else that may be wrong?

Code below works for COLUMN_NAME and the data_type, but not description.


Function mytry()

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset

Set conn = CurrentProject.Connection
Set rs = conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "tblCRF_BaselineIv&quot)
While Not rs.EOF
Debug.Print " " & rs!COLUMN_NAME & vbTab & rs!data_type
Debug.Print " " & rs!Description
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Function

 

[Weebairnes]

In an attempt to get the field description...
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 is part of the results (notice that description is null, but my table design has a description comment.) I can get it 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: