Error While Creating a Recordset from a QueryDef with Parameters

[Sedrick]

I have successfully used this type of code to create permanent queries that are much faster to access from vba code. (It seems to be much faster than using normal DAO RecordSet code with queries or tables).

It also lets you use parameters.

I first define a query in the normal query design mode and save it. Then I add PARAMETERS to the top of the SQL.

I can then access it as a QueryDef object from the code and create a recordset from it.

I am using Access 2003

My problem is that when I perform a change in both the code and in the SQL PARAMETERS section to remove a parameter, it gives the error

"Too Few Parameters. Expected 3" for example.


SQL for the query:

PARAMETERS lngTeamID Long, lngShiftID Long, strOther Text ( 255 );
SELECT DISTINCTROW Log.ID, Log.ID2, Log.[Other]
 FROM Log

WHERE Log.ID=[lngID]) AND Log.ID2=[lngID2] AND Log.[Other]=[strOther]

I created a permanent query that appears in the list of queries

Code in the module utilizing the QueryDef:

Sub Test1 (ID as Strong, ID2 As String)

Dim qdf As QueryDef, prmID As Parameter, prmID2 As Parameter, prmOther As Parameter

' Use pre-defined (compiled) QueryDefs for better performance than OpenRecordSet(strSQL)
Dim rst As Recordset, strOther_text As String
    Set qdf = CurrentDb.QueryDefs("qry_def_Log")
    
    Set prmID = qdf.Parameters!lngID
    Set prmID2 = qdf.Parameters!lngID2
    Set prmOTHER = qdf.Parameters("strOTHER")     
    prmID = ID ' From function parameters
    prmID2 = ID2
    prmOTHER = "1"
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    
    If rst.RecordCount > 0 Then
        rst.MoveFirst 
	
	Other statements…

    End If
    
Exit Sub

It runs when I first define it and then when I go back, for example and remove parameter and code for strOther, prmOTHER it gives me the error I mentioned.

Seems like the only way I can get rid of it is to reconstruct the query again from scratch, ending up with the same code that gave me the error.

Is it compiling it interally and needs to be removed somehow and then re-defined?

[TheSmileyCoder]

You don't really show how you remove it. Depending on the method I believe it might be necessary with a refresh of sorts. This is not based on own experience, merely on something I recall having read.

[zmbd]

It runs when I first define it and then when I go back, for example and remove parameter and code for strOther, prmOTHER it gives me the error I mentioned.

First... I don't quite follow you here, I think I understand; however, just to be sure... could you give an example of what are you doing?

Second... (and this may change depending on your answer to the first question): Have you closed the record set that you opened on line 17 and released the call before trying to change the parameters.

qdf.close
Set rst = Nothing

Once you set the parameters with the call, for a given recordset (ie need two different recordsets using the same query then use two different variable names), the query will stay that way until closed and the recordset released.

Third... what is happening within the "other code...

[Sedrick]

Sorry for not being more clear on this.

The change I am making is manual, not programatic.

I am going into the query under the Access Queries menu and doing "Design" for the existing permanent query listed there.

I right click in the tables area at the top and choose "SQL". Then I go in and take out strOther as a parameter there and also remove it from the WHERE clause. So the code behind the query then becomes:

PARAMETERS lngTeamID Long, lngShiftID Long;
 
SELECT DISTINCTROW Log.ID, Log.ID2, Log.[Other] 
 FROM Log 
  
WHERE Log.ID=[lngID]) AND Log.ID2=[lngID2]

I have a module called Module3. The manual change there in the sub called "Test1" would be:

    Set prmID = qdf.Parameters!lngID 
    Set prmID2 = qdf.Parameters!lngID2 

    ' Removed line with prmOTHER from here

    prmID = ID ' From function parameters 
    prmID2 = ID2 

    ' Removed line with prmOTHER from here

    Set rst = qdf.OpenRecordset(dbOpenSnapshot

The error will occur then when executing the "Test1" sub.

"Too Few Parameters. Expected 3"

So it is the same code that worked with three parameters before manually taking out the 3rd parameter.

[TheSmileyCoder]

Where is your qdf defined? Is it refreshed after your manual change?

[Sedrick]

It is saved manually when I exit design mode.

I just tried something else. I deleted and created it programatically using the QueryDefs collection and the CreateQueryDef method. Still the same error.

Currentdb.Querydefs.Delete "qry_def_Log"

CurrentDb.CreateQueryDef("qry_def_Log", _
"PARAMETERS lngTeamID Long, lngShiftID Long, strOther Text ( 255 ); SELECT DISTINCTROW Log.ID, Log.ID2, Log.[Other]  FROM Log  WHERE Log.ID=[lngID]) AND Log.ID2=[lngID2] AND Log.[Other]=[strOther] "

Same result :-/

[Sedrick]

Although I have not yet been able identify the cause of this I found a way to work around it.

I simply take out all the parameters from both the Query definition and the code that passes in the parameters. Run it to verify working. Then add back one parameter at a time to both the code and the query's underlying SQL.

[zmbd]

I'm glad you found a way around things.

However, I was never really clear in what you were doing to begin with....

Why are you trying to change a stored query by hand while a VBA object is running that refers to it?

Once again, I point out... you've not shown where the record set was released in the code. You open a snapshot of the record set and then you go changing the query by hand while the recordset is still open - I'd expect something wierd.

[Sedrick]

Sorry for the misunderstandin g. I was not running code and then changing things manually in the middle of the execution. I guess I did not make that clear enough.

I was executing what I had set up manually. Stopped the execution. Made a change to the query and re-executed. The recordset was released at the end of the module when the code closed it and set it to nothing.

I found another possible solution since my last post. It seems like Access did not like one of my parameter names. When I changed the name it worked with much less trouble.

Thanks for looking into this with me. Much appreciated.

[TheSmileyCoder]

I am glad to hear you got it working, but I also find that this thread is a very good example of too little information being provided from the person asking the question. When it comes to code, its important to see how (And WHERE public/private) objects are dimensioned and set, and even in some cases (Especially for public variables) how they are cleaned up.