PDA

View Full Version : Getting list of table & field names from Access into Excel using VBA



shivboy
06-13-2006, 01:47 AM
I am using VBA in MS Excel to create tables in MS Access and set relationships between the tables as I have large amounts of related data. Now in order to do that, I need to know how to list the names of the tables and fields in the tables in the database. This would help me to choose tables and further choose fields in those tables to set relationships between them. How do I do that? Please help.

Peace,

Shivboy

acw
06-14-2006, 04:37 PM
Hi

Hope the following code gets you going.

In the VBE, go tools, references and select the microsoft DAO... reference. Adjust the code to select the relevant database required. This will cycle through all the tables in the database (including the inbuilt) and show all the table names, and the fields in those tables. The output is only to the screen, so you will have to adapt to an output medium that suits.


Sub bbb()
Dim wj As Workspace
Dim db As database
Dim rs As Recordset

Set wj = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wj.OpenDatabase("c:\temp\test.mdb")
For Each ce In db.TableDefs
For Each f In ce.Fields
MsgBox "Table: " & ce.Name & " Field: " & f.Name
Next f
Next ce


Set db = Nothing
Set wj = Nothing
End Sub


Tony