Tracking Changes

[Alfie2008]

Hi everyone,

I have recently taken over running an Access database. It is quite comprehensive and almost all of it written in vba.
Is it possible to see what is effected when I make any changes. For example - if I amend a function - can I see everywhere where that function is called - even from a query. I am a little wary of making changes initially as I need to know the overall effect. Hope I've made some sort of sense - thanks

[CreganTur]

Just use Ctrl+F and search for the function's name in all of the database's modules and forms. Easiest way I know of.

[Alfie2008]

Just use Ctrl+F and search for the function's name in all of the database's modules and forms. Easiest way I know of.

thanks Randy but if the function is called from a query or several queries, I will not know. I was hoping that something may have been developed that would list all objects that use/reference the function or sub etc

Ian

[hansup]

thanks Randy but if the function is called from a query or several queries, I will not know. I was hoping that something may have been developed that would list all objects that use/reference the function or sub etc
I've seen two tools mentioned, but never tried them myself: Black Moshannon's Speed Ferret and Rick Fisher's Find & Replace. You can turn up more information with a web search.

Personally, I would use the method Randy suggested to search the project's modules. If you want to find where the function is referenced in saved queries, you can search the queries' SQL property.

Public Sub findStringInQueryDefs(ByVal pFind As String)
Dim qdf As QueryDef
Dim objRegExp As Object

Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.Global = True
objRegExp.IgnoreCase = True
objRegExp.Pattern = "\b" & pFind & "\b"

For Each qdf In CurrentDb.QueryDefs
If Left$(qdf.name, 1) <> "~" And objRegExp.test(qdf.SQL) = True Then
Debug.Print qdf.name
Debug.Print "SQL: " & qdf.SQL
Debug.Print String(20, "-")
End If
Next qdf
Set objRegExp = Nothing
Set qdf = Nothing
End Sub

Run it in the Immediate Window like so:

findStringInQueryDefs "FunctionName"

... and it will display the query name and its SQL for any query which contains the text "FunctionName". Here is an example I copied from the Immediate Window where I searched my saved queries for a function called "NameString".

findStringInQueryDefs "NameString"
qryJohnM
SQL: SELECT t.last_name, t.first_name, t.middle_initial, t.Name2Search, p.names_1
FROM [SELECT last_name, first_name, middle_initial, NameString(last_name, first_name, middle_initial) AS Name2Search
FROM temp_table]. AS t, print_ready AS p
WHERE (((p.names_1) Like "*" & [t].[Name2Search] & "*"));

--------------------

Finally remember you can make a copy of your database and explore to your heart's content without fear of damaging the original. Don't let this task intimidate you!

[Alfie2008]

Excellent! Thanks vey much. I am trying it now