Microsoft Access Search and Replace

Added on: Tuesday 26th January 2010

Although Microsoft Access has a search and replace feature it only works on data within a form or a table. There is also a Find facility within code modules but what if you are looking for something within a form or query design.

Following the change in the VAT tax rate in the UK on January 1st, I was asked to update an access database to reflect this change.

Unfortunately, this wasn't as easy as I thought it would be because the database had obviously had bits bolted onto it on numerous occasions - possibly by different developers.

So there wasn't a single VAT rate stored in a table somewhere - sometimes the rate was hardcoded into the code behind forms or modules, sometimes in the query design and sometimes in a calculated field on a form.

The code was easy enough to update but the database contained hundreds of forms and even more queries (none of which had a name that gave any indication of what they did) so I was faced with opening each one up and going into design view to check for a hardcoded VAT rate.

At this stage I wondered whether I could make use of the little known SaveAsText feature of Microsoft Access VBA.

SaveAsText does exactly what it says - it writes the query or form design out to a text file. Some of the output is fairly unintelligable but I figured it would be easy enough to spot the VAT rate calculation amongst the text.

After a few minutes here is what I came up with:

Function WriteObjects(sFolder As String)
Dim qdf As QueryDef
Dim ctr As Container
Dim intForms As Integer
Dim db As Database
Set db = CurrentDb()
Set ctr = db.Containers("Forms")
For intForms = 0 To ctr.Documents.Count - 1
SaveAsText acForm, ctr.Documents(intForms).Name, sFolder & ctr.Documents(intForms).Name & ".txt"
Next intForms
For Each qdf In db.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
SaveAsText acQuery, qdf.Name, sFolder & qdf.Name & ".txt"
End If
Next
End Function

This uses the Microsoft DAO library instead of Active X Objects but you could adapt the code to use the latter.

For this to work in a project you would have to first add a Reference to the DAO Object library.

Then just call the function with the folder you want to save the text files in as a parameter. You could call it from the Immediate window or else from a button on a form.

The function then loops through all the forms and queries in the database and outputs each one to a text file with the same name as the object.

Notice the line: If Left(qdf.Name, 1) <> "~" Then in the code. This just makes sure that only saved queries are output - otherwise you'll also get all the queries behind combo and list boxes.

Once I'd got my folder of text files I simply used a text editor (PSPad) to search the entire folder for the references I was looking for.

Having examined the search results I could go back to the database itself and make the relevant changes.

Permalink