Every so often I’ll find the solution to an obscure issue, and wonder “why isn’t this in the MSKB? Why has nobody written an explanation I could find in Google?” Well through this site, now I can do something about that.

If you call an Access (Jet 4.0) query via ADO/MDAC, you may get empty recordsets back if the query uses parameters. There’s a bug that Microsoft reckons applies to RDO calls, but also appears to affect ADO/MDAC 2.7 in the same way.

To fix it you need to define the parameter size to an arbitrary amount before you set the parameter. Here’s some hopefully useful (to somebody, somewhere, some day) sample code…

(This is VB6. Dim everything beforehand of course, ‘cos everybody loves early-binding. What, you want to play with fire by using late-binding and no Option Explicit? Silly you.)

Set oCommand = New ADODB.Command
oCommand.ActiveConnection = gcDatabase.Connection
oCommand.CommandText = "select * from MyQuery"

Set oParam = New ADODB.Parameter
oParam.Size = 255 'Take this out and you get an empty recordset back, but no error. Wacky.
oParam.value = "your value"
oParam.Name = "your parameter name"
oParam.Type = adYourFieldType
oCommand.Parameters.Append oParam

Set oRS = New ADODB.Recordset
oRS.Open oCommand, adOpenForwardOnly, adLockReadOnly

(and close and destroy everything when you’re done, natch. You know what VB6’s garbage collection is like.)

I know the spread of macro viruses via consumer products is a dangerous thing, and obviously Microsoft in particular have had to take action to help slow them down. But I’m not convinced the plethora of dialog boxes that now adorns every application is really the way to go.

For instance, if you open an MDB in Access 2003 that was created in Access 2000, you are likely to get no less than three separate security dialogs asking if you’re sure, if you’re really sure you want to open the file.

I’ve been using Access for some years, but I don’t know what an “unsafe expression” is. I created the MDB I’m opening, and it’s just got tables in it. No macros, no VBA modules, not even a report or query. There’s nothing unsafe in it. So I said No, don’t block the unsafe stuff you imagine is in this file. Give it all to me.

Having said no, I don’t want them blocked, it then complains that it can’t block them. Obviously it doesn’t trust me to answer sensibly, it really wants to block those imaginery unsafe items. But it can’t without sending me off to Windows Update to install Jet 4 SP 8 or later.

I had to really concentrate to work out what the Yes/No options at the bottom of the dialog are for. They’re nothing to do with blocking the alleged unsafe expressions, or installing the service pack. Nope. What it’s asking is if I still want to open the file.

Having ascertained that I don’t care about the unsafe expressions that don’t exist, and I still want to open the file… it asks me just one more time, by suggesting the bleeding obvious: “This file may not be safe if it contains code that was intended to harm your computer.” Well duh, no kidding.

The cunningly placed Cancel button on the left could easily lead one to click that by default. But finding and clicking the Open button finally really opens the file.

Now, why did I want to look at this file again?