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.