Re: How to find queries which are not longer required in a mdb
am 13.01.2008 18:18:20 von lyle
On Jan 13, 6:31 am, "Simon" wrote:
> Dear reader,
>
> Is there a tool available to check the necessity of a Query.
>
> Queries are used in Forms and Reports. It can be that a Form or Report is
> not longer required and will be deleted. In case you are not aware to delete
> the Queries as well the Queries will be left in the mdb without any
> necessity.
>
> How can I find the Queries which are not longer required.
>
> Thanks for any help or suggestion.
>
> Kind regards,
>
> Simon
When I run the code below in Northwinds in Access 2000, 2002 or 2003,
I get these results:
*****
Category Sales for 1995
Query: Product Sales for 1995
*****
*****
Current Product List
None
*****
*****
Invoices
Form: Orders
Report: Invoice
*****
*****
Order Details Extended
Form: Orders Subform
Form: Customer Orders Subform2
Query: Sales by Category
*****
*****
Order Subtotals
None
*****
*****
Product Sales for 1995
Query: Category Sales for 1995
*****
*****
Products Above Average Price
None
*****
*****
Quarterly Orders
Form: Quarterly Orders Subform
*****
*****
Sales by Category
Form: Sales Reports Dialog
Report: Sales by Category Subreport
Query: Category Sales for 1995
*****
*****
Ten Most Expensive Products
None
*****
*****
Alphabetical List of Products
Form: Products
*****
*****
Catalog
None
*****
*****
Customers and Suppliers by City
None
*****
*****
Employee Sales by Country
Form: Sales Reports Dialog
*****
*****
Invoices Filter
Form: Orders
*****
*****
Orders Qry
Form: Orders
*****
*****
Products by Category
None
*****
*****
Quarterly Orders by Product
Form: Quarterly Orders Subform
*****
*****
Sales by Year
Form: Sales by Year Dialog
Report: Summary of Sales by Year
Report: Sales by Year Subreport
*****
*****
Sales Totals by Amount
Form: Sales Reports Dialog
*****
*****
Summary of Sales by Quarter
None
*****
*****
Summary of Sales by Year
None
*****
**************
Code
**************
Option Compare Database
Private Declare Function GetTempPath& Lib "kernel32" _
Alias "GetTempPathA" ( _
ByVal nBufferLength&, _
ByVal lpBuffer$)
Private Declare Function GetTempFileName& Lib "kernel32" _
Alias "GetTempFileNameA" ( _
ByVal lpszPath$, _
ByVal lpPrefixString$, _
ByVal wUnique&, _
ByVal lpTempFileName$)
Public Sub QueryUse()
Dim q$
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema(adSchemaViews)
With r
While Not .EOF
q = .Fields(2).Value
If Left(q, 1) <> "~" Then
Debug.Print "*****"
Debug.Print WhatObjectsAreUsingAnObject(q)
Debug.Print "*****"
End If
.MoveNext
Wend
End With
Set r = CurrentProject.Connection.OpenSchema(adSchemaProcedures)
With r
While Not .EOF
q = .Fields(2).Value
If Left(q, 1) <> "~" Then
Debug.Print "*****"
Debug.Print WhatObjectsAreUsingAnObject(q)
Debug.Print "*****"
End If
.MoveNext
Wend
End With
End Sub
Public Function WhatObjectsAreUsingAnObject$(ByVal ObjectName$)
Dim FilePath$
Dim FileName$
Dim Hits$
Dim Name$
Dim Object As AccessObject
Dim GetQueryNames As ADODB.Recordset
FilePath = String(255, vbNullChar)
GetTempPath 255, FilePath
FilePath = Left(FilePath, InStr(FilePath, vbNullChar) - 1)
FileName = String(255, vbNullChar)
GetTempFileName FilePath, "Obj", 0, FileName
FileName = Left(FileName, InStr(FileName, vbNullChar) - 1)
FileNumber = FreeFile
WhatObjectsAreUsingAnotherObject = ObjectName & vbNewLine & "None"
For Each Object In CurrentProject.AllDataAccessPages
SaveAsText acDataAccessPage, Object.Name, FileName
If CheckObject(ObjectName, FileName) _
And StrComp(ObjectName, Object.Name, vbTextCompare) <> 0
Then _
Hits = Hits & vbNewLine & "DAP: " & Object.Name
Next Object
For Each Object In CurrentProject.AllForms
SaveAsText acForm, Object.Name, FileName
If CheckObject(ObjectName, FileName) _
And StrComp(ObjectName, Object.Name, vbTextCompare) <> 0
Then _
Hits = Hits & vbNewLine & "Form: " & Object.Name
Next Object
For Each Object In CurrentProject.AllReports
SaveAsText acReport, Object.Name, FileName
If CheckObject(ObjectName, FileName) _
And StrComp(ObjectName, Object.Name, vbTextCompare) <> 0
Then _
Hits = Hits & vbNewLine & "Report: " & Object.Name
Next Object
For Each Object In CurrentProject.AllModules
SaveAsText acModule, Object.Name, FileName
If CheckObject(ObjectName, FileName) _
And StrComp(ObjectName, Object.Name, vbTextCompare) <> 0
Then _
Hits = Hits & vbNewLine & "Module: " & Object.Name
Next Object
For Each Object In CurrentProject.AllMacros
SaveAsText acMacro, Object.Name, FileName
If CheckObject(ObjectName, FileName) _
And StrComp(ObjectName, Object.Name, vbTextCompare) <> 0
Then _
Hits = Hits & vbNewLine & "Macro: " & Object.Name
Next Object
Set GetQueryNames =
CurrentProject.Connection.OpenSchema(adSchemaViews)
With GetQueryNames
Do While Not .EOF
SaveAsText acQuery, .Fields("TABLE_NAME"), FileName
If CheckObject(ObjectName, FileName) _
And StrComp(ObjectName, .Fields("TABLE_NAME"),
vbTextCompare) <> 0 Then _
Hits = Hits & vbNewLine & "Query: "
& .Fields("TABLE_NAME")
.MoveNext
Loop
End With
Kill FileName
If Len(Hits) > 0 Then
WhatObjectsAreUsingAnObject = ObjectName & Hits
Else
WhatObjectsAreUsingAnObject = ObjectName & vbNewLine & "None"
End If
'MsgBox "All Done Finding Objects Using " & ObjectName & ".", _
vbInformation, _
"FFDBA"
End Function
Private Function CheckObject( _
ByVal ObjectName$, _
ByVal FileName$) _
As Boolean
Dim Buffer$
Dim FileNumber#
Buffer = String(FileLen(FileName), vbNullChar)
FileNumber = FreeFile
Open FileName For Binary As #FileNumber
Get #FileNumber, , Buffer
Close #FileNumber
CheckObject = CBool(InStr(Buffer, ObjectName))
End Function
Re: How to find queries which are not longer required in a mdb
am 13.01.2008 23:27:26 von Phil Stanton
I have a DB wich when incorporate into your DB (A copy) will show where
tables, queries, sub forms, sub reports etc are used. If it is any help I
can let you have a copy
Phil
"Simon" wrote in message
news:4789f690$0$22220$bf4948fe@news.tele2.nl...
> Dear reader,
>
>
>
> Is there a tool available to check the necessity of a Query.
>
> Queries are used in Forms and Reports. It can be that a Form or Report is
> not longer required and will be deleted. In case you are not aware to
> delete
> the Queries as well the Queries will be left in the mdb without any
> necessity.
>
>
>
> How can I find the Queries which are not longer required.
>
> Thanks for any help or suggestion.
>
>
>
> Kind regards,
>
> Simon
>
>
Re: How to find queries which are not longer required in a mdb
am 14.01.2008 07:14:53 von Simon
Of course it will help please sent me the copy or let me know where I can
find it.
"Phil Stanton" schreef in bericht
news:13ol42emoacgia4@corp.supernews.com...
> I have a DB wich when incorporate into your DB (A copy) will show where
> tables, queries, sub forms, sub reports etc are used. If it is any help I
> can let you have a copy
>
> Phil
>
>
> "Simon" wrote in message
> news:4789f690$0$22220$bf4948fe@news.tele2.nl...
> > Dear reader,
> >
> >
> >
> > Is there a tool available to check the necessity of a Query.
> >
> > Queries are used in Forms and Reports. It can be that a Form or Report
is
> > not longer required and will be deleted. In case you are not aware to
> > delete
> > the Queries as well the Queries will be left in the mdb without any
> > necessity.
> >
> >
> >
> > How can I find the Queries which are not longer required.
> >
> > Thanks for any help or suggestion.
> >
> >
> >
> > Kind regards,
> >
> > Simon
> >
> >
>
>