Thursday, January 3, 2013

"The width of a Unicode text column must be an even number of bytes."

Just received the above error message in Access 2010 when clicking on a checkbox. There's a description of the error here,  which conveniently says

If this error message appears during normal run time, it is probable that the database has become corrupted and needs to be compacted or repaired.
Yup. It's a corruption issue. But compacting and repairing the back-end database did no good. The corruption was apparently in the front-end database file. After I compacted and repaired the front-end, the problem went away.

Wednesday, January 2, 2013

The Amazing Eval Function

The Eval function in VBA allows you to execute a string as if it were a line of code. There are some examples in Access VBA help that are marginally useful, but what I like to use Eval for is executing optional functions. Normally, if you call a function that doesn't exist, you'll get a compile error and your code won't run. But the Eval function is one place where you can call a function that may or may not exist, and trap the error condition that occurs when the function does not exist. Here's an example:

Dim strRtn As String
Dim strFunctionNameAndParms As String

strFunctionNameAndParms = "Date()"    '<== WE'RE GOING TO CALL THIS FUNCTION

On Error Resume Next
strRtn = Eval(strFunctionNameAndParms)
If Err <> 0 Then
    Select Case Err
    Case 2425, 2426
        '* The function is not found
        MsgBox "The function '" & strFunctionNameAndParms & "' was not found."
    Case Else
        '* There was an error calling the function
        MsgBox "Error when calling '" & strFunctionNameAndParms & "': " & Err.Number & " - " & Err.Description
    End Select
    MsgBox "Return value from '" & strFunctionNameAndParms & "' is: " & vbCrLf & strRtn
End If
On Error GoTo 0

Now, if the function above ("Date()", in this instance) doesn't exist, there's no compile error, the code keeps right on executing.

You could use this if you, for instance, wanted to optionally include an "Accounts Receivable" module in your   accounting application. The main menu item for AR could use Eval to call the "StartAR()" function that lives in a "basAccountsReceivable" module that you can optionally include in your database. If the module is there, then the function runs and Accounts Receivable opens. If the module is not there, then you could have a message appear... something like "The Accounts Receivable module has not been included in this application build. To purchase this additional module please contact ..."