obtain list of fields

  • Thread starter Thread starter J. Freed
  • Start date Start date
J

J. Freed

I've been asked to generate a list of all the fields used within an mdb for
certain selected tables. Aside from going through each query and manually
inspecting them, is there a programmatic way to do this? (I've examined the
hidden system tables but they have not been helpful).

TIA.
 
I've been asked to generate a list of all the fields used within an mdb for
certain selected tables. Aside from going through each query and manually
inspecting them, is there a programmatic way to do this? (I've examined the
hidden system tables but they have not been helpful).

TIA.
Click to expand...

First youstate "all the fields used within an mdb" then you state
"going through each query". Which is it?

Anyway look in
Tools + Analyze + Documenter
 
Try these

'---------------------------------------------------------------------------------------
' Procedure : listTableFields
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of all the fields (column names) of a give
table
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTblName - Name of the table to list the fields of.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2007-June-01 Initial Release
'---------------------------------------------------------------------------------------
Function listTableFields(strTblName As String) As String
On Error GoTo listTableFields_Error
Dim db As DAO.Database
Dim tdfld As DAO.TableDef
Dim fld As Field

Set db = CurrentDb()
Set tdfld = db.TableDefs(strTblName)
For Each fld In tdfld.Fields 'loop through all the fields of the tables
Debug.Print fld.Name
Next

Set tdfld = Nothing
Set db = Nothing
If Err.Number = 0 Then Exit Function

listTableFields_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: listTableFields" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
Exit Function
End Function

'---------------------------------------------------------------------------------------
' Procedure : listQueryFields
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of all the fields (column names) of a give
Query
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strQryName - Name of the query to list the fields of.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2007-June-01 Initial Release
'---------------------------------------------------------------------------------------
Function listQueryFields(strQryName As String) As String
On Error GoTo listQueryFields_Error
Dim db As DAO.Database
Dim qryfld As DAO.QueryDef
Dim fld As Field

Set db = CurrentDb()
Set qryfld = db.QueryDefs(strQryName)
For Each fld In qryfld.Fields 'loop through all the fields of the Query
Debug.Print fld.Name
Next


Set qryfld = Nothing
Set db = Nothing
If Err.Number = 0 Then Exit Function

listQueryFields_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: listQueryFields" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
Exit Function
End Function

--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.
 
Back
Top