MS Access: Error 6 – Overflow

2Aug/190

MS Access: Error 6 – Overflow

A quick break between posts of the recent trip to Japan: I was distracted at work by a user who was experiencing a bug in an Access Database. Now, my team has never built access databases, but we're still the first-point-of-call for technical support on internally developed applications; even if not developed by us, nor in this decade!

We'd also just recently migrated a SQL server from one Windows VM to another, so there was a high chance that a user had been orphaned in the process... either way, I dug in.

First step: Reproduce locally!

I had the user screen-share over Skype so I could see what was going on. There was a very quick path to reproduction, so I took a copy of the database and brought it over to my machine. In no time I had the following:

Time to find the code?

Unlocking an Access DB

To get into the backend code, you need to unlock all the menus. Hit the File menu and then choose Privacy Options.

From here... make sure the following items are checked. Allow Special Keys is required for breakpoints to trigger in the VBA script!

Close the database and open it again.

Hacking the code

You'll now have the navigation pain on the left-hand side. Somewhere down the bottom you should have one or more modules containing the code throwing errors.

Double-clicking Module1 presenting me with the following... of course, like any good organisation, we had the passwords well-documented!

Once in... breakpoints were set and code was tested. The breakpoints didn't initially trigger, as I hadn't set the Allow Special Keys option. The name really doesn't make sense, but it's required if you want to debug! Once going though, I found that the code wouldn't hit another breakpoint inside the following function...

Public Sub RetrieveTestNamesFromSpecs(iBulkID As Integer)
        Dim RCountDB As DAO.Database
        Set RCountDB = CurrentDb
        ...
        ...
End Sub

Nothing really special! But the Overflow error was happening before the function started. A breakpoint on the 'Set' line would never get hit. I looked at the line before the function call and didn't see anything incriminating. I then checked the value of the variable being passed in as iBulkdID. It was 32790. That's a pretty ominous number for anyone who understands bytes... or variable sizes. It turns out that an Access Database Integer only supports the value range of -32768 to 32768. Our ID had surpassed this and was therefore not 'fitting in' to the variable.

Changing this to a Long fixed the issue! I handed the DB back to the user and dusted my hands.

More Casting!

Before long I had the DB back on my desk as there was a new Overflow Error. I quickly dug into the code and found that the error was happening when executing the following SQL.

        strSQL = "SELECT * FROM [TblName] where [CName] = " & Me.Variable & " Order By CInt(CName) Asc"
        Set rs = RCountDB.OpenRecordset(strSQL)

Anyone playing at home will see the error straight-away, but I wasn't used to Access SQL syntax. Long-story-short, that CInt is trying to cast the value as an Integer, and we already know that it doesn't fit! A quick conversion to CLng fixed this error as well!

I then scoured the rest of the code for crappy Integer references...