Make table query - where does it come from

  • Thread starter Thread starter C Tate
  • Start date Start date
C

C Tate

There appears to be a table in my database (Access 2003) which is formed
through a make table query. Is there any way to find out which query/queries
made it? The dependencies don't show the information and there are hundreds
of queries to go through.
 
C said:
There appears to be a table in my database (Access 2003) which is formed
through a make table query. Is there any way to find out which query/queries
made it? The dependencies don't show the information and there are hundreds
of queries to go through.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using an .mdb file you can use VBA & DAO to go thru the
QueryDefs like the following to get the name of the table.

Set the parameter to the table's name. This subroutine will print the
name's of all the queries that refer to that table.

Sub WhichQuery(strSearchFor As String)

dim db as dao.database
dim qd as dao.querydef

set db = currentdb

for each qd in db.querydefs

If InStr(qd.SQL, strSearchFor)>0 Then
Debug.Print qd.name
End If

next qd

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRRWia4echKqOuFEgEQIBjQCfcsrKbv/Su4rESs/HXH3l+I66K0QAn1o/
FAU5qsuarad2hM5DLIBLfXD5
=PuWw
-----END PGP SIGNATURE-----
 
Very many thanks for your kind assistance. I think this information is going
to be very useful for me. However, I am not at all familiar with Visual
Basic. I am assuming I can paste this code into a module and just do a
'runcode' from a macro? I'm not sure which bit I have to change to the
table's name however (you say 'set the parameter to the table's name')? Do I
need to change anything else, like the database name?
 
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, just paste the code in a module.

To run the routine from a macro; I believe it has to be a function -
change the Sub in the first line to Function.

You can run it from the debug window (ctrl-G) like this: Type the name
of the subroutine and the name of the table in quotes. E.g.:

WhichQuery "Accounts"

It will also search for anything in the query, like a parameter you want
to change, etc.

It searches the current database so you don't have to do anything to the
database name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRRnUW4echKqOuFEgEQLr6QCg6a+gbEeU9SSkkfAq+niTHrTuVJ4AoKWW
j/t0Vomvcid4e/iPuD8heeM9
=ABSs
-----END PGP SIGNATURE-----
 
Back
Top