bobpeers -> blog

I recently developed an HTA Application written in Javascript that used ADO to retrieve and update data in an MS Access database.

One of the calls tried to open a stored query but failed with the error ‘Unkown function’. It turns out that the query contained the Nz VBA function to convert null values into some other, zero in my case.

This works fine when run from within MS Access but when called externally would fail due to the JET 4.0 database engines sandbox mode.

I could have changed the registry key to allow the function but that would only work on my computer so as a fix I replaced all the references to Nz with IIf statements, not an elegant solution but it works none the less.

Sometimes for testing it’s useful to be able to insert random numbers into a table field. This is an easy way to do this in MS Access. First write a custom function that looks like this.

Function random(ID as Integer,min as Integer,max as Integer) As Integer
random = Int((max - min + 1) * Rnd + min)
End Function

The max and min are the lowest and highest random numbers you wish to generate, ID is a field from the table (it can be any field) you wish to insert the random number into.

Of course if your field is not an integer then you will have to change the ID as Integer part to ID as String for example.

The field is required so the function is called on every row, otherwise Access will just run the function once and then update every row with the same random number.
To call the function from SQL use this.

Update my_table set random_field=random(ID, 1, 10)

In this case I’ve used an Update statement to insert a number between 1 and 10 into the field called random_field but it works equally for for Insert statements.