Solved

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

Posted on 2011-09-29
4
553 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

21 Experts available now in Live!

Get 1:1 Help Now