Reading field names from a D-base

[sonicblis]

I am writing a little program for myself and would like to be able to pull table name from a database, and then field names from the table. I know how to do this in ASP but I'm relatively new to VB and could really use the help. I knew if there was help to be had, this was the place to get it.

[parksie]

What type of DB? Are you using ADO or DAO?

For Access with DAO, use this:

For i = 1 To db.TableDefs(0).Fields.Count
     Debug.Print db.TableDefs(0).Fields(i - 1).Name
Next

...where db is your Database object.

[sonicblis]

Unfortunately you're going to have to assume I know less than you think I do. I understand your code but not how to get to that point, and isn't that just for field names? what about table names? Thanks for the help.

[sonicblis]

I connected to the database, and I can iterate through the names, but how do I build a drop down list or a select box out of the values?

[parksie]

Use:

List1.AddItem db.TableDefs(0).Fields(i - 1).Name

or similar.

DAO's objects often have Collection-based properties, for example, under TableDefs, you can use either:

db.TableDefs(0) ' By index

or

db.TableDefs("MyTable") ' By name

The same goes for Fields, QueryDefs and so on. Check out the documentation for specifics.

[sonicblis]

I'm still not doing it right for some reason because I'm getting values returned that don't exist in my database at all like (data) and (table), but I'll keep playing with it. I know that you have set me on the right track though and so you have the deepest thanks I can offer.

[sonicblis]

Why does this:

For i = 1 to db.TableDefs(0).Fields.Count
     List1.AddItem db.TableDefs(0).Fields(i-1).Name
Next

NOT return the same as this:

For i = 1 to db.TableDefs("Table1").Fields.Count
     List1.AddItem db.TableDefs("Table1").Fields(i-1).Name
Next

Even though table1 is the first table in the database and I thought would equal tabledef(0)? When I use the tabledef(0) I only get half the fields that are in the four field table. I'm very confused, especially since one of the field names returned isn't even in the table! Please help me out.

[HeSaidJoe]

[I get the same for both arguments [field names]]

Option Explicit

Public db As Database
Public rs As Recordset

Private Sub Command1_Click()
Dim i As Integer

Set db = Workspaces(0).OpenDatabase("C:\keepers\art.mdb")
      
For i = 1 To db.TableDefs(0).Fields.Count
     List1.AddItem db.TableDefs(0).Fields(i - 1).Name
Next
End Sub

Private Sub Command2_Click()
Dim i As Integer
Set db = Workspaces(0).OpenDatabase("C:\keepers\art.mdb")

For i = 1 To db.TableDefs("art").Fields.Count
     List1.AddItem db.TableDefs("art").Fields(i - 1).Name
Next
End Sub

[parksie]

You've probably opened one of the system tables, which store information about the database. In this case, it is always best to use the table name.