Here is some code that can be usefu 9

[LonnieJohnson]

Here is some code that can be useful for letting you know which queries use a certain table. Cut and past this into a module.


Option Compare Database
Option Explicit

Sub findtbls()
FindAllQueriesThatContainASpecifiedTable ("MyTableNameHere&quot
End Sub

Public Function FindAllQueriesThatContainASpecifiedTable(tblName As String)
Dim qd As QueryDef
Dim DB As Database
Dim tb As TableDef
Dim rsQueries As Recordset, RS As Recordset
Dim sqlStr As String
Dim index As Integer
Set DB = CurrentDb()
Set tb = DB.CreateTableDef("tempTblDef&quot 'create temp table
sqlStr = "SELECT QueryName FROM " & tb.name & " WHERE ((([QuerySQL]) LIKE '*" & tblName & "*'))"

tb.Fields.Append tb.CreateField("QueryName", dbText) 'add fields we need
tb.Fields.Append tb.CreateField("QuerySQL", dbMemo)
DB.TableDefs.Append tb

Set RS = tb.OpenRecordset() 'open the table!

For Each qd In DB.QueryDefs 'get sql and name of each query, ignoring
If (Left(qd.name, 1) <> &quot;~&quot Then 'system and hidden queries
RS.AddNew
RS!QueryName = qd.name
RS!QuerySQL = qd.SQL
Debug.Print RS!QueryName
RS.Update
End If
Next
RS.close

'open up a recordset based on the temp table using a SQL query
Set rsQueries = DB.OpenRecordset(sqlStr)
rsQueries.MoveLast: rsQueries.MoveFirst

'print out the resuls of our query
Debug.Print &quot;------ Queries containing table '&quot; & tblName & &quot;' -------&quot;
Debug.Print &quot;------ number of queries : &quot; & rsQueries.RecordCount
For index = 1 To rsQueries.RecordCount Step 1
Debug.Print rsQueries!QueryName
rsQueries.MoveNext
Next

rsQueries.close 'get rid of it!
DB.TableDefs.Delete tb.name 'delete temp. tabledef
Set DB = Nothing
End Function


[/blue]

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[hermanlaksko]

Thx for the tip, mayby it should be in the FAQ section as not get lost ?

Had a look at your site ...... NICE!

Not impressed with the tips section, let me know if I can help here B-)

 

[GingerR]

thanks

 

[GingerR]

love this and will use for sure.
suggestion: seems as tho if the temptbldef already exists I get an error. might want to deal with that--delete it first? also, i'd put results into a table instead. i can do that myself--just a suggestion. another--make one huge table with added field of TableName, then all queries associated with it. loop thru all. then user doesnt have to specify table name. anyhow, results in a table seems like something i'd find easier to reference than the immediate window.

anyhow, blah blah blah...i can tweak as i'd like, just had some thoughts and wanted to say thanks for a useful bit of code.

g

 

[JohnHarkins]

Kindly post your new code, Ginger, and we'll all benefit!

Thanks,

John Harkins

 

[GingerR]

here's a tweaked version of Lonnie's. i don't know how to delete an existing table before creating it new again, so my gig assumes you have existing tables tempTblDefs and tempTblQueries, i just delete records and append new records during the function.

tempTblDefs: fields QueryName (text 255), QuerySQL (MEMO)
tempTblQueries: fields TableName (text255),QueryName(text255)

tempTblQueries has final info in it
i added the bit about getting the table defs, looping thru every table. also had to account for if the table is not used in any query, just continue to next table.

thanks again lonnie.



Public Function FindAllQueriesAndAssociatedTables()
Dim qd As QueryDef
Dim DB As Database
Dim tb As TableDef
Dim rsQueries As Recordset, RS As Recordset
Dim sqlStr As String
Dim index As Integer
Dim tblName As String

Dim rsFinal As Recordset
Dim strSQLFinal As String
Dim tdf As TableDef

'clear out existing tables
CurrentDb.Execute &quot;Delete * from tempTblDef&quot;
CurrentDb.Execute &quot;Delete * from tempTblQueries&quot;

'Open tempTblDef table and store all queries and their SQL statements
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(&quot;Select * from tempTblDef&quot 'open the table!

For Each qd In DB.QueryDefs 'get sql and name of each query, ignoring
If (Left(qd.Name, 1) <> &quot;~&quot Then 'system and hidden queries
RS.AddNew
RS!QueryName = qd.Name
RS!QuerySQL = qd.SQL
RS.Update
End If
Next
RS.Close
'Open tempTblQueries and store every table name along with it's associated queries
strSQLFinal = &quot;Select * from tempTblQueries&quot;
Set rsFinal = DB.OpenRecordset(strSQLFinal)

For Each tdf In DB.TableDefs
tblName = tdf.Name
sqlStr = &quot;SELECT QueryName FROM tempTblDef WHERE ((([QuerySQL]) LIKE '*&quot; & tblName & &quot;*'))&quot;
Set rsQueries = DB.OpenRecordset(sqlStr)

'If this table is not used in any query, continue on to the next table
If rsQueries.RecordCount > 0 Then
rsQueries.MoveLast: rsQueries.MoveFirst
For index = 1 To rsQueries.RecordCount Step 1
rsFinal.AddNew
rsFinal!TableName = tblName
rsFinal!QueryName = rsQueries!QueryName
rsFinal.Update
rsQueries.MoveNext
Next
End If
Next

rsQueries.Close 'get rid of it!
rsFinal.Close
Set DB = Nothing
End Function

 

[jrbarnett]

Hi,

I would change the code declarations to specifically state DAO so the newer versions of Access won't get confused between DAO and ADO. Thus:

    Dim qd          As DAO.QueryDef
    Dim DB          As DAO.Database
    Dim tb          As DAO.TableDef
    Dim rsQueries   As DAO.Recordset, RS    As DAO.Recordset
    Dim sqlStr      As String
    Dim index       As Integer
    Dim tblName     As String
    
    Dim rsFinal     As DAO.Recordset
    Dim strSQLFinal As String
    Dim tdf         As DAO.TableDef

This my only recommended change to GingerR's code.

John

 

[GingerR]

you know, i don't get the DAO/ADO thing so thanks for the put.
i always add a reference to my db's (Acc2k) to DAO 3.6 and sail along just fine.
but i think sometimes when i provide help to others, they get wacky errors that i don't get, and it might be this kind of thing, huh? i will incorp this into my stuff from now on. thanks--g

 

[GingerR]

hey i think i just thought of a possible problem.
rs gets queries where QuerySQL like *tablename*

what if you have a table named Employee, and one named EmployeeRates
the queries will be returned for table Employee as for table EmployeeRates, even if table Employee is not used in the query because like *Employee* satisfies the criteria when table EmployeeRates is used in the query.

??

 

[LonnieJohnson]

Another gentleman tweeked it using the system tabels...


Here is a shorter version using the system tables. This works for Access97 and because it uses the system tables may be less portable to newer versions of Access. The advantage is that it doesn't require making any new (although temporary) tables...

Option Compare Database
Option Explicit

Sub findtbls()
FindAllQueriesThatContainASpecifiedTable (&quot;MyTableNameHere&quot
End Sub

Public Function FindAllQueriesThatContainASpecifiedTable(tblName As String)
Dim DB As Database
Dim rsQueries As Recordset
Dim sqlStr As String
Dim index As Integer

Set DB = CurrentDb()

sqlStr = &quot;SELECT MSysObjects.Name AS QueryName &quot; & _
&quot;FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId &quot; & _
&quot;WHERE (((MSysQueries.Name1)=&quot;&quot;&quot; & tblName & &quot;&quot;&quot AND ((MSysQueries.Attribute)=5));&quot;

Set rsQueries = DB.OpenRecordset(sqlStr)
rsQueries.MoveLast: rsQueries.MoveFirst

'print out the resuls of our query
Debug.Print &quot;------ Queries containing table '&quot; & tblName & &quot;' -------&quot;
Debug.Print &quot;------ number of queries : &quot; & rsQueries.RecordCount
For index = 1 To rsQueries.RecordCount Step 1
Debug.Print rsQueries!QueryName
rsQueries.MoveNext
Next

rsQueries.Close
Set DB = Nothing
End Function





ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[sha76]

This looks really useful, thanks to everyone who's posted their code - I can see this saving hours in untangling complex databases!

Sharon

 

[RiderJon]

This is the best thing I have seen, since dreaming of winning the lottery. Here's another *star*. Thank you Ginger R and Lonnie, and the gang.

GingerR: Just a quick note. There's a typo: The Table name is &quot;tempTblDef&quot; and not &quot;tempTblDefs&quot; .... no &quot;s&quot;

Thanks again

RiderJon
&quot;I might have created ctrl+alt+del,
But Bill made it famous&quot; - Dr. Dave

 

[younguser]

Great!!! Today I just fixed my program because I add one field in a table. It cause big trouble in many query. It took about 3 hours to find out and fix queries. Now, I can use it no trouble any more. Thank you again.

 

[RiderJon]

Hi all ....


I got the code to work on a small database. But the same code doesn't work for a big one (~500 megs). Some table name are long, could that be the problem.

It gives me syntax error in the line :
sqlStr = &quot;SELECT QueryName FROM tempTblDef WHERE ((([QuerySQL]) LIKE '*&quot; & tblName & &quot;*'))&quot;


My code is the same one as posted by GingerR. Only change was using DAO and ADO as suggested by JR Barnett.

Again the code works, for some time then gives me an error.

Any ideas?



RiderJon
&quot;I might have created ctrl+alt+del,
But Bill made it famous&quot; - Dr. Dave

 

[LonnieJohnson]

What does the error say?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[RiderJon]

Lonnie:

It says: &quot;Syntax error ..... &quot; on that line.

I can post the full version on friday as I am off work until then. But that's all the error says. And debug takes me to your code line:

Sub findtbls()
FindAllQueriesThatContainASpecifiedTable (&quot;MyTableNameHere&quot
End Sub

PS: Although I just specify a dummy variable for the &quot;MyTableNameHere&quot;. I kept it incase I want just some particular table info later on.











RiderJon
&quot;I might have created ctrl+alt+del,
But Bill made it famous&quot; - Dr. Dave

 

[LonnieJohnson]

Sure, go ahead and post it.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[RiderJon]

Lonnie:

Sorry the delay. I checked on it. The error meesage says it's a syntax error like I said before. I believe it's because of some file names having &quot;special characters&quot; like apostrophe etc.

I am out of my office, but I will check on it over the weekend or on Monday. Please reply if you come across anything.



RiderJon
&quot;I might have created ctrl+alt+del,
But Bill made it famous&quot; - Dr. Dave

 

[lgbatdw]

Ginger:
You are right about your thought (in above posting)


"hey i think i just thought of a possible problem.
rs gets queries where QuerySQL like *tablename*

what if you have a table named Employee, and one named EmployeeRates
the queries will be returned for table Employee as for table EmployeeRates, even if table Employee is not used in the query because like *Employee* satisfies the criteria when table EmployeeRates is used in the query."


I have two tables tblMasterFacility and tblMasterFacilityBillingMatrix and queryies show up for tblMasterFacility that do not use it, but do use tblMasterFacilityBillingMatrix. So this is a problem that I don't know how to get around. These are not the only two tables that are similarly named.


Linda in MN

 

[GingerR]

take out the "like" stuff and put in the exact table name?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html