Solved

Confusing Run-Time error 3061 'too few parameters expected'

Posted on 2011-09-29
4
549 Views
Last Modified: 2012-05-12
I am trying to run a MS Access 2007 Passthrough query through VBA to populate a table in an a database. Even though the passthrough query executes fine independently I keep getting a Run-Time error 3061 'too few parameters expected 15' when running it through VBA. I have performed a compact and close and a debug Complile but this hasn't solved the issue. The names of the fields in the SQL match exactly the names of the fields in the Access table.
I am using an INSERT statement rather than a SELECT INTO as I don't want to delete the table prior to running this query due top the fact that the table is used in other queries and reports.
Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "INSERT INTO tblCM_all2 " & _
                "SELECT CHG_Infrastructure_Change.Infrastructure_Change_ID, " & _
                "CHG_Infrastructure_Change.Submit_Date, " & _
                "CHG_Infrastructure_Change.Active_Approval, " & _
                "CHG_Infrastructure_Change.Actual_Start_Date, " & _
                "CHG_Infrastructure_Change.Actual_End_Date, " & _
                "CHG_Infrastructure_Change.Approval_Phase_Name, " & _
                "CHG_Infrastructure_Change.Approval_Status, " & _
                "CHG_Infrastructure_Change.Description, " & _
                "CHG_Infrastructure_Change.Scheduled_Start_Date, " & _
                "CHG_Infrastructure_Change.Scheduled_End_Date, " & _
                "CHG_Infrastructure_Change.Change_Request_Status, " & _
                "CHG_Infrastructure_Change.Priority, " & _
                "CHG_Infrastructure_Change.Site_Group, " & _
                "CHG_Infrastructure_Change.Site, " & _
                "FORMAT(CHG_Infrastructure_Change.Submit_Date,'yyyy\mm') AS Period, " & _
                "CHG_Infrastructure_Change.Status_Reason " & _
                "FROM PassThroughCM_All; "

Open in new window

0
Comment
Question by:JayceW
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 200 total points
Comment Utility
You need to specify the fields that you are inserting into, in order.

ie:

dbs.Execute "INSERT INTO tblCM_all2 " & _
                "(Infrastructure_Change_ID, " & _
                 "Submit_Date, " & _
                 " ... etc) " & _
                "SELECT CHG_Infrastructure_Change.Infrastructure_Change_ID, " & _
                "CHG_Infrastructure_Change.Submit_Date, " & _
                "CHG_Infrastructure_Change.Active_Approval, " & _
                "CHG_Infrastructure_Change.Actual_Start_Date, " & _
                "CHG_Infrastructure_Change.Actual_End_Date, " & _
                "CHG_Infrastructure_Change.Approval_Phase_Name, " & _
                "CHG_Infrastructure_Change.Approval_Status, " & _
                "CHG_Infrastructure_Change.Description, " & _
                "CHG_Infrastructure_Change.Scheduled_Start_Date, " & _
                "CHG_Infrastructure_Change.Scheduled_End_Date, " & _
                "CHG_Infrastructure_Change.Change_Request_Status, " & _
                "CHG_Infrastructure_Change.Priority, " & _
                "CHG_Infrastructure_Change.Site_Group, " & _
                "CHG_Infrastructure_Change.Site, " & _
                "FORMAT(CHG_Infrastructure_Change.Submit_Date,'yyyy\mm') AS Period, " & _
                "CHG_Infrastructure_Change.Status_Reason " & _
                "FROM PassThroughCM_All; "
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 200 total points
Comment Utility
Change the last line to this:

"FROM PassThroughCM_All as CHG_Infrastructure_Change; "


Problem is  with name used to reference the passthru query, not with the column names.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
Comment Utility
Are you passing parameters to the pass-through query [PassThroughCM_All]?

If so, Access is unable to process those parameters.  In that case, you should use similar code to build the pass-through querie SQL string so that it contains literal values rather than parameters.  Something like:

strSQL = "SELECT * FROM your SQLTable WHERE [SomeField] = " & intSomeInteger _
           &  " AND [SomeTextField] = '" & strSomeString & "'"
currentdb.QueryDefs("PassThroughCM_All").SQL = strSQL

strSQL = "INSERT INTO .... FROM PassThroughCM_All"
dbs.Execute strSQL
0
 

Author Closing Comment

by:JayceW
Comment Utility
Thanks guys
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now