Iterate through table fields in ADP?

  • Thread starter Thread starter DStark
  • Start date Start date
D

DStark

Hi! Is it possible to interate through table fields in an ADP? What
I'd like to do is:

Sub PrintTableFields()
Dim dbs As Object
Dim tbl As AccessObject
Dim fld As ????

Set dbs = Application.CurrentData
For Each tbl In dbs.AllTables
Debug.Print tbl .Name
For Each fld in tbl.????
Debug.Print " " & fld.Name
Next fld
Next tbl

I can't seem to get to the Fields collection of a table. Any help
would be greatly appreciated!


TIA,

DStark
 
Well, this is how I do it using DAO. This prints the fields out to a list box
on a form:

Private Sub cmdOK_Click()

On Error GoTo error_handler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As Field
Dim i As Integer
Dim strInputBox As String
Dim tblName As String


lstData.RowSource = "" 'clears listbox

callInputBox:

strInputBox = InputBox("Enter a Table Name", "Name")
If strInputBox <> "" Or IsNull(strInputBox) Then
tblName = strInputBox
Else
MsgBox "No table name entered", vbOKOnly, "Error"
Exit Sub
End If

Set db = CurrentDb

Set rs = db.OpenRecordset(tblName)

For i = 1 To rs.Fields.Count - 1
lstData.AddItem rs.Fields(i).Name
Next

error_handler_exit:
rs.Close
Exit Sub

error_handler:
If Err.Number = 3078 Then
MsgBox "The table doesn't exist. Pick another", vbOKOnly, "Error"
Resume callInputBox
Else

MsgBox "Error number " & Err.Number & " occurred. The message is " &
Err.Description
Resume error_handler_exit

End If

End Sub
 
Back
Top