Extracting table names and field names from Access 2000 db via code

[Jethro]

This has to be possible, but can't suss it at all

[peet]

'Add a reference to Microsoft ADO Ext. X.X For DLL And Security
'Add a reference to Microsoft ActiveX Data Objects X.X Library
Option Explicit
 
Private cat As ADOX.Catalog
Private tbl As ADOX.Table
Private cnn As New ADODB.Connection
Private col As New Column
 
Private Sub Command1_Click()
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    
    'setup db connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO.MDB"
    Set cat.ActiveConnection = cnn
    
    'loop through all tables, and all fields in each table.
    For Each tbl In cat.Tables
        Debug.Print tbl.Name
        For Each col In tbl.Columns
            Debug.Print vbTab & vbTab & col.Name
        Next col
    Next tbl
    
    'close the connection
    cnn.Close
    Set cnn = Nothing
End Sub

should get u going Jello

[Jethro]

Hey thanks Peet .... that should do the trick cool...

[peet]

good

CoolColorfullMovingJelloAvatar DF is doing avatars all day long?

*mumble*gmprrs* damn competition !

[Jethro]

She's doing a few for the latest TT party next friday ... of course she would never think of competing with Ye Olde Viking Avatars...

Just plowing through your code post at the moment. Thanks again peet a real life saver, have to demo a beta early next week

[JasonGS]

You can also query the system tables in the database. To view them, goto Tool / Options in Access and check the "System objects" in the "Show" group.

To get a recordset of all tables, you can issue the SQL statement: SELECT [Name] FROM [MSysObjects] WHERE (type = 1)

[Jethro]

Thanks Jason unfort need to do it via code.


OKie dokie Peet that worked a treat got the following happening

a) Connect to mdb

b) Combo drop down of files

c) User selects a file

d) Field names, type, and size displayed.

Two further problems.

1) After the user selects a file, it would appear that l have to loop through the table list again to get Tbl.Name = combo select, in order to display the field details. Is there a better way of doing this.

2) Field types are coming back as integers, e.g 130 = text. Not a huge problem but anyway of translating it without resorting to a Select Case statement and manually translating it.

[peet]

morning Jethro,

1) cant think, and don know, of a better/smarter way to do it

2) I use select case, have not found a better way. There are built
in const. for field type when using DAO.

Private Function GetVarType(dbFieldTypeConst As Long) As String
    Select Case dbFieldTypeConst
    Case dbText
        GetVarType = "String"
    Case dbBoolean
        GetVarType = "Boolean"
    Case dbBigInt
        GetVarType = "LongInt"
    Case dbDate
        GetVarType = "String"
    Case dbDouble
        GetVarType = "Double"
    Case dbInteger
        GetVarType = "Integer"
    Case dbLong
        GetVarType = "Long"
    Case dbMemo
        GetVarType = "String"
    Case dbSingle
        GetVarType = "Single"
    Case dbText
        GetVarType = "String"
    Case Else
        GetVarType = "String"
    End Select
End Function

use that in an old app I'v made, there are probably const. for the ADOX returned types aswell, I'd use them. (don't know what they
are though )

[Jethro]

Thanks again oh great viking coding god


Okie dokie .... expect more questions....