find the query that made a table.

[JH]

Is thre a way to find out what make table query created a table? For example,
I have a table that I created a month ago but can not remember what make
table query I used to create it. I have access 2003.
Thanks for your help.

 

[KARL DEWEY]

Start on the Query tab, go to TOOLS - Analyze - Documentor, select all,
select SQL options, click Ok, click Ok, then click on icon that is a blue "W"
to create a Rich Text
File (.RTF) that opens in Word.
You can also take the data to Excel instead of Word.
Search for the table name.

 

[JH]

Thanks Karl. I have hundreds of queries to look through. Is there a way to
look in the object tables for a specific table and find out what query
created that table?
Thanks for your help.

 

[KARL DEWEY]

Did you try searching for the table name in the query SQL as I suggested?

 

[JH]

Yes I did and I did see the name of the table that was created by a query ,
however I have many queries and to go through them would take awhile. I was
hoping that if I looked in the table object for a specific table that I
would be able to see the query that created it. Is that a possibility or do
I need to just plow throuh all the queries until I find the one of interest?
Thanks.

 

[KARL DEWEY]

You will have to plow throuh all the queries until you find a make table
query for that table.
It is possible it was made on the fly, query not saved or a select query
used to make the table and the changes not saved.

 

[JH]

Thanks Karl.
--
JH

You will have to plow throuh all the queries until you find a make table
query for that table.
It is possible it was made on the fly, query not saved or a select query
used to make the table and the changes not saved.

 

[John Spencer]

Here is a simple sub that should find the query if it exists

Sub FindInSQL()
Dim qdf As QueryDef
Dim dbAny As DAO.Database
Dim strA As String
Dim strB As String

Set dbAny = CurrentDb()
strA = "Faq" 'Name of table
strB = "INTO " 'Make query has word into in it

'Loop through all the queries and look for ones that
'have the table name and the word INTO in it.

For Each qdf In dbAny.QueryDefs
With qdf
If InStr(1, .SQL, strA) > 0 _
And InStr(1, .SQL, strB) > 0 Then
Debug.Print .Name

End If
End With
Next qdf

End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jo1786]

Thank you John that has worked a treat! Extremely helpful!