How to Query ACCESS db structure from VB6

[Cap Kirk]

I have written a program in VB6 that picks records in an ACCESS db and
stores them in proper EXCEL sheeets. Now, all table names and record names
and types I have hard coded in the VB program. Next, I refined it by feeding
the proper table names and record names from a file (read them from a file
and build the SELECT as needed. All works fine.
Now I need to go a step further, but I am 100% stuck: I want to retrieve the
ACCESS db table names from the DB and for each table the record names an
types programmatically, after opening the db.

I have no idea how to. Any help will be welcome.

 

[Brendan Reynolds]

As a demonstration, this just prints the info to the VBA Immediate Window.
Hopefully you'll be able to adapt it to your needs ...

Public Sub TablesAndFields()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = DAO.DBEngine.OpenDatabase("C:\Program Files\Microsoft
Office\Office11\Samples\Northwind.mdb")
Set tdfs = db.TableDefs
For Each tdf In tdfs
If UCase$(Left$(tdf.Name, 4)) <> "MSYS" And UCase$(Left$(tdf.Name,
4)) <> "USYS" Then
Debug.Print "Table Name: " & tdf.Name
Set rst = db.OpenRecordset("SELECT * FROM [" & tdf.Name & "]
WHERE False")
Set flds = rst.Fields
For Each fld In flds
Debug.Print "Field Name: " & fld.Name, "Field Type: " &
fld.Type
Next fld
rst.Close
Set rst = Nothing
End If
Next tdf
db.Close

End Sub

 

[Cap Kirk]

Thank you very much, Brendan. I have not tried it yet but it looks like I
have all I need there. I'll have to experiment a little now, but that is the
fun part of it.

Cap Kirk

As a demonstration, this just prints the info to the VBA Immediate Window.
Hopefully you'll be able to adapt it to your needs ...

Public Sub TablesAndFields()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = DAO.DBEngine.OpenDatabase("C:\Program Files\Microsoft
Office\Office11\Samples\Northwind.mdb")
Set tdfs = db.TableDefs
For Each tdf In tdfs
If UCase$(Left$(tdf.Name, 4)) <> "MSYS" And UCase$(Left$(tdf.Name,
4)) <> "USYS" Then
Debug.Print "Table Name: " & tdf.Name
Set rst = db.OpenRecordset("SELECT * FROM [" & tdf.Name & "]
WHERE False")
Set flds = rst.Fields
For Each fld In flds
Debug.Print "Field Name: " & fld.Name, "Field Type: " &
fld.Type
Next fld
rst.Close
Set rst = Nothing
End If
Next tdf
db.Close

End Sub

--
Brendan Reynolds
Access MVP

I have written a program in VB6 that picks records in an ACCESS db and
stores them in proper EXCEL sheeets. Now, all table names and record names
and types I have hard coded in the VB program. Next, I refined it by
feeding
the proper table names and record names from a file (read them from a file
and build the SELECT as needed. All works fine.
Now I need to go a step further, but I am 100% stuck: I want to retrieve
the
ACCESS db table names from the DB and for each table the record names an
types programmatically, after opening the db.

I have no idea how to. Any help will be welcome.