3001 error on parameter query recordsets after upgrade

[dlambert]

After upgrading my access database from 97 to 2003 I'm running into problems with recordsets opened from querydef objects.

Here's an example

Sub testdao()
Dim db As DAO.Database, RecMain As DAO.Recordset, myQuery As DAO.QueryDef
Dim MillSection As String

Set db = CurrentDb()
Set myQuery = db.QueryDefs("bbk_MainQueryByState")
MillSection = "WDUS"
myQuery.Parameters("[Mill Section]") = MillSection
Set RecMain = myQuery.OpenRecordset()
With RecMain
Do While Not .EOF
Debug.Print "FacilityID=" & !FacilityID
.MoveNext
Loop
End With

End Sub

The error I return is Run Time Error 3001 Invalid argument.

In testing the only thing I tried that made any difference was changing the openrecordset options to...

Set RecMain = myQuery.OpenRecordset(dbOpenDynaset, dbOptimistic)

This starts to work but errors with 3254 cannot lock all records. Well I don't realy need all the records locked, in fact I could use a read only recordset if I could figure it out. But I haven't found any documents that show which parameters work with each other and the above trial was they only attempt that almost produced results.

Anyone have any ideas?

Thanks

dl

 

[Mike555]

Is the following reference checked in your VBA:
Microsoft DAO 3.6 Object Library

--
Mike

 

[dlambert]

Yes, sorry.

I realized after my post that I should have mentioned I'd checked for the library reference.

 

[RoyVidar]

Don't know much about DAO, but could you try an ADO sample?

[tt]dim cmd as adodb.command
dim rs as adodb.recordset
MillSection = "WDUS"
set cmd=new adodb.command
with cmd
.activeconnection=currentproject.connection
.properties("Jet OLEDB:Stored Query")=true
.commandtext="bbk_MainQueryByState"
.parameters("[Mill Section]") = MillSection
set rs = .execute
end with
do while not rs.eof
debug.print "FacilityID=" & rs("FacilityID").value
rs.movenext
loop[/tt]

This should give a readonly forwardonly recordset, if this too fails, let's hope some of the DAO gurus drop by...

You have tried all arguements? dbOpenDynamic, dbOpenForwardOnly, dbOpenDynaset, dbOpenSnapshot?

Roy-Vidar

 

[dlambert]

I've got a lot of DAO code I'm managing so my hope is that I can find a solution without switching to ADO.

To answer your question, yes, I've tried changing the arguments and using different lock parameters.

 

[PHV]

But I haven't found any documents that show which parameters work with each other
Search your local drives for file named DAO*.CHM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244