Finding all queries which use a table

Hi,

Does anyone know of a tool that can scan all queries in a database and find 
if a certain table is used?  I have a table called tblCustomerRollup which is 
old and outdated.  I want to see which of the 500 queries in my database use 
this table without opeing every single one of them?

Thanks,
-- 
Chuck W
0
Utf
3/17/2008 7:27:01 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
823 Views

Similar Articles

[PageSpeed] 11

Chuck

Sounds like a variation on Search/Replace.  Try searching online for 
"Database Documenter" as a starting point.

A couple of the commercial tools I've used include FMS, Inc.'s Total Access 
Analyzer and Black Moshannon's Speed Ferret.  There are a lot of free tools, 
too.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"ChuckW" <haji@hotmail.com> wrote in message 
news:DD66A235-AF0C-4B47-BF62-9CB348744284@microsoft.com...
> Hi,
>
> Does anyone know of a tool that can scan all queries in a database and 
> find
> if a certain table is used?  I have a table called tblCustomerRollup which 
> is
> old and outdated.  I want to see which of the 500 queries in my database 
> use
> this table without opeing every single one of them?
>
> Thanks,
> -- 
> Chuck W 


0
Jeff
3/17/2008 7:31:20 PM
If you are using access 2003, then just right click on the table, and select 
object dependences.

It will show you all forms, reports, and queries that are based on the 
table.....

this option often forces to you enable track-name auto correct. I would 
suggest turning that feature off once you are done viewing dependences....


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
3/17/2008 7:39:38 PM
Here is a routine I developed to search for strings in queries.
Copy and paste this code into a VBA module.
For variable strSearch1, enter the string to serach for.
Then run the routine and the results will be in the Immediate window.


Public Sub Find_String_In_Queries()
    '
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim strSearch1 As String
    Dim strResults As String
    Dim numFound As Long
    
    strSearch1 = "..."  'enter table name or field name or any string to 
search for
    
    Set db = CurrentDb
    
    strResults = ""
    numFound = 0
    For Each qdf In db.QueryDefs
        strSQL = qdf.SQL
        
        If InStr(1, strSQL, strSearch1) > 0 Then
            numFound = numFound + 1
            strResults = strResults & vbNewLine & "     " & numFound & ") " 
& qdf.Name
        End If
    Next
    
End Sub





"ChuckW" wrote:

> Hi,
> 
> Does anyone know of a tool that can scan all queries in a database and find 
> if a certain table is used?  I have a table called tblCustomerRollup which is 
> old and outdated.  I want to see which of the 500 queries in my database use 
> this table without opeing every single one of them?
> 
> Thanks,
> -- 
> Chuck W
0
Utf
3/17/2008 9:51:01 PM
Sorry, I missed a line while I copied and pasted.

Add: Debug.Print strResults

below the Next and above End Sub

"JP" wrote:

> Here is a routine I developed to search for strings in queries.
> Copy and paste this code into a VBA module.
> For variable strSearch1, enter the string to serach for.
> Then run the routine and the results will be in the Immediate window.
> 
> 
> Public Sub Find_String_In_Queries()
>     '
>     Dim db As Database
>     Dim qdf As QueryDef
>     Dim strSQL As String
>     Dim strSearch1 As String
>     Dim strResults As String
>     Dim numFound As Long
>     
>     strSearch1 = "..."  'enter table name or field name or any string to 
> search for
>     
>     Set db = CurrentDb
>     
>     strResults = ""
>     numFound = 0
>     For Each qdf In db.QueryDefs
>         strSQL = qdf.SQL
>         
>         If InStr(1, strSQL, strSearch1) > 0 Then
>             numFound = numFound + 1
>             strResults = strResults & vbNewLine & "     " & numFound & ") " 
> & qdf.Name
>         End If
>     Next
>     
> End Sub
> 
> 
> 
> 
> 
> "ChuckW" wrote:
> 
> > Hi,
> > 
> > Does anyone know of a tool that can scan all queries in a database and find 
> > if a certain table is used?  I have a table called tblCustomerRollup which is 
> > old and outdated.  I want to see which of the 500 queries in my database use 
> > this table without opeing every single one of them?
> > 
> > Thanks,
> > -- 
> > Chuck W
0
Utf
3/17/2008 10:00:02 PM
Hi JP,
Thanks for your response. I have a database (Access 2002) littered with 
queries that I want to get to get rid off, but I find that difficult because 
I sometimes cannot tell if the query is needed for a form or another query. 
Can you help me?
-- 
Glint


"JP" wrote:

> Here is a routine I developed to search for strings in queries.
> Copy and paste this code into a VBA module.
> For variable strSearch1, enter the string to serach for.
> Then run the routine and the results will be in the Immediate window.
> 
> 
> Public Sub Find_String_In_Queries()
>     '
>     Dim db As Database
>     Dim qdf As QueryDef
>     Dim strSQL As String
>     Dim strSearch1 As String
>     Dim strResults As String
>     Dim numFound As Long
>     
>     strSearch1 = "..."  'enter table name or field name or any string to 
> search for
>     
>     Set db = CurrentDb
>     
>     strResults = ""
>     numFound = 0
>     For Each qdf In db.QueryDefs
>         strSQL = qdf.SQL
>         
>         If InStr(1, strSQL, strSearch1) > 0 Then
>             numFound = numFound + 1
>             strResults = strResults & vbNewLine & "     " & numFound & ") " 
> & qdf.Name
>         End If
>     Next
>     
> End Sub
> 
> 
> 
> 
> 
> "ChuckW" wrote:
> 
> > Hi,
> > 
> > Does anyone know of a tool that can scan all queries in a database and find 
> > if a certain table is used?  I have a table called tblCustomerRollup which is 
> > old and outdated.  I want to see which of the 500 queries in my database use 
> > this table without opeing every single one of them?
> > 
> > Thanks,
> > -- 
> > Chuck W
0
Utf
3/18/2008 1:33:02 PM
One approach is to rename a query (I prefix mine with "zzz" - this makes 
them sort to to bottom but still remain recognizable), then run your 
application.  If it doesn't break, you didn't need it!

Of course, this depends on your ability to exercise EVERY aspect of your 
application!

Good luck.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Glint" <Glint@discussions.microsoft.com> wrote in message 
news:192BEBF9-3A8E-404F-A445-713F046835CE@microsoft.com...
> Hi JP,
> Thanks for your response. I have a database (Access 2002) littered with
> queries that I want to get to get rid off, but I find that difficult 
> because
> I sometimes cannot tell if the query is needed for a form or another 
> query.
> Can you help me?
> -- 
> Glint
>
>
> "JP" wrote:
>
>> Here is a routine I developed to search for strings in queries.
>> Copy and paste this code into a VBA module.
>> For variable strSearch1, enter the string to serach for.
>> Then run the routine and the results will be in the Immediate window.
>>
>>
>> Public Sub Find_String_In_Queries()
>>     '
>>     Dim db As Database
>>     Dim qdf As QueryDef
>>     Dim strSQL As String
>>     Dim strSearch1 As String
>>     Dim strResults As String
>>     Dim numFound As Long
>>
>>     strSearch1 = "..."  'enter table name or field name or any string to
>> search for
>>
>>     Set db = CurrentDb
>>
>>     strResults = ""
>>     numFound = 0
>>     For Each qdf In db.QueryDefs
>>         strSQL = qdf.SQL
>>
>>         If InStr(1, strSQL, strSearch1) > 0 Then
>>             numFound = numFound + 1
>>             strResults = strResults & vbNewLine & "     " & numFound & ") 
>> "
>> & qdf.Name
>>         End If
>>     Next
>>
>> End Sub
>>
>>
>>
>>
>>
>> "ChuckW" wrote:
>>
>> > Hi,
>> >
>> > Does anyone know of a tool that can scan all queries in a database and 
>> > find
>> > if a certain table is used?  I have a table called tblCustomerRollup 
>> > which is
>> > old and outdated.  I want to see which of the 500 queries in my 
>> > database use
>> > this table without opeing every single one of them?
>> >
>> > Thanks,
>> > -- 
>> > Chuck W 


0
Jeff
3/18/2008 2:04:05 PM
Thanks Jeff.
As you rightly pointed out, it can be disaster if one overlooks any aspect 
before deleting. If the application is large, another approach may be needed.
-- 
Glint


"Jeff Boyce" wrote:

> One approach is to rename a query (I prefix mine with "zzz" - this makes 
> them sort to to bottom but still remain recognizable), then run your 
> application.  If it doesn't break, you didn't need it!
> 
> Of course, this depends on your ability to exercise EVERY aspect of your 
> application!
> 
> Good luck.
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> 
> "Glint" <Glint@discussions.microsoft.com> wrote in message 
> news:192BEBF9-3A8E-404F-A445-713F046835CE@microsoft.com...
> > Hi JP,
> > Thanks for your response. I have a database (Access 2002) littered with
> > queries that I want to get to get rid off, but I find that difficult 
> > because
> > I sometimes cannot tell if the query is needed for a form or another 
> > query.
> > Can you help me?
> > -- 
> > Glint
> >
> >
> > "JP" wrote:
> >
> >> Here is a routine I developed to search for strings in queries.
> >> Copy and paste this code into a VBA module.
> >> For variable strSearch1, enter the string to serach for.
> >> Then run the routine and the results will be in the Immediate window.
> >>
> >>
> >> Public Sub Find_String_In_Queries()
> >>     '
> >>     Dim db As Database
> >>     Dim qdf As QueryDef
> >>     Dim strSQL As String
> >>     Dim strSearch1 As String
> >>     Dim strResults As String
> >>     Dim numFound As Long
> >>
> >>     strSearch1 = "..."  'enter table name or field name or any string to
> >> search for
> >>
> >>     Set db = CurrentDb
> >>
> >>     strResults = ""
> >>     numFound = 0
> >>     For Each qdf In db.QueryDefs
> >>         strSQL = qdf.SQL
> >>
> >>         If InStr(1, strSQL, strSearch1) > 0 Then
> >>             numFound = numFound + 1
> >>             strResults = strResults & vbNewLine & "     " & numFound & ") 
> >> "
> >> & qdf.Name
> >>         End If
> >>     Next
> >>
> >> End Sub
> >>
> >>
> >>
> >>
> >>
> >> "ChuckW" wrote:
> >>
> >> > Hi,
> >> >
> >> > Does anyone know of a tool that can scan all queries in a database and 
> >> > find
> >> > if a certain table is used?  I have a table called tblCustomerRollup 
> >> > which is
> >> > old and outdated.  I want to see which of the 500 queries in my 
> >> > database use
> >> > this table without opeing every single one of them?
> >> >
> >> > Thanks,
> >> > -- 
> >> > Chuck W 
> 
> 
> 
0
Utf
3/18/2008 5:07:01 PM
Reply:

Similar Artilces:

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

find instance of IE for server socket in activex component
Hi I have an activex component that has a server socket (using casyncsocket class) ..The problem is that when I have multiple instances of the IE open only the first instance (created first) recieves the messages the rest do not recieve the message. Is there a way to solve this problem .(i want the server send the reply to that instance of ie that sent the message) .Do I need to pass something like a pointer of hte javascript object .If so how Thanks When you say "server socket", that suggests a socket that does a Listen followed by an Accept. Otherwise it is a client socke...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

basic pivot table group
I have looked at some basic Pivot Table tutorials, but I have a very basic question not answered by their examples. . . . I have data in a spreadsheet formatted like this, each row representing an event: Timestamp Process Message ID 11:23:45:123 processA message123 11:23:45:124 processB message123 11:23:45:136 processC message123 11:23:46:123 processA message456 11:23:47:123 processB message456 11:23:47:678 processC message456 Can a pivot table help me reformat the data like below? ...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...

Query involving Strings : How To Return Matching Data From Both Ta
Good afternoon, I have tried my best on this but am unable to figure it out. --------------------------------------------------------------------- Scenario: Table A contains only one column titled [District]. Let's assume that there are three rows here: 1) Bay Area Rapid Transit 2) San Diego Zoo 3) San Mateo Table B contains other data with these fields: [GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees]. Let's assume that there are three rows here: 1) West Coast | CA | Bay Area Rapid Transit , 154, 205 2) CA | Southern | Bay Area Rapid Transit , 105, 206 3) Southw...