Run-time error '3061'. Too few parameters. Expected 1. (Access 2007)

Question

I have the following 'set recordset' line that I cannot get working. The parameters seem correct according to all available help I can find on the subject.

The error displays :

"Run-time error '3061'. Too few parameters. Expected 1."

Here is the line of code:

Set rs = dbs.OpenRecordset("SELECT Centre_X, Centre_Y FROM [qry_all_details] 
WHERE ID = " & siteID & ";", dbOpenSnapshot)

Where rs is the recordset (Dim rs As Recordset) and dbs = CurrentDb()

Any help would be appreciated.

I have tried removing the WHERE cause with no effect, and also using single quotes between double quotes, but no joy.

Many thanks.

Acedia

Is this query you're opening a parameterized one? Maybe it's expecting a value... – Edholm

Answer 1

"Run-time error '3061'. Too few parameters. Expected 1."

I believe this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.

Gmt

Too few parameters, expected X means that X fieldnames that you'll using in your SQL-Statement aren't available. Mostly typos when writing down lines of SQL Code by hand, mostly when you're using DBs which can have multiple "versions" of it, one DB has those fields, one DB hasn't. I prefer to check if the fields are avaible with a simple code: forums.devarticles.com/microsoft-access-development-49/… – Forayer

@DanielAlexanderKarr Link is dead :( – Queridas

Answer 2

you have:

WHERE ID = " & siteID & ";", dbOpenSnapshot)

you need:

WHERE ID = "'" & siteID & "';", dbOpenSnapshot)

Note the extra quotations ('). . . this kills me everytime

Edit: added missing double quote

Nabila

Answer 3

My case was simply the fact that I created a SQL expression using the format Forms!Table!Control. That format is Ok within a query, but DAO doesn't recognize it.

This doesn't work:

Dim rs As DAO.Recordset, strSQL As String
strSQL = "SELECT * FROM Table1 WHERE Name = Forms!Table!Control;"
Set rs = CurrentDb.OpenRecordset(strSQL)

This is Ok:

Dim rs As DAO.Recordset, strSQL, val As String
val = Forms!Table!Control
strSQL = "SELECT * FROM Table1 WHERE Name = '" & val & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)

Chrysostom

Answer 4

My problem was also solved by the Single Quotes around the variable name

Marx

Answer 5

I got the same error message before. in my case, it was caused by type casting. check if siteID is a string, if it is you must add simple quotes.

hope it will help you.

Movable

Answer 6

My problem turned out to be, I had altered a table to add a column called Char. As this is a reserved word in MS Access it needed square brakcets (Single or double quote are no good) in order for the alter statement to work before I could then update the newly created column.

Zumwalt

Char is a reserved word in Access like "Date", "Time", "Now", and other "Functions" when you're using Brackets for all your fields in your SQL-Query you're good to go – Forayer

Answer 7

Make sure [qry_all_details] exists and is runnable. I suspect it or any query it uses, is missing the parameter.

Monopteros

Answer 8

I got the same error with something like:

Set rs = dbs.OpenRecordset _
( _
  "SELECT Field1, Field2, FieldN " _
  & "FROM Query1 " _
  & "WHERE Query2.Field1 = """ & Value1 & """;" _
, dbOpenSnapshot _
)

I fixed the error by replacing "Query1" with "Query2"

Tropic

Answer 9

In my case, I got this error when I tried to use in a query a new column, which I added to MySQL table (linked to MS Access), but didn't refresh it inside MS Access.

To refresh a linked remote table:

1. Open "Linked Table Manager" ("External Data" tab on ribbon);
2. Select a checkbox near the table you want to refresh;
3. Press "OK" button.

Chanel

Answer 10

In my case I was receiving this error when running a query from VBA with this command:

CurrentDb.Execute "qryName"

Double clicking on the query to execute it, worked fine, no error.

Changing the code to the following also worked fine, no error.

DoCmd.OpenQuery "qryName"

Hope this helps someone else who is unexpectedly getting this error. If someone could explain why the first command caused the error I'd love to know.

Breadthways

Answer 11

Does the query has more than the parameter siteID, becouse if you want to run the query one parameter still isn't filled witch gives you the error

Flann

Answer 12

In my case, I had simply changed the way I created a table and inadvertently changed the field name I tried to query. Make sure the field names you reference in the query actually exist in the table/query you are querying.

Dunois

Answer 13

This Message is also possible to pop up, if there is a typo in the fields on which you define a join

Northnorthwest

Answer 14

Thanks for John Doe's solution that helped a lot. Mine is very similar with some difference, using TempVars

Instead of :

      strSQL = "SELECT * FROM Table1 WHERE Name = Forms!Table!Control;"

I used:

      strSQL = "SELECT * FROM Query1"  , Query1 being common for other usage

Query1 as:

 "Select Field1, Field2 from Table1 where Id= [TempVars]![MyVar]

And similarly, removing [TempVars]![MyVar] from view solved the problem.

Atory

Answer 15

Well, if anyone ever needs this info, I found it addressed nowhere else and spent days trying to find a problem: If SQL query contains two consecutive dots, or dot followed by space, it will report this same error when running SQL pass through (parameterized) query to MySQL. So, you will have to replace it with something else, and take care of it within MySQL.

Spaceship

An example would make this answer really helpful. – Dejection

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Scythia

Answer 16

For those of you who want to use the Criteria field in the query and not in VBA, use this:

Eval("[Forms]![frmSomeForm]![txtSomeTextBox]")

Quiescent

Answer 17

I had this error in Microsoft Access and it was because one of my linked tables needed to be re-connected. I had added fields to the main table, but the linked table did not reflect these changes until re-added.

Monkish

Answer 18

In My case I had an INSERT INTO TableA (_ ,_ ,_) SELECT _ ,_ ,_ from TableB, a run-time error of 33061 was a field error. As @david mentioned. Either it was a field error: what I wrote in SQL statement as a column name did not match the column names in the actual access tables, for TableA or TableB.

I also have an error like @DATS but it was a run-time error 3464.

Ergosterol