Solved

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

Posted on 2011-09-29
4
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

739 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