help modify code

[robertfuschetto via AccessMonster.com]

I have some code. You fill in a table name on a form and press a button. It
goes through all the queries in a db and reports back those queries that
contain the table name. I want to alter the code to look for more than one
table at a time. In effect I have a table that contains the table names I
want it to look for. When I hit the button it should open this table and
start searching queries to see if the tables therein are referenced in the
queries. Here is the current code that looks for one table at a time. How
would I modify it?


Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim TempTable As TableDef
Dim TempTableName As String
Dim Indicator As String
Dim sqlstr As String
Dim X As Integer
Dim db As Database


DoCmd.SetWarnings False
Set db = CurrentDb()
With db

'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm


'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable


'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
End With

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True

End Sub

 

[Joe Black]

I have some code. You fill in a table name on a form and press a button. It
goes through all the queries in a db and reports back those queries that
contain the table name. I want to alter the code to look for more than one
table at a time. In effect I have a table that contains the table names I
want it to look for. When I hit the button it should open this table and
start searching queries to see if the tables therein are referenced in the
queries. Here is the current code that looks for one table at a time. How
would I modify it?


Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim TempTable As TableDef
Dim TempTableName As String
Dim Indicator As String
Dim sqlstr As String
Dim X As Integer
Dim db As Database


DoCmd.SetWarnings False
Set db = CurrentDb()
With db

'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm


'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable


'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
End With

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True

End Sub

Firstly, the line "With db" is pointless because within that With block you
reference "db" directly (e.g. "db.TableDefs.Append TempTable"

Secondly, you do not need to delete and recreate "tbl_TempList", all you
need to do is run a delete query:
CurrentDb.Execute "Delete From tbl_TempList;"

All you need to do to search for table names where the names are held in
another table is to set a recordset object to that table and loop through
it. The new loop will be outside your current loop beginning with "For Each
QueryNm In .QueryDefs"
Something like the aircode below:

Dim rstSearchNames As Recordset
Set rstSearchNames = CurrentDb.Recordsets("tblSearchNames")

If (Not rstSearchNames.EOF) And (Not rstSearchNames.BOF) Then
rstSearchNames.MoveFirst
While Not rstSearchNames.EOF

For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, rstSearchNames!TableName) <> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm

rstSearchNames.MoveNext
Wend
End If
rstSearchNames.Close
Set rstSearchNames = Nothing