I do I find the query that made a table?

[Dea]

I have a really large databse with 50 tables. When I am looking at the
tables, how can I discover the query that made a specific table. I know I
can "hover" over the query to see the table it makes. I have tried "Table
Related Views". I have tried Object dependencies--I would like to see the
query in Objects that I depend on, but that doesn't work. Any ideas?

 

[Dale Fye]

Well,

Most queries don't "make" tables. However if you are sure that the table is
created by a "make table" query, you could write a function that searches all
of the queries in your database, looking for the name of the table.
something like:

Public Function fnFindQuery(TableName as String) as String

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 then
fnFindQuery = fnFindQuery & ", " & qdf.name
endif
Next

if len(fnFindQuery & "") > 0 then fnFindQuery = mid(fnFindQuery, 3)

End Function

If you copy this function and paste it in a code module, you can then call
it from the VBA Immediate window. It will return the name of all the queries
that contain the term "INTO" (which is necessary for a make-table query) and
the name of the table you passed as the argument.

 

[Dea]

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1, here is the module that I inserted the table name
in...I then clicked the run button in the module. Should this have produced
the query name? It actually brought up a dialog box for Macros...

Public Function fnFindQuery(T_DEGREE_Population_step_1 As String) As String

Dim qdf As dao.QueryDef

For Each qdf In CurrentDb.QueryDefs

If InStr(qdf.SQL, "INTO") > 0 And InStr(qdf.SQL,
T_DEGREE_Population_step_1) > 0 Then
fnFindQuery = fnFindQuery & ", " & qdf.Name
End If
Next

If Len(fnFindQuery & "") > 0 Then fnFindQuery = Mid(fnFindQuery, 3)

End Function

 

[Dale Fye]

Coyp the code I gave you and paste it (exactly as I wrote it) in a code module.

Then, in the Immediate window (this is usually displayed immediately below
the code window in the VB editor. If you don't see it, go to the menus on
the VB Editor window and select "View" -> "Immediate Window").

Once the immediate window is visible, type:

?fnFindQuery("T_DEGREE_Population_step_1")

and then hit return. As indicated in my original post, this should print
out the names of the queries that contain "INTO" and the name you passed the
function. If a query uses the table name that you passed to the function to
create another table in your database, that query will show up as well, since
my code does not actually search for:

"INTO T_DEGREE_Population_step_1"

I setup the code to search for "INTO" and the table name you passed it
separately because the table name could possess spaces, in which case the SQL
would look like:

INTO [T_DEGREE_Population_step_1]

 

[Dea]

Okay, this is great....I received the following results and wonder what you
would change in the function to only list the query I need, example, I just
happen to know that QM_Degree_Population_step_1 is the query that made
T_DEGREE_population_step_1 (wish others here used this kind of naming
convention, but alas...). The other queries listed in your output are all
the make/append queries in the database that depend on this table as input
table.

?fnFindQuery("T_DEGREE_Population_step_1")
QA_DEGREE_Population_1_2, QM_AIC_home_town_for_honors, QM_All_Honors,
QM_Degree_Population_all_fields, QM_Degree_Population_step_1,
QM_Find_duplicates_step_1_dual_degrees

Coyp the code I gave you and paste it (exactly as I wrote it) in a code module.

Then, in the Immediate window (this is usually displayed immediately below
the code window in the VB editor. If you don't see it, go to the menus on
the VB Editor window and select "View" -> "Immediate Window").

Once the immediate window is visible, type:

?fnFindQuery("T_DEGREE_Population_step_1")

and then hit return. As indicated in my original post, this should print
out the names of the queries that contain "INTO" and the name you passed the
function. If a query uses the table name that you passed to the function to
create another table in your database, that query will show up as well, since
my code does not actually search for:

"INTO T_DEGREE_Population_step_1"

I setup the code to search for "INTO" and the table name you passed it
separately because the table name could possess spaces, in which case the SQL
would look like:

INTO [T_DEGREE_Population_step_1]

----
HTH
Dale

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1, here is the module that I inserted the table name
in...I then clicked the run button in the module. Should this have produced
the query name? It actually brought up a dialog box for Macros...

Public Function fnFindQuery(T_DEGREE_Population_step_1 As String) As String

Dim qdf As dao.QueryDef

For Each qdf In CurrentDb.QueryDefs

If InStr(qdf.SQL, "INTO") > 0 And InStr(qdf.SQL,
T_DEGREE_Population_step_1) > 0 Then
fnFindQuery = fnFindQuery & ", " & qdf.Name
End If
Next

If Len(fnFindQuery & "") > 0 Then fnFindQuery = Mid(fnFindQuery, 3)

End Function

 

[Dale Fye]

Try changing the If statement line to:

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 AND
instr(qdf.sql, "INSERT") = 0 then

----
HTH
Dale

Okay, this is great....I received the following results and wonder what you
would change in the function to only list the query I need, example, I just
happen to know that QM_Degree_Population_step_1 is the query that made
T_DEGREE_population_step_1 (wish others here used this kind of naming
convention, but alas...). The other queries listed in your output are all
the make/append queries in the database that depend on this table as input
table.

?fnFindQuery("T_DEGREE_Population_step_1")
QA_DEGREE_Population_1_2, QM_AIC_home_town_for_honors, QM_All_Honors,
QM_Degree_Population_all_fields, QM_Degree_Population_step_1,
QM_Find_duplicates_step_1_dual_degrees

Coyp the code I gave you and paste it (exactly as I wrote it) in a code module.

Then, in the Immediate window (this is usually displayed immediately below
the code window in the VB editor. If you don't see it, go to the menus on
the VB Editor window and select "View" -> "Immediate Window").

Once the immediate window is visible, type:

?fnFindQuery("T_DEGREE_Population_step_1")

and then hit return. As indicated in my original post, this should print
out the names of the queries that contain "INTO" and the name you passed the
function. If a query uses the table name that you passed to the function to
create another table in your database, that query will show up as well, since
my code does not actually search for:

"INTO T_DEGREE_Population_step_1"

I setup the code to search for "INTO" and the table name you passed it
separately because the table name could possess spaces, in which case the SQL
would look like:

INTO [T_DEGREE_Population_step_1]

----
HTH
Dale

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1, here is the module that I inserted the table name
in...I then clicked the run button in the module. Should this have produced
the query name? It actually brought up a dialog box for Macros...

Public Function fnFindQuery(T_DEGREE_Population_step_1 As String) As String

Dim qdf As dao.QueryDef

For Each qdf In CurrentDb.QueryDefs

If InStr(qdf.SQL, "INTO") > 0 And InStr(qdf.SQL,
T_DEGREE_Population_step_1) > 0 Then
fnFindQuery = fnFindQuery & ", " & qdf.Name
End If
Next

If Len(fnFindQuery & "") > 0 Then fnFindQuery = Mid(fnFindQuery, 3)

End Function



:

Well,

Most queries don't "make" tables. However if you are sure that the table is
created by a "make table" query, you could write a function that searches all
of the queries in your database, looking for the name of the table.
something like:

Public Function fnFindQuery(TableName as String) as String

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 then
fnFindQuery = fnFindQuery & ", " & qdf.name
endif
Next

if len(fnFindQuery & "") > 0 then fnFindQuery = mid(fnFindQuery, 3)

End Function

If you copy this function and paste it in a code module, you can then call
it from the VBA Immediate window. It will return the name of all the queries
that contain the term "INTO" (which is necessary for a make-table query) and
the name of the table you passed as the argument.

----
HTH
Dale



:

I have a really large databse with 50 tables. When I am looking at the
tables, how can I discover the query that made a specific table. I know I
can "hover" over the query to see the table it makes. I have tried "Table
Related Views". I have tried Object dependencies--I would like to see the
query in Objects that I depend on, but that doesn't work. Any ideas?

 

[John Spencer]

Try this comparison

If QDF.SQL like "*Into *" & TableName & "*"
AND NOT Qdf.SQL LIKE "*FROM *" & TableName & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Try changing the If statement line to:

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 AND
instr(qdf.sql, "INSERT") = 0 then

----
HTH
Dale

Okay, this is great....I received the following results and wonder what you
would change in the function to only list the query I need, example, I just
happen to know that QM_Degree_Population_step_1 is the query that made
T_DEGREE_population_step_1 (wish others here used this kind of naming
convention, but alas...). The other queries listed in your output are all
the make/append queries in the database that depend on this table as input
table.

?fnFindQuery("T_DEGREE_Population_step_1")
QA_DEGREE_Population_1_2, QM_AIC_home_town_for_honors, QM_All_Honors,
QM_Degree_Population_all_fields, QM_Degree_Population_step_1,
QM_Find_duplicates_step_1_dual_degrees

Coyp the code I gave you and paste it (exactly as I wrote it) in a code module.

Then, in the Immediate window (this is usually displayed immediately below
the code window in the VB editor. If you don't see it, go to the menus on
the VB Editor window and select "View" -> "Immediate Window").

Once the immediate window is visible, type:

?fnFindQuery("T_DEGREE_Population_step_1")

and then hit return. As indicated in my original post, this should print
out the names of the queries that contain "INTO" and the name you passed the
function. If a query uses the table name that you passed to the function to
create another table in your database, that query will show up as well, since
my code does not actually search for:

"INTO T_DEGREE_Population_step_1"

I setup the code to search for "INTO" and the table name you passed it
separately because the table name could possess spaces, in which case the SQL
would look like:

INTO [T_DEGREE_Population_step_1]

----
HTH
Dale



:

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1, here is the module that I inserted the table name
in...I then clicked the run button in the module. Should this have produced
the query name? It actually brought up a dialog box for Macros...

Public Function fnFindQuery(T_DEGREE_Population_step_1 As String) As String

Dim qdf As dao.QueryDef

For Each qdf In CurrentDb.QueryDefs

If InStr(qdf.SQL, "INTO") > 0 And InStr(qdf.SQL,
T_DEGREE_Population_step_1) > 0 Then
fnFindQuery = fnFindQuery & ", " & qdf.Name
End If
Next

If Len(fnFindQuery & "") > 0 Then fnFindQuery = Mid(fnFindQuery, 3)

End Function



:

Well,

Most queries don't "make" tables. However if you are sure that the table is
created by a "make table" query, you could write a function that searches all
of the queries in your database, looking for the name of the table.
something like:

Public Function fnFindQuery(TableName as String) as String

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 then
fnFindQuery = fnFindQuery & ", " & qdf.name
endif
Next

if len(fnFindQuery & "") > 0 then fnFindQuery = mid(fnFindQuery, 3)

End Function

If you copy this function and paste it in a code module, you can then call
it from the VBA Immediate window. It will return the name of all the queries
that contain the term "INTO" (which is necessary for a make-table query) and
the name of the table you passed as the argument.

----
HTH
Dale



:

I have a really large databse with 50 tables. When I am looking at the
tables, how can I discover the query that made a specific table. I know I
can "hover" over the query to see the table it makes. I have tried "Table
Related Views". I have tried Object dependencies--I would like to see the
query in Objects that I depend on, but that doesn't work. Any ideas?

 

[Dale Fye]

John,

I'm keep forgetting that you can use the Like operator in an IF statement.

Wonder which is "quicker"?

----
HTH
Dale

Try this comparison

If QDF.SQL like "*Into *" & TableName & "*"
AND NOT Qdf.SQL LIKE "*FROM *" & TableName & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Try changing the If statement line to:

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 AND
instr(qdf.sql, "INSERT") = 0 then

----
HTH
Dale

Okay, this is great....I received the following results and wonder what you
would change in the function to only list the query I need, example, I just
happen to know that QM_Degree_Population_step_1 is the query that made
T_DEGREE_population_step_1 (wish others here used this kind of naming
convention, but alas...). The other queries listed in your output are all
the make/append queries in the database that depend on this table as input
table.

?fnFindQuery("T_DEGREE_Population_step_1")
QA_DEGREE_Population_1_2, QM_AIC_home_town_for_honors, QM_All_Honors,
QM_Degree_Population_all_fields, QM_Degree_Population_step_1,
QM_Find_duplicates_step_1_dual_degrees



:

Coyp the code I gave you and paste it (exactly as I wrote it) in a code module.

Then, in the Immediate window (this is usually displayed immediately below
the code window in the VB editor. If you don't see it, go to the menus on
the VB Editor window and select "View" -> "Immediate Window").

Once the immediate window is visible, type:

?fnFindQuery("T_DEGREE_Population_step_1")

and then hit return. As indicated in my original post, this should print
out the names of the queries that contain "INTO" and the name you passed the
function. If a query uses the table name that you passed to the function to
create another table in your database, that query will show up as well, since
my code does not actually search for:

"INTO T_DEGREE_Population_step_1"

I setup the code to search for "INTO" and the table name you passed it
separately because the table name could possess spaces, in which case the SQL
would look like:

INTO [T_DEGREE_Population_step_1]

----
HTH
Dale



:

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1, here is the module that I inserted the table name
in...I then clicked the run button in the module. Should this have produced
the query name? It actually brought up a dialog box for Macros...

Public Function fnFindQuery(T_DEGREE_Population_step_1 As String) As String

Dim qdf As dao.QueryDef

For Each qdf In CurrentDb.QueryDefs

If InStr(qdf.SQL, "INTO") > 0 And InStr(qdf.SQL,
T_DEGREE_Population_step_1) > 0 Then
fnFindQuery = fnFindQuery & ", " & qdf.Name
End If
Next

If Len(fnFindQuery & "") > 0 Then fnFindQuery = Mid(fnFindQuery, 3)

End Function



:

Well,

Most queries don't "make" tables. However if you are sure that the table is
created by a "make table" query, you could write a function that searches all
of the queries in your database, looking for the name of the table.
something like:

Public Function fnFindQuery(TableName as String) as String

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

if instr(qdf.sql, "INTO") > 0 AND instr(qdf.sql, TableName) > 0 then
fnFindQuery = fnFindQuery & ", " & qdf.name
endif
Next

if len(fnFindQuery & "") > 0 then fnFindQuery = mid(fnFindQuery, 3)

End Function

If you copy this function and paste it in a code module, you can then call
it from the VBA Immediate window. It will return the name of all the queries
that contain the term "INTO" (which is necessary for a make-table query) and
the name of the table you passed as the argument.

----
HTH
Dale



:

I have a really large databse with 50 tables. When I am looking at the
tables, how can I discover the query that made a specific table. I know I
can "hover" over the query to see the table it makes. I have tried "Table
Related Views". I have tried Object dependencies--I would like to see the
query in Objects that I depend on, but that doesn't work. Any ideas?

 

[Clifford Bass]

Hi Dea,

Try this query:

SELECT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id
WHERE (((MSysQueries.Name1)="T_DEGREE_population_step_1") AND
((MSysQueries.Attribute)=1) AND ((MSysQueries.Flag)=2))
ORDER BY MSysObjects.Name;

Clifford Bass

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1

[snip]

 

[Dale Fye]

Ok, Cliff, I'll bite. What does all that mean?

I've never used the mSysQueries table before. What do the various values
for Attribute and Flag mean?

----
Dale

Hi Dea,

Try this query:

SELECT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id
WHERE (((MSysQueries.Name1)="T_DEGREE_population_step_1") AND
((MSysQueries.Attribute)=1) AND ((MSysQueries.Flag)=2))
ORDER BY MSysObjects.Name;

Clifford Bass

So, I'm trying to find the make-table query that produced
T_DEGREE_population_step_1

[snip]

 

[Clifford Bass]

Hi Dale,

Ooohhh, I wish you had not asked that--it reveals my ignorance about
them. MSysObject is the main system table that contains information about
the various objects in the database. MSysQueries is the system table that
contains query-specific information. I have used the tables for other kinds
of searches related to the contents of queries. It has always been figured
out by trial and error, looking at the data for various types of queries.
MSysObjects.Type for queries is 5, as far as I can tell. I probably should
have included that condition, but I think the others narrow it down to only
queries anyway. The destination table for a make-table and an append query
is stored in MSysQueries.Name1 in the rows that have an Attribute of 1. The
Flag seems to be somewhat related to the type of the query.

Sorry, wish I know more :-(.

Clifford Bass

 

[Clifford Bass]

Hi Dale (and others),

Added information. You may get some queries with names that start with
"~sq_" and/or "~TMP". The "~sq_" seem to be related to forms and reports
where you have used a table, or possibly also an SQL statement, instead of a
query as the record source. The "~TMP" queries are ones Access creates on
the fly as needed for some purpose. They go away with a compact and repair.

Clifford Bass

 

[Clifford Bass]

Hi Dea,

For a more generic search for text in your queries you can use:

PARAMETERS [Search text:] Text ( 255 );
SELECT MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2,
MSysQueries.Expression
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id
WHERE (((MSysQueries.Name1) Like "*" & [Search text:] & "*") AND
((MSysObjects.Type)=5)) OR (((MSysQueries.Name2) Like "*" & [Search text:] &
"*")) OR (((MSysQueries.Expression) Like "*" & [Search text:] & "*"))
ORDER BY MSysObjects.Name;

This will prompt you to enter something to search for it and will
display all rows where it is found.

Clifford Bass

 

[Dale Fye]

I've used mSysObjects, just have not played with mSysQueries.

 

[Clifford Bass]

Hi All,

Hmm, I see a couple of the conditions went missing. It should be:

PARAMETERS [Search text:] Text ( 255 );
SELECT MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2,
MSysQueries.Expression
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id
WHERE (((MSysQueries.Name1) Like "*" & [Search text:] & "*") AND
((MSysObjects.Type)=5)) OR (((MSysQueries.Name2) Like "*" & [Search text:] &
"*") AND ((MSysObjects.Type)=5)) OR (((MSysQueries.Expression) Like "*" &
[Search text:] & "*") AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

And here is a variation that lets you also search for text that
contains the wild cards *, #, ? and/or the opening bracket ([):

PARAMETERS [Search text:] Text ( 255 );
SELECT MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2,
MSysQueries.Expression
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id
WHERE (((MSysQueries.Name1) Like "*" & IIf(IsNull([Search
text:]),"",Replace(Replace(Replace(Replace([Search
text:],"[","[[]"),"*","[*]"),"#","[#]"),"?","[?]")) & "*") AND
((MSysObjects.Type)=5)) OR (((MSysQueries.Name2) Like "*" &
IIf(IsNull([Search text:]),"",Replace(Replace(Replace(Replace([Search
text:],"[","[[]"),"*","[*]"),"#","[#]"),"?","[?]")) & "*") AND
((MSysObjects.Type)=5)) OR (((MSysQueries.Expression) Like "*" &
IIf(IsNull([Search text:]),"",Replace(Replace(Replace(Replace([Search
text:],"[","[[]"),"*","[*]"),"#","[#]"),"?","[?]")) & "*") AND
((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

Clifford Bass

 

[James A. Fortune]

Try this comparison

If QDF.SQL like "*Into *" & TableName & "*"
AND NOT Qdf.SQL LIKE "*FROM *" & TableName & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

In:

http://groups.google.com/group/microsoft.public.access/msg/b342e061be8585a1

KenSheridan said:

"Note how each Boolean OR operation is contained in parentheses to force
it to evaluate independently of the Boolean AND operation."

That's very good advice. That statement applies even more if you're
talking about the LIKE operator.

From the A97 Help file (Operator Precedence):

"When several operations occur in an expression, each part is evaluated
and resolved in a predetermined order called operator precedence.

When expressions contain operators from more than one category,
arithmetic operators are evaluated first, comparison operators are
evaluated next, and logical operators are evaluated last. Comparison
operators all have equal precedence; that is, they are evaluated in the
left-to-right order in which they appear. Arithmetic and logical
operators are evaluated in the following order of precedence:

Arithmetic Comparison Logical
Exponentiation (^) Equality (=) Not
Negation (–) Inequality (<>) And
Multiplication and division (*, /) Less than (<) Or
Integer division (\) Greater than (>) Xor
Modulus arithmetic (Mod) Less than or equal to (<=) Eqv
Addition and subtraction (+, –)Greater than or equal to (>=) Imp
String concatenation (&) Like
Is

When multiplication and division occur together in an expression, each
operation is evaluated as it occurs from left to right. When addition
and subtraction occur together in an expression, each operation is
evaluated in order of appearance from left to right. Parentheses can be
used to override the order of precedence and force some parts of an
expression to be evaluated before others. Operations within parentheses
are always performed before those outside. Within parentheses, however,
operator precedence is maintained.

The string concatenation operator (&) is not an arithmetic operator, but
in precedence, it does follow all arithmetic operators and precede all
comparison operators. The Like operator is equal in precedence to all
comparison operators, but is actually a pattern-matching operator. The
Is operator is an object reference comparison operator. It does not
compare objects or their values; it checks only to determine if two
object references refer to the same object."

Since the LIKE operator is near the bottom of the precedence order, if
you have something similar to:

WHERE [SomeField] LIKE "*something*" AND [AnotherField] = "else"

it's very possible for SQL to try to treat that as:

WHERE [SomeField] LIKE ("*something*" AND [AnotherField] = "else")

which can get very confusing if SQL tries to convert "*something*" into
a Boolean and then tries to covert the Boolean result of the AND into a
string. I have not tested out how SQL deals with such things,
preferring to obviate the potential problem, so the potential problem
might not even exist . I always put LIKE expressions in their own
set of parentheses.

James A. Fortune
(e-mail address removed)

 

[Thomas (ADC)]

Gentlemen,

I might be a bit late but I would like to humbly point you to an add-
in that I wrote for this purpose (open source, no commercial interest,
see link below). It might be helpful for non-coders in situations like
these as it finds numerous kinds of dependencies.

Thomas Koester