OpenRecordset - Error 3061

[Simon]

When I execute the following code, I receive the a run-time error 3061 and
when I debug, the line in question is: Set rst = qdf.OpenRecordset()

Looking at web sites, I am using the correct syntax.
Please can anyone help me - thanks

Dim dbsCurrent As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim mSql As String

Set dbsCurrent = CurrentDb

mSql = "SELECT ..."

Set qdf = dbsCurrent.CreateQueryDef("", mSql)
Set rst = qdf.OpenRecordset()

rst.MoveFirst


Simon

 

[Allen Browne]

The DAO and ADO libraries both have a Recordset object. Try being explicit
about which you want, i.e. change the 3rd line to:
Dim rst As DAO.Recordset

For more info, see:
Solving Problems with Library References
at:
http://allenbrowne.com/ser-38.html

BTW, you don't have to create a temporary QueryDef object. You can just
code:
Set rst = dbsCurrent.OpenRecordset(mSql)

 

[Marshall Barton]

When I execute the following code, I receive the a run-time error 3061 and
when I debug, the line in question is: Set rst = qdf.OpenRecordset()

Looking at web sites, I am using the correct syntax.
Please can anyone help me - thanks

Dim dbsCurrent As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim mSql As String

Set dbsCurrent = CurrentDb

mSql = "SELECT ..."

Set qdf = dbsCurrent.CreateQueryDef("", mSql)
Set rst = qdf.OpenRecordset()

rst.MoveFirst

If you add
Debug.Print mSql
and then copy/paste the sql statement from the Immediate
window in to a new query, does the query work?

 

[Simon]

Thanks Allen,
I had tried both suggestions below, both causing the same error as before.
I shall look at the link you have provide for more information.

Simon

 

[Simon]

Hi Marsh,

Yes the SELECT statement within the variable mSql executes correctly when
copied into a query - I do have to remove double quotes from either end of
the string.

Simon

 

[Allen Browne]

Error 3061 says parameters are expected.

Perhaps this query contains something like:
Forms!Form1!Text0
If so, the Expression Service handles that when you execute it in the query
window, but is not available in DAO code. You will need to explicitly assign
a value to the parameter, or else concatenate the value into the string.

This kind of thing:

Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE SomeField = " & Forms!Form1!Text0 & ";"
Set rst = dbsCurrent.OpenRecordset(strSql)

Don't forget to add the quote delimiters if SomeField is text, or the #
delimter if it is a date/time type.

 

[Simon]

Thanks Allen,
I did indeed reference a control within a form and your advice below
resolved the issue - thank you.

I didn't think that I should include my SQL statement within the original
post, because no one needs to see that - how wrong! I shall include all code
from now on!

Simon

 

[Marshall Barton]

Yes the SELECT statement within the variable mSql executes correctly when
copied into a query - I do have to remove double quotes from either end of
the string.

Then that implies the query needs to have its parameters
resolved before you use OpenRecordset. I see that Allen
already resolved the issue so all I can do is wish you good
luck with your next problem ;-)

 

[Philippe Perrault]

I am having a similar problem. I am using a Between [Forms]![Form1]![Text0]
AND [Forms]![Form1]![Text1] in my query.

My VBA code looks like this

Dim db As Database
Dim MySql As String
Dim rstMySql As Recordset
Dim cnn As ADODB.Connection
Dim MyRecorset As New ADODB.Recordset
MyRecordset.activeconnection = cnn


MySql = "Select tblMain.myDate"& _
"FROM tblMain WHERE tblMain.myDate = & [Forms]![Form1]![Text0] ?? what
next?? & _
"BETWEEN [Forms]![Form1]![Text0] AND [Forms]![Form1]![Text1] ;"

Set db = CurrentDb()
Set rstMySql = db.OpenRecordset(MySql) "This is the line where the code
fails"
Set MyRecordset = qdfQry.OpenRecordset("getMyFinalQuery")

getMyFinalQuery has no parameters in it but is based on the query defined by
MySql
Thanks in advance
Philippe