3061 VBA Error - SQL Query "Too few parameters. Expected 1." Simple query

Question

I have the following code:

Private Sub lst1Model_Operation_Click()
Dim db As Database
Dim sSQL As String
Dim rst As Recordset

Set db = CurrentDb

    sSQL = "SELECT * FROM qryOrder_Model_Operation_Value WHERE Model_Operation_ID = " & CInt(Me![lst1Model_Operation].Value)

Debug.Print sSQL 'when pasted this into a query SQL, it works flawlessly.

Set rst = db.OpenRecordset(sSQL) 'error line

'some code here

rst.Close
Set db = Nothing
End Sub

I'm at loss at what to do. The Debug.Print looks like:

SELECT * FROM qryOrder_Model_Operation_Value WHERE Model_Operation_ID = 748

And as I said, if I paste that Debug.Print into a Query in the Access itself, it produces the desired results.

I have tried adding ' ' around the value, but using CInt() I already made sure it is parsed as integer. The Model_Operation_ID also expects to get an integer (otherwise it wouldn't work in a separate query either).

Edit: The qryOder_Model_Operation_Value is as follows:

SELECT tbl1Model_Operation.Model_Operation_ID, tbl1Model_Operation.Model_ID, tbl1Model_Operation.Operation_Value_ID, tbl2Operation_Value.Operation_Name_ID, tbl3OperationsList.Operation_Name, tbl1Order_Model.Quantity AS [Počet párov], tbl1Order_Model.Order_ID
FROM tbl3OperationsList INNER JOIN (tbl2Operation_Value INNER JOIN (tbl1Model_Operation INNER JOIN tbl1Order_Model ON tbl1Model_Operation.Model_ID = tbl1Order_Model.Model_ID) ON tbl2Operation_Value.Operation_Value_ID = tbl1Model_Operation.Operation_Value_ID) ON tbl3OperationsList.Operation_ID = tbl2Operation_Value.Operation_Name_ID;

I will add it in the question itself so the formatting remains readable. — emihir0
– emihir0, Commented May 6, 2015 at 16:43
The query itself looks ok... but you are querying a Access Query which might expect a parameter? — Peter Schneider
– Peter Schneider, Commented May 6, 2015 at 16:43
The qryOrder_Model_Operation_Value is in the question itself now. — emihir0
– emihir0, Commented May 6, 2015 at 16:53

HansUp · Accepted Answer · 2015-05-06 17:12:47Z

3

Make sure Access understands you want rst to be a DAO.Recordset instead of an ADODB.Recordset:

'Dim rst As Recordset
Dim rst As DAO.Recordset

Both the ADO and DAO object models include Recordset objects. Although similar in some respects, they can not be used interchangeably.

When your Access VBA project references include a version of ADO (ActiveX Data Objects) and that reference has a higher priority than the DAO reference, Dim rst As Recordset will give you an ADODB.Recordset. Avoid unwelcome surprises by always qualifying your Recordset declarations with DAO or ADODB as appropriate.

edited May 6, 2015 at 17:12

answered May 6, 2015 at 16:56

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HansUp Over a year ago

You're welcome. That didn't seem like it could be the explanation for error # 3061. But, when you showed Alex error # 3011, that was a dead give away. :-)

Alex Over a year ago

the way .net detect number and types of parameters for the mutable functions brings a lot of surprises :-)

Alex · Accepted Answer · 2015-05-06 16:48:11Z

0

Try this way:

Set rst = db.OpenRecordset(sSQL,dbOpenTable)

or

Set rst = db.OpenRecordset(sSQL,dbOpenSnapshot)

answered May 6, 2015 at 16:48

Alex

17.3k1 gold badge31 silver badges52 bronze badges

3 Comments

emihir0 Over a year ago

I get the following error: i.imgur.com/4FM3iNj.png on dbOpenTable and 13 - Type mismatch on dbOpenSnapshot :/.

Alex Over a year ago

that is much better. so tell me what is your database? is it MS Acces? we can remove mysql tag from OP ?

emihir0 Over a year ago

Sorry @Alex, removed the mysql tag now. Indeed my database is MS Access.

Collectives™ on Stack Overflow

3061 VBA Error - SQL Query "Too few parameters. Expected 1." Simple query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related