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
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.

PHP and Ajax with special characters

Added on: Thursday 14th January 2010

Here's a quick tip when using Ajax and PHP to output HTML to a browser. If the code contains special characters such as currency symbols you may get unexpected results.

I was recently working on an ajax script to update our CMS shopping cart and noticed some strange behaviour.

The html for the cart is stored as an editable template to allow it to be styled for different sites. The currency symbols are written into the template.

When the page was first loaded the PHP script processed the template and displayed everything just fine. However, when the cart was updated via the ajax script the currency symbols were replaced in Firefox by the FF FD code for a missing character in a font.

I thought at first that by using the javascript escape function to encode the characters or by using html entity encoding in PHP it would solve the problem. Unfortunately, both of these didn't work.

A quick Google search and I found this article on Roshan's Blog.

The solution is to set the character set in the script that outputs the html to the ajax function using the PHP header function (example below).

header("Content-Type: text/html; charset=iso-8859-1\n");

Winter Wonderland

Added on: Tuesday 12th January 2010

I thought I'd put up some images of our part of Dartmoor taken during the recent cold weather.

It is not often that the reservoir at Fernworthy freezes over. It has only happened twice in the 15 years we have been here.

Click the images for larger versions and a gallery view.

Sunset over Fernworthy

Sunset over Fernworthy

Kestor Rock

Kestor Rock

Fernworthy Reservoir - frozen over

Fernworthy Reservoir - frozen over

Another view of the frozen reservoir

Another view of the frozen reservoir

Another view of Fernworthy

Another view of Fernworthy

Moorland Stream

Moorland Stream