bobpeers -> blog

On my work PC I have some batch jobs set up that run from a network resource. Because of this I have to set up the job with my domain user name and password.

This worked fine until I changed my network password yesterday, suddenly I was getting ‘Access Denied’ messages when I tried to save the jobs.

After a long talk with IT it turns out that my new password was the problem, I was trying to use ‘iosteam::in’ as my password but it seems that the double colon is not accepted or recognised by Windows Scheduled tasks so if you get the same problem just change your password and all should be OK.

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.

I was writing some VBA code in MS Access today and discovered some strange behaviour when inserting values into a table field when the data type of the field is set to byte (Byte variables are stored as single, unsigned, 8-bit (1-byte) numbers ranging in value from 0-255).

To recreate the problem simply create a new table with one field of type byte, this can be done by running the following SQL statement:

create table test (id byte)

Next run the following code:

insert into test values (1000)

If you look at the value actually inserted into the table it shows the value 232. There is no warning of invalid data type or out of range warnings, it just goes ahead and inserts the result of the VBA Mod function on 1000 mod 256. The Mod function is defined as ‘Divides two numbers and returns only the remainder’. If you try it with the value 513 it will insert the value 1.

It can be of no coincidence that a VBA byte can have a maximum value of 255. The most worrying thing here is that it converts my integer to a byte result and inserts the value without any warning. If you were trapping errors at this point to trying to catch someone inserting an integer or long value into the field then you’ll never know an error has occurred as no error is thrown.

Even more strangely in VBA code if you open a recordset on the table and add an integer using AddNew method then an error IS thrown, but if you execute the equivalent SQL statement using a command object NO error is thrown. The AddNew behaviour is shown below.

Dim rs As New ADODB.recordset
Dim cn As CurrentProject.Connection
With rs
	.Open "select id from test", cn, adOpenForwardOnly
	.AddNew
	.Fields("id")=1000	'throws an error
	.Update
	.Close
End With

The next code using the command object does not throw an error.

Dim cmd As New ADODB.Command
Dim cn As CurrentProject.Connection
With cmd
	.ActiveConnection = cn
	.CommandText="insert into test values (1000)"
	.Execute      'no error
End With

It seems to me that running an SQL statement from either a query window or through the ADODB command object uses some different method whereas the AddNew method bypasses this thereby raising the correct error.

Of course the lesson here is to check your data before trying to insert it into the database rather than catching errors caused by invalid data.