By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

How can I use VB to get Access datbase structure information??

P: n/a

Using Visual Basic with an ADO connection to MS-Access,
I'd like to be able to pull the names of all tables, the
column names in those tables, and the field TYPE
and COMMENT.

I've got the code below to get the Table Names
and Field Names, but don't know how to get
the TYPE and COMMENT information.

Mostly I probably just need the MS-Access Schema
so I know what they are called?

Thanks,
Gary

Dim xRS As New ADODB.Recordset

'----- EXAMPLE #1 FROM MS-WORD DOCUMENTION ABOUT ADO
' LISTS ALL TABLE-NAMES IN THE MDB
'Set xRS = gDBCN.OpenSchema(adSchemaTables)
'Do Until xRS.EOF
' If xRS.Fields("TABLE_TYPE") <> "VIEW" Then
' Debug.Print xRS.Fields("TABLE_NAME")
' End If
' xRS.MoveNext
'Loop
'xRS.Close

'----- EXAMPLE #2 FROM MICROSOFT SITE
' ADAPTED FOR MY MARS MDB - PRINTS ALL COLUMN NAMES FOR WIREORDERS
TABLE
Set xRS = gDBCN.OpenSchema(adSchemaColumns, Array(Empty, Empty,
"WireOrders"))
While Not xRS.EOF
Debug.Print xRS!COLUMN_NAME
Debug.Print xRS!COLUMN_NAME
xRS.MoveNext
Wend
xRS.Close
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

Nevermind - I found it. Will post the code here when
I polish it up a bit. Basically it's just "DESCRIPTIONS" Duh...

Debug.Print xRS!COLUMN_NAME, xRS!Description

"Gary Warner" <ja********@yahoo.com> wrote in message
news:2u*************@uni-berlin.de...

Using Visual Basic with an ADO connection to MS-Access,
I'd like to be able to pull the names of all tables, the
column names in those tables, and the field TYPE
and COMMENT.

I've got the code below to get the Table Names
and Field Names, but don't know how to get
the TYPE and COMMENT information.

Mostly I probably just need the MS-Access Schema
so I know what they are called?

Thanks,
Gary

Dim xRS As New ADODB.Recordset

'----- EXAMPLE #1 FROM MS-WORD DOCUMENTION ABOUT ADO
' LISTS ALL TABLE-NAMES IN THE MDB
'Set xRS = gDBCN.OpenSchema(adSchemaTables)
'Do Until xRS.EOF
' If xRS.Fields("TABLE_TYPE") <> "VIEW" Then
' Debug.Print xRS.Fields("TABLE_NAME")
' End If
' xRS.MoveNext
'Loop
'xRS.Close

'----- EXAMPLE #2 FROM MICROSOFT SITE
' ADAPTED FOR MY MARS MDB - PRINTS ALL COLUMN NAMES FOR WIREORDERS TABLE
Set xRS = gDBCN.OpenSchema(adSchemaColumns, Array(Empty, Empty,
"WireOrders"))
While Not xRS.EOF
Debug.Print xRS!COLUMN_NAME
Debug.Print xRS!COLUMN_NAME
xRS.MoveNext
Wend
xRS.Close

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.