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.)