Too Few Parameters, Too Few Competence and Patience

[NauticalGent]

I'm going to need a new monitor, the one I have cannot take a punch...

Here is my query - It is a QueryDef: qryExtDB

UPDATE t_PRON_User_Data IN '\\orgeast\ACCESS_DBS-E\Aviation\AP\WMT_Code\Sandbox_Huntsville\User\WMT_User_PR.accdb' SET t_PRON_User_Data.[KO Assigned] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[KO Assigned], t_PRON_User_Data.[PRON Received Date Buyer] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[PRON Received Date Buyer], t_PRON_User_Data.[First Article Test Requirement] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[First Article Test Requirement], t_PRON_User_Data.AMC_AMSC = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[AMC_AMSC], t_PRON_User_Data.[PRON Canceled] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[PRON Canceled], t_PRON_User_Data.[PRON Canceled Date] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[PRON Canceled Date]

WHERE (((t_PRON_User_Data.ID)=[Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[ID]));

Not the way I normally do things, but I am not the lead dog in this race.

Why is it when I use the OpenQuery method it executes flawlessly, but when I try the CurrentDB.Execute method I get "Too Few Parameters, expected 7"?

There WHERE Clause includes PK field ([ID]) - In the past, if I included the PK in the SQL statement, it fixed the problem. Today, no dice.

 

[theDBguy]

The reason is because the query uses the expression service, and VBA does not.

You could check out this generic function.

Code Snippets

 

[NauticalGent]

Thanks, I will definitely give it a shot, if anything, to learn something new!

The reason is because the query uses the expression service, and VBA does not.

In English please, and speak as if you were talking to a small child, or a golden retriever...

 

[NauticalGent]

Is the Expression Service the feature that allows us to use the query grid without have to use quotes (both single and double), hastags and such?

Or is it the form reference that is gumming it up?

 

[theDBguy]

Thanks, I will definitely give it a shot, if anything, to learn something new!

In English please, and speak as if you were talking to a small child, or a golden retriever...

Hi. I was generalizing. I will have to find a reference to make sure I don't misspeak about it. Essentially, it's the same reason why you can't use Split() in a query. It's because one service sometimes does not understand the same expressions as the other service does.

PS. Also, the expression services can have different process for the same expressions. For example, using the IIf() statement could have different results depending on which expression service is used.

 

[arnelgp]

it is maybe you are using the Wrong database (currentdb):

Dim db As DAO.Database
Set db = DbEngine.OpenDatabase("\\orgeast\ACCESS_DBS-E\Aviation\AP\WMT_Code\Sandbox_Huntsville\User\WMT_User_PR.accdb", False, False)
db.Execute "UPDATE t_PRON_User_Data  SET t_PRON_User_Data.[KO Assigned] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[KO Assigned], t_PRON_User_Data.[PRON Received Date Buyer] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[PRON Received Date Buyer], t_PRON_User_Data.[First Article Test Requirement] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[First Article Test Requirement], t_PRON_User_Data.AMC_AMSC = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[AMC_AMSC], t_PRON_User_Data.[PRON Canceled] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[PRON Canceled], t_PRON_User_Data.[PRON Canceled Date] = [Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[PRON Canceled Date] WHERE (((t_PRON_User_Data.ID)=[Forms]![f_Item_Data_Status].[sf_PRON_RDD].[form].[ID]));"
db.Close
Set db = Nothing

 

[NauticalGent]

Thanks Arnel, I tried your suggestion and got the same error.

I haven't tried DBG's Function yet, but I will as soon as I get some a break,

Since two others will have to be stepping through the code, I may have to just keep it simple.

 

[NauticalGent]

DBG, I'm a little thick sometimes so apologies in advance if(when) I say something stupid.

The Function you referenced seems to return a recordset from a select query. Since my query is an action query, how would I adapt it to return an SQL String I could execute?

 

[arnelgp]

Dim frm As Form
Dim db As DAO.Database
Set frm = [Forms]![f_Item_Data_Status]![sf_PRON_RDD].[Form]
Set db = DBEngine.OpenDatabase("\\orgeast\ACCESS_DBS-E\Aviation\AP\WMT_Code\Sandbox_Huntsville\User\WMT_User_PR.accdb", False, False)
With db.CreateQueryDef("", _
    "UPDATE " & _
    "t_PRON_User_Data  SET " & _
    "t_PRON_User_Data.[KO Assigned] = p0, " & _
    "t_PRON_User_Data.[PRON Received Date Buyer] = p1, " & _
    "t_PRON_User_Data.[First Article Test Requirement] = p2, " & _
    "t_PRON_User_Data.AMC_AMSC = p3, " & _
    "t_PRON_User_Data.[PRON Canceled] = p4, " & _
    "t_PRON_User_Data.[PRON Canceled Date] = p5 " & _
    "WHERE t_PRON_User_Data.ID=p6;")
    .Parameters("p0") = frm![KO Assigned]
    .Parameters("p1") = frm![PRON Received Date Buyer]
    .Parameters("p2") = frm![First Article Test Requirement]
    .Parameters("p3") = frm![AMC_AMSC]
    .Parameters("p4") = frm![PRON Canceled]
    .Parameters("p5") = frm![PRON Canceled Date]
    .Parameters("p6") = frm![ID]
    .Execute
End With
db.Close
Set db = Nothing

 

[theDBguy]

DBG, I'm a little thick sometimes so apologies in advance if(when) I say something stupid.

The Function you referenced seems to return a recordset from a select query. Since my query is an action query, how would I adapt it to return an SQL String I could execute?

There is an equivalent function for action queries.

Code Snippets

 

[arnelgp]

"SELECT INTO.." is an action query also.
fnAnySQL() parameter query | Access World Forums (access-programmers.co.uk)

 

[NauticalGent]

Thanks again Arnel, I did in fact try setting the parameters with the exiting Query Def and it DID work. The problem is there are two others working on this. They are the ones you created the Apps and will be the ones modifying the code once I find a solution to our problem.

Unfortunately, they are not too adept at VBA. That being said, the fact they were able to develop a tool that 140 users can use is impressive. I have learned MUCH here and with that knowledge I have been able to help them solve a number of issues they were having. However, there is the "Pride in ownership" hurdle and no one likes being told that their creation is not optimized.

With them not being too familiar with VBA, it is much easier for them to use the OpenQuery method instead of qdef.Execute.

 

[The_Doc_Man]

NG, let me step in with background explanation. We rarely observe this directly, but there are two environments for Access, or in fact for any system that has the separation of duties between the user interface and the data tables interface.

The Access GUI/code segment includes actions like string concatenation and many types of string manipulation. You also can have application objects and can get values from the properties of some of those objects. You usually have to use CStr() and a concatenation step to put app object values into a string of any kind.

The JET/ACE SQL segment has whatever functions and actions are defined for SQL. You can look at the Database Objects library using Object Browser to see what is defined in that particular library. We know that DAO is somehow included there. A lot of functions are there too.

When you use a DoCmd.RunSQL, your SQL statement gets processed by the code segment first before it gets passed to the SQL segment. That is, if your SQL string includes a concatenation operation of any kind, it gets done BEFORE the SQL gets passed along to where the statement is executed by JET/ACE as a separate action. When theDBguy talks about two different services, I believe that is what he is discussing. The issue with DB.Execute is that the SQL string you are naming has to exist ready-to-go because the code segment doesn't see it again. (Yes, it is possible if you give a quoted string that the code environment gets one last look...)

There are things visible to the code segment that cannot be seen by the SQL segment. That is because MSACCESS has its own "process thread" that is separate from the JET/ACE thread. But this has secondary implications. You see, to have a separate thread, you need separate memory, and once you do that, the two threads become "electronically" separate due to Windows security code for process separation (the feature that stops user Johnny from stomping on user Toby). They ONLY communicate through preferred connection points (like maybe a string-input point for passing in SQL and a data output point for getting back values). There is no looking at a "public reference" by SQL to pick up a value.

In practical terms, the code segment that starts the DoCmd.RunSQL can see anything. The SQL segment that executes the query can only see the tables, whether local or linked, plus the actual text of the SQL statement (whether it was passed literally or by reference to a named query). The two services are based on the two environments. That is the "WHY" of having differences. The actual differences? Unfortunately, MS didn't release their code so some of it is a black box to us. But there are some functions that cannot be reached by SQL because we can only see MSACCESS.EXE's references. We cannot see whether JET/ACE has ANY references other than the standard database objects library.

Hope that didn't confuse you too much. As to "screens not withstanding a punch" - I know that percussive adjustment used to work if something was loose, but doesn't usually work these days 'cause the damned screens use sealed circuitry. Takes ALL the fun out of releasing frustration.

 

[NauticalGent]

Hope that didn't confuse you too much. As to "screens not withstanding a punch" - I know that percussive adjustment used to work if something was loose, but doesn't usually work these days 'cause the damned screens use sealed circuitry. Takes ALL the fun out of releasing frustration.

 

[NauticalGent]

Doc, even though you departed from my "speak as if you were talking to a small child, or a golden retriever..." request, I did understand what you said and, coupled with Arnel's and DBG's "fix all SQL" functions I now have a better understanding of the "why" it is the way it is and the "how" to fix it if I want to use the .Execute Method over the OpenQuery.

Really appreciate the replies. So glad this Forum exists...

 

[NauticalGent]

UPDATE: I've tried both Functions and they both work PERFECTLY. I thank you, my fist thanks you and my 2nd monitor thanks you too!

PS: Thanks!

 

[theDBguy]

UPDATE: I've tried both Functions and they both work PERFECTLY. I thank you, my fist thanks you and my 2nd monitor thanks you too!

PS: Thanks!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[The_Doc_Man]

my fist thanks you and my 2nd monitor thanks you too!

Can a monitor sigh in relief?

 

[NauticalGent]

Can a monitor sigh in relief?

If it could, this one would. The one I punched survived but I'm sure the message was loud and clear...