The land of 10,000 queries

[Sully38]

I have just been assigned a DB that contains at least 30 tables and countless queries that trigger queries that trigger appends,deletes,and create table queries. There is no documentation to be found anywhere and the programmer(and I use the title loosely) was let go for being a hack. Is there a way, besides going through query by query, that I can see where, what, and when a table is created.

My basic dielemma to start with is that one of the tables has stopped being loaded with new data and I got no clue where or when it gets created or updated.

Much thanks as always to any help that can be passed on

 

[Dugantrain]

Well, this is a cheap hack, but it'll get you started.
This will write the SQL of each query in the Debugger window. You can then copy/paste the text into Excel, Word, or whatever you want and then do Searches on your table's name to see where it crops up. As far as something really sophisticated to show Object dependencies, I believe that Access 2003 includes something like that, but I haven't seen it first hand.

Public Function fGetSQL()
Dim db As DAO.Database
Dim qrys As DAO.QueryDefs
Dim qry As DAO.QueryDef
Set db = CurrentDb
Set qrys = db.QueryDefs
For Each qry In qrys
    Debug.Print qry.SQL
Next
Set qrys = Nothing
Set db = Nothing
End Function

 

[mdmilner]

Write a VBA routine that walks through the Query Objects and prints all their names & SQL to a file. Should be simple then to open it in WORD and do a 'search' for the SQL syntax "INTO tablname" to find queries that update that correct table.

Triggering queries sounds like Macros or VBA is in use?

Obviously you can use other options to save the output, just watch out for field limits in tables (255 characters) within Access.

Function showSQL()
Dim dbs As DAO.Database
Dim qry As DAO.QueryDef
Dim strVal As String
Const cstrPath = "C:\filename.txt"

Set dbs = CurrentDb()

Open cstrPath For Append As #1
For Each qry In dbs.QueryDefs
  strVal = qry.Name & vbCrLf & qry.SQL
  If Len(strVal) > 0 Then
    Print #1, strVal
  End If
Next qry
Close #1

Set qdf = Nothing
Set dbs = Nothing
End Function

Ack - after edit saw Dugan's post. Funny how we basically had exactly the same idea.

Mike