Too few parameters

[ck.letterbox]

Hi guys,

I know that there're heap of this error posted on the web.
I have read through some of them but still don't get it.
My problem is I want to do:

strQuery = "SELECT * INTO [tblExportTemp] FROM " & qryToExport.Name
Debug.Print strQuery
dbCode.Execute strQuery

the qryToExport is a query. Debug window display:
SELECT * INTO [tblExportTemp] FROM qryBS_Facility_sbf

When I run this strQuery in query window, it works fine.
But when running in VBA it throw me "(3061) Too few parameters. Expected 8."
I hope that you guys are not bored to answer such question.

Any suggestion would be appreciate.

 

[datAdrenaline]

I would bet that the referenced query has a form reference of some kind. THis will cause the error becuase when you execute an SQL through the CurrentDB (or a database object variable), you are directly manipulating the JET/ACE db engine. The hierarchy of things loosely goes like this Access Application -> Data Interface (DAO/ADO) -> Data Engine (JET). So when you use CurrentDb you are at the Data Interface layer directly manipulating the Data Engine. The Data Interface layer basically just sends commands to the data engine and receives data back from the engine. The Data Interface layer has no clue how to resolve what an Access Object (ie: a text box, combo box, form, etc.) is. Plus when the SQL statement (command) reaches the data engine, the engine has no clue what an Access object is either! What that means is that when you issue a data command (ie: a SQL statement) to the data engine from the data interface, you need to resolve all the references to Access objects because you are sending a literal string to the data engine.

Now ... since the data engine is just receiving a string all things in that string must be understood by the data engine. The data engine does have a few tricks up its sleeve, so despite the data engines inability to see (resolve) Access Objects it does have visibility of User Defined Functions, and many VBA functions through what is called the "JET Expression Service" ... so you can send VBA function calls to the engine. So VBA can be used like a bridge to Access Objects ... for example, you can wrap a text box control reference with the Eval() function because JET knows VBA and sees Eval(), and Eval() can see Access Objects ... so it all works ... for example:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblTable WHERE Field1=Eval('[Forms]![MyForm]![MyTextBox]')"

You can also pass the form reference directly BUT you need to use the Parameters collection and resolve the parameter for the data engine.

The reason you can use form references for DoCmd.RunSQL (or directly from the NavPane/Database window) is that the RunSQL (or the process to open a query) starts its journey to the db engine at the Access application level and therefore Access resolves the reference, then gives the resolved command to the data interface layer which then passes it to the db engine...

Does the make sense?

Other references your may find useful (they may be interlinked ...)
This reply is virtualy a cut/paste from here ... that thread has links to other related threads...

 

[ck.letterbox]

datAdrenaline,Thanks very much for your time. Your info is very useful and clear enough to me.
Then I have another question follow up to your description.
Since the structure is as you described "Access Application -> Data Interface (DAO/ADO) -> Data Engine (JET)". Is it possible to has an architecture as below:
1. Have one .mde file, let say util.mde, which act as utility procedure generating report.
2. Another file that has form calling to (also passing) reference of query/table to the utility procedure in util.mde to generate report.

(I'm thinking of code maintenance and reusing)