J
JimP
Is it possible to list all of the tables/queries in a query via code?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
OpenRecordset() on something like this:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Type In (1,4,5,6))
AND (Not (MSysObjects.Name Like "MSys*"
Or MSysObjects.Name Like "~*"))
ORDER BY MSysObjects.Name;
Alternatively, loop through the TableDefs and QueryDefs.
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Type In (1,4,5,6))
AND (Not (MSysObjects.Name Like "MSys*"
Or MSysObjects.Name Like "~*"))
ORDER BY MSysObjects.Name;
Alternatively, loop through the TableDefs and QueryDefs.
J
JimP
Thanks, but I think this will give me a listing of the queries in an mdb.
What I want to do is list the tables and/or queries that comprise a single
query.
What I want to do is list the tables and/or queries that comprise a single
query.
A
Allen Browne
Hmm. You want to list the source "tables" that feed a query?
If you are using Access 2003 or 2007, you may be able to trace the
DependencyInfo object back to get the source tables. IME this doens't work
very well: it requires Name AutoCorrect to be turned on (heaps of issues),
and can trying to walk the DependencyInfor can actually crash Access
(typically where some tables/queries refer to objects that no longer exist
in the database), so I can't recommend that approach.
There are some commecial utilities that trace dependencies like that.
Examples:
http://www.speedferret.com/
http://www.rickworld.com/products.html
http://www.fmsinc.com/Products/
If you prefer to trace it yourself, each field in a QueryDef has a
SourceTable, so you could loop the Fields of the QueryDef and examine the
SourceTable of each. Provided the query outputs the field (it may not, e.g.
if it's just used as an INNER JOIN), that should list it, and you can then
discover whether it is a table or query and handle that recursively.
If you are using Access 2003 or 2007, you may be able to trace the
DependencyInfo object back to get the source tables. IME this doens't work
very well: it requires Name AutoCorrect to be turned on (heaps of issues),
and can trying to walk the DependencyInfor can actually crash Access
(typically where some tables/queries refer to objects that no longer exist
in the database), so I can't recommend that approach.
There are some commecial utilities that trace dependencies like that.
Examples:
http://www.speedferret.com/
http://www.rickworld.com/products.html
http://www.fmsinc.com/Products/
If you prefer to trace it yourself, each field in a QueryDef has a
SourceTable, so you could loop the Fields of the QueryDef and examine the
SourceTable of each. Provided the query outputs the field (it may not, e.g.
if it's just used as an INNER JOIN), that should list it, and you can then
discover whether it is a table or query and handle that recursively.
J
JimP
I like your last suggestion. I tried the attached code and received the
error "Operation not supported for this type of object". Ideas?
Sub ListQueryObjects()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Set db = CurrentDb
Set qdf = db.QueryDefs("CampaignRptSelNews")
For Each fld In qdf
Debug.Print fld.NAME
Next fld
End Sub
error "Operation not supported for this type of object". Ideas?
Sub ListQueryObjects()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Set db = CurrentDb
Set qdf = db.QueryDefs("CampaignRptSelNews")
For Each fld In qdf
Debug.Print fld.NAME
Next fld
End Sub
J
John Spencer
Try the following
Sub ListQueryObjects()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Set db = CurrentDb
Set qdf = db.QueryDefs("CampaignRptSelNews")
For Each fld In qdf.Fields '<<<<<<<<<<<<<<<<
Debug.Print fld.NAME
Next fld
End Sub
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Sub ListQueryObjects()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Set db = CurrentDb
Set qdf = db.QueryDefs("CampaignRptSelNews")
For Each fld In qdf.Fields '<<<<<<<<<<<<<<<<
Debug.Print fld.NAME
Next fld
End Sub
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
J
JimP
Thank you - that worked.
However, I was hoping it would print the source table/query name in front of
the field name. So I'm back where I started.
Is there a way to list the tables/queries that comprise a single query?
However, I was hoping it would print the source table/query name in front of
the field name. So I'm back where I started.
Is there a way to list the tables/queries that comprise a single query?
A
Allen Browne
Did you try the suggestion of using the SourceTable?
In John's code:
Debug.Print fld.Name & " = " & fld.SourceTable & "." & fld.SourceField
In John's code:
Debug.Print fld.Name & " = " & fld.SourceTable & "." & fld.SourceField
J
JimP
Thanks - will try it.
Allen Browne said:Did you try the suggestion of using the SourceTable?
In John's code:
Debug.Print fld.Name & " = " & fld.SourceTable & "." & fld.SourceField
J
JimP
...not sure anyone is still following this thread. I tried SourceTable.
Unfortunately the table(s) I am looking for are used to filter records and
are not included in the query grid.
Unfortunately the table(s) I am looking for are used to filter records and
are not included in the query grid.
A
Allen Browne
Jim, I dont' follow that.
If the table has no fields in the query, I don't see how you can get the
table name for the field that is not in the query from the query.
Guess I don't follow what you are doing, but you may have moved on to
something else now anyway.
If the table has no fields in the query, I don't see how you can get the
table name for the field that is not in the query from the query.
Guess I don't follow what you are doing, but you may have moved on to
something else now anyway.
J
JimP
You're right.
The table in this case is a temp table of "alpha codes" created from a
multi-select list box on a form. The routine that creates the tables,
converts all codes to lower case to eliminate the possibility of duplicates.
The temp table in turn links to SQL Server tables to extract data. Some of
the SQL Server tables have a mixture of upper/lower case codes.
I need to find which queries have the temp table to edit the SELECT
statement to force lower case joins (to many queries to manually inspect) -
case sensitivity is enabled.
I didn't design the SQL Server db. Ms-Access is just being used as a report
writer and I'm trying to assure that the table joins in queries are correct.
Jim
The table in this case is a temp table of "alpha codes" created from a
multi-select list box on a form. The routine that creates the tables,
converts all codes to lower case to eliminate the possibility of duplicates.
The temp table in turn links to SQL Server tables to extract data. Some of
the SQL Server tables have a mixture of upper/lower case codes.
I need to find which queries have the temp table to edit the SELECT
statement to force lower case joins (to many queries to manually inspect) -
case sensitivity is enabled.
I didn't design the SQL Server db. Ms-Access is just being used as a report
writer and I'm trying to assure that the table joins in queries are correct.
Jim
R
raskew via AccessMonster.com
The following will provide a listing of all queries and their sources. If
the
source is another query, you'll need to scroll to that query to see the
table(s) as source.
One problem I've been unable to resolve: If the query is a SELECT Top
the number provided will show up in the Source column. I've attempted
to resolve this using the Val() function, i.e. if the Source is a number,
that will appear as a number, if not it'll show as 0. The intent is to
filter on the field (Expr1), select only those records with Expr1 = 0,
however this returns 'Data Type Mismatch in Criteria Expression'.
Have done several searches where posters have described this problem
but none of them have resulted in a successful solution, other than a
response that 'the Val function should work'.
SELECT DISTINCT
A.Name AS Query
, B.Name1 AS Source
, Val([Name1]) AS Expr1
FROM
MSysObjects AS A
, MSysQueries AS B
WHERE
(((A.Id)=.[ObjectID])
AND
((B.Expression) Is Null)
AND
((Left([Name],1))<>'~')
AND
((Left([Name1],1))<>'['))
ORDER BY
A.Name
, Val([Name1]);ob
HTH - Bob
the
source is another query, you'll need to scroll to that query to see the
table(s) as source.
One problem I've been unable to resolve: If the query is a SELECT Top
the number provided will show up in the Source column. I've attempted
to resolve this using the Val() function, i.e. if the Source is a number,
that will appear as a number, if not it'll show as 0. The intent is to
filter on the field (Expr1), select only those records with Expr1 = 0,
however this returns 'Data Type Mismatch in Criteria Expression'.
Have done several searches where posters have described this problem
but none of them have resulted in a successful solution, other than a
response that 'the Val function should work'.
SELECT DISTINCT
A.Name AS Query
, B.Name1 AS Source
, Val([Name1]) AS Expr1
FROM
MSysObjects AS A
, MSysQueries AS B
WHERE
(((A.Id)=.[ObjectID])
AND
((B.Expression) Is Null)
AND
((Left([Name],1))<>'~')
AND
((Left([Name1],1))<>'['))
ORDER BY
A.Name
, Val([Name1]);ob
HTH - Bob
You're right.
The table in this case is a temp table of "alpha codes" created from a
multi-select list box on a form. The routine that creates the tables,
converts all codes to lower case to eliminate the possibility of duplicates.
The temp table in turn links to SQL Server tables to extract data. Some of
the SQL Server tables have a mixture of upper/lower case codes.
I need to find which queries have the temp table to edit the SELECT
statement to force lower case joins (to many queries to manually inspect) -
case sensitivity is enabled.
I didn't design the SQL Server db. Ms-Access is just being used as a report
writer and I'm trying to assure that the table joins in queries are correct.
Jim
[quoted text clipped - 7 lines]Jim, I dont' follow that.
R
raskew via AccessMonster.com
Thanks to RuralGuy at
http://www.access-programmers.co.uk/forums/showthread.php?p=715086&posted=1#post715086
the problem is resolved. Try this:
SELECT DISTINCT
A.Name AS Query
, B.Name1 AS Source
, IsNumeric([Name1]) AS Expr2
FROM
MSysObjects AS A
, MSysQueries AS B
WHERE
(((IsNumeric([Name1]))=False)
AND
((A.Id)=.[ObjectID])
AND
((B.Expression) Is Null)
AND
((Left([Name],1))<>'~')
AND
((Left([Name1],1))<>'['))
ORDER BY
A.Name;
http://www.access-programmers.co.uk/forums/showthread.php?p=715086&posted=1#post715086
the problem is resolved. Try this:
SELECT DISTINCT
A.Name AS Query
, B.Name1 AS Source
, IsNumeric([Name1]) AS Expr2
FROM
MSysObjects AS A
, MSysQueries AS B
WHERE
(((IsNumeric([Name1]))=False)
AND
((A.Id)=.[ObjectID])
AND
((B.Expression) Is Null)
AND
((Left([Name],1))<>'~')
AND
((Left([Name1],1))<>'['))
ORDER BY
A.Name;
The following will provide a listing of all queries and their sources. If
the
source is another query, you'll need to scroll to that query to see the
table(s) as source.
One problem I've been unable to resolve: If the query is a SELECT Top
the number provided will show up in the Source column. I've attempted
to resolve this using the Val() function, i.e. if the Source is a number,
that will appear as a number, if not it'll show as 0. The intent is to
filter on the field (Expr1), select only those records with Expr1 = 0,
however this returns 'Data Type Mismatch in Criteria Expression'.
Have done several searches where posters have described this problem
but none of them have resulted in a successful solution, other than a
response that 'the Val function should work'.
SELECT DISTINCT
A.Name AS Query
, B.Name1 AS Source
, Val([Name1]) AS Expr1
FROM
MSysObjects AS A
, MSysQueries AS B
WHERE
(((A.Id)=.[ObjectID])
AND
((B.Expression) Is Null)
AND
((Left([Name],1))<>'~')
AND
((Left([Name1],1))<>'['))
ORDER BY
A.Name
, Val([Name1]);ob
HTH - Bob
[quoted text clipped - 18 lines]You're right.