VBA to analyze Access Macros

[mikethewizz]

All,

I am attempting to write a utility to analyze a specified Access database to determine what objects are used and what is unused. For example. Test every table, for use in any query SQL, form, or VBA code in modules, etc...

Once completed, it will produce a list of all objects that are not used somewhere in the dB.

This will allow me to clean up several old process dBs. With out having to manually review each part of the dB.

Well, enough back ground. Here is my problem.

I want to analyze the built in MS Access Macros to see what Queries, Tables, VBA Code.... is called by each macro.

If I can access the steps in the macro via code I could pass a simple text string and validate whether or not the object is used in any macro.

Here is a sample of my code for checking for a table in all queries.

In the function below I pass the object name, and object type for reporting.
The code will capture the queries, where and object is used, or will capture the object as not being used after testing all queries.

--BEGIN CODE--
Function TestForTblOrQryInSQL(strTblQrySearch As String, strObjType As String) As String

Dim qry1 As QueryDef
Dim blnTblFoundInAnyQry As Boolean
Dim blnTblFoundInCurrQry As Boolean
Dim intQryLoop As Integer
Dim rst2 As DAO.Recordset

blnTblFoundInAnyQry = False
For Each qry1 In CurrentDb.QueryDefs
blnTblFoundInCurrQry = False
If InStr(1, qry1.SQL, strTblQrySearch) <> 0 Then
intQryLoop = 1
Do Until intQryLoop > 6
Select Case intQryLoop
Case 1
If InStr(1, qry1.SQL, " " & strTblQrySearch & " ") <> 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 2
If InStr(1, qry1.SQL, " " & strTblQrySearch & Chr(13)) <> 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 3
If InStr(1, qry1.SQL, " " & strTblQrySearch & ";") <> 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 4
If InStr(1, qry1.SQL, " [" & strTblQrySearch & "] ") <> 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 5
If InStr(1, qry1.SQL, " [" & strTblQrySearch & "]" & Chr(13)) <> 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 6
If InStr(1, qry1.SQL, " [" & strTblQrySearch & "];") <> 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
End Select
If blnTblFoundInCurrQry = True Then
intQryLoop = 7
Else
intQryLoop = intQryLoop + 1
End If
Loop
If blnTblFoundInCurrQry = True Then
'Record Table and Qry Name
Set rst2 = CurrentDb.OpenRecordset("db_Clean_Objs_Uses")
rst2.AddNew
rst2!ObjName = strTblQrySearch
rst2!ObjType = strObjType
rst2!ObjTypeUsed = "Qry"
rst2!ObjNameUsed = qry1.Name
rst2.Update
Set rst2 = Nothing
blnTblFoundInAnyQry = True
End If
End If
Next qry1
If blnTblFoundInAnyQry = False Then
Set rst2 = CurrentDb.OpenRecordset("db_Clean_Objs_No_Uses")
rst2.AddNew
rst2!ObjName = strTblQrySearch
rst2!ObjType = strObjType
rst2.Update
Set rst2 = Nothing
End If

End Function

--END CODE--

I am using MS Access 2002 (XP)

I hope this make sense.

Thanks in advance for any input.

 

[OlcayM]

the link http://www.access-programmers.co.uk/forums/showthread.php?t=99179 will help you export your macros as text. may be you will impoert them to a note field and make your search... well long run, but at least works if there is no better way...
olcaym

 

[mikethewizz]

Any Other Ideas?

Thanks for the Input OlcayM, I did not think of that. I can certainly make that work. Although it is a little more work than I anticipated.

Does anyone else have any other ideas?

Thanks in advance.

 

[The_Doc_Man]

I've attacked the same problem myself. Darned storage format for macros is totally not easy to decode. Converting first and analyzing as though it were VBA code might be the best way to do this short of illegally reverse-engineering something that your EULA says you shouldn't touch.