Solved

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

Posted on 2011-09-29
4
556 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
ID: 36815389
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
ID: 36815420
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
ID: 36815430
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
ID: 36815486
Thanks guys
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

756 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