List Fields and Tables in Query

[Karen]

I would like a piece of code that will tell me what the table names
and field names are in a particular query. In other words if I have
query named "qryEmployee" based on table Employee with the fields
LastName and FirstName only used. I would like a way to show Table:
Employee and Fields: LastName, FirstName.

Thanks in advance,

Karen

[Marshall Barton]

Karen,

If you loop through the querydef's Field collection, you can
retrieve the properties of each field. You probably want the
Name and SourceTable properties and maybe SourceField as
well. There's a sample of a Sub procedure to display those
properties below.

Marsh

__________________________________
Public Sub QueryProps(Qname As String)
Dim db As Database
Dim qdf As QueryDef
Dim fld As Field

On Error Resume Next

Set db = CurrentDb
Set qdf = db.QueryDefs(Qname)

For Each fld In qdf.Fields
Debug.Print fld.Name
Debug.Print " Source Table: "; fld.SourceTable
Debug.Print " Source Field: "; fld.SourceField
Next fld

Set qdf = Nothing
Set db = Nothing
End Sub

Karen wrote in message
<26bf02e8.01092...@posting.google.com>...

[Imboden]

kmit...@surfree.com (Karen) wrote in message news:<26bf02e8.01092...@posting.google.com>...

Karen-

This will give what you ask, although in slightly different
format. To use, copy function to a module in any database.
Then, from debug window type ? GetQueries <enter>

Function GetQueries()
'*******************************************
'Name: GetQueries (Function)
'Purpose: Creates tblQueries and populates
' with query info.
'Author: imboden
'Date: November 19, 2000
'*******************************************

Dim db As DATABASE
Dim qd As QueryDefs
Dim found As Boolean, test As String
Dim rs As Recordset
Dim tName As String, tfield As String, tsource As String
Dim n As Integer, i As Integer, fcount As Integer
Dim x As Integer

Set db = CurrentDb
Set qd = db.QueryDefs

On Error Resume Next
tName = "tblQueries"
'Does table "tblQueries" exist? If true, delete it;
found = False
test = db.TableDefs(tName).Name
If Err <> 3265 Then
found = True
docmd.DeleteObject acTable, "tblQueries"
End If
'Create new tblTable
db.Execute "CREATE TABLE tblQueries(Object TEXT (55), FieldName TEXT
(55), RecordSource TEXT (55));"
Set rs = db.OpenRecordset("tblQueries")

'find out how many queries there are in the database
n = qd.Count

'loop thru each of the querydefs to capture the query
'name, field names and the source table (or query)
'for the particular field
For i = 0 To n - 1
tName = qd(i).Name
fcount = qd(i).Fields.Count
For x = 0 To fcount - 1
If Left(tName, 1) <> "~" And Len(tName) > 0 Then
rs.AddNew
tfield = qd(i).Fields(x).Name
tsource = qd(i).Fields(x).SourceTable
rs!Object = tName
rs!fieldname = tfield
rs!RecordSource = tsource
rs.Update
End If
Next x
Next i

rs.Close
db.Close
Set qd = Nothing
Set db = Nothing

End Function