Solved

Error 3061 : Expected 2 MS Access

Posted on 2012-04-10
17
599 Views
Last Modified: 2012-06-19
Hi I need an urgent solution to this please


Sub runfetch_BudData()

Dim mydb As DAO.Database
Dim rstparameter As DAO.Recordset
Dim rstdata As DAO.Recordset
Dim strcriteria As String
'Dim sqlstringIn As String
'Dim sqlstringOut As String
Dim strSQL1 As String


Set mydb = CurrentDb
strSQL = "SELECT [BudgetHeirarchy-DivisionNames-Table].[Program ID]"
strSQL = strSQL & "FROM [BudgetHeirarchy-DivisionNames-Table]"
strSQL = strSQL & "WHERE ((([BudgetHeirarchy-DivisionNames-Table].FilesToRun)=-1));"
Set rst = CurrentDb.OpenRecordset(strSQL)
DoCmd.RunSQL ("Delete * from tblTMP")


Do Until rst.EOF = True
strcriteria = rst![Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "Dex1", "tblTMP", "c:\SAP Extracts\DivisionExtract\" & strcriteria, True
SQL = "INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, " & rst.[Program ID] & " AS [Program ID] FROM tblTmp;"
Debug.Print (SQL)

Error is on this line


CurrentDb.Execute SQL, dbFailOnError


I'm getting an error 3061. Expected 2


On the Debug Output its coming as

INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, AF-B01 AS [Program ID] FROM tblTmp;

Please help urgently
0
Comment
Question by:FAH_
[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
  • 10
  • 6
17 Comments
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 37830352
You will need to edit the select statement so that it returns a single field since you are appending a single field

It should look something like this:

INSERT INTO tblMain ([Program ID]) SELECT "AF-B01" AS [Program ID] FROM tblTmp;

Open in new window


Try this code:

SQL = "INSERT INTO tblMain ([Program ID]) SELECT " & Chr(34)  & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"

Open in new window

0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 37830373
Not sure why you even have the select from the table.

This may be all you need:

SQL = "INSERT INTO tblMain ([Program ID]) Values( " & Chr(34)  & rst.[Program ID] & Chr(34) & ");"

Open in new window

0
 

Author Comment

by:FAH_
ID: 37830430
I'm adding all fields from another table and entering a new field which equals the Program ID value from the recordset
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:FAH_
ID: 37830444
When I entered the SQL its giving me now an unidentified field error 3127 for [Program ID]
0
 

Author Comment

by:FAH_
ID: 37830445
This is debug output

INSERT INTO tblMain ([Program ID]) SELECT "AF-B01" AS [Program ID] FROM tblTmp;
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 37830468
You are actually inserting a single field ([Program ID] with your SQL of INSERT INTO tblMain ([Program ID])  

The Syntax is  INSERT INTO tblMain (Field1, Field2, ...) ...  so you need to list all the fields with the way you are writing the SQL.
0
 

Author Comment

by:FAH_
ID: 37830512
I got it to work except its not importing all the fields from tblTmp


strSQL1 = "INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, " & Chr(34) & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37830783
As mentioned earlier, you need to spell out all of your fields, and match the order between the two clauses:

strSQL1 = "INSERT INTO tblMain (Field1, Field2, Field3, Field4, [Program ID]) SELECT Field1, Field2, Field3, Field4, " & Chr(34) & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"
0
 
LVL 21
ID: 37830809
I find it best to avoid SELECT *  or SELECT tblTmp.*.  I always list every field. This way I am sure it is correct
0
 

Author Comment

by:FAH_
ID: 37832615
I'm getting the following error now :

Runtime 3049: Cannot Open Database 'INSERT INTO tblMain  

Please see attached file for the error ... It worked on almost every file and got stuck on this one
runtime3049.doc
0
 

Author Comment

by:FAH_
ID: 37832795
I have also checked online that runtime error usually refers to not enough memory so I'm trying on a different database and I'm currently getting  the same error 3061 : Expected 1.

I have checked all the field names. I'm attaching the database for your reference
Database2.accdb
PA-B01.txt
0
 

Author Comment

by:FAH_
ID: 37832877
Secondly, its duplicating the lines over and over and assigning it the wrong text file ID


I'm getting multiple instances of the same line over and over in tblMain

However in tblTmp its perfectly fine
0
 

Author Comment

by:FAH_
ID: 37832888
Do Until rst.EOF = True

strcriteria = rst![Program ID] & ".txt"

DoCmd.TransferText acImportFixed, "Dex1", "tblTMP", "c:\SAP Extracts\DivisionExtract\" & strcriteria, True

strSQL1 = "INSERT INTO tblMain (CCGROUP,COST_CENTRE_DESC,CostElement,CostElementDesc,ActualYTD,CommitmentYTD,TotalYTD,PlanYTD,YTDVariance,AnnualPlan,AnnualVariance,[Program ID]) SELECT CCGROUP,COST_CENTRE_DESC,CostElement,CostElementDesc,ActualYTD,CommitmentYTD,TotalYTD,PlanYTD,YTDVariance,AnnualPlan,AnnualVariance, " & Chr(34) & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"

Debug.Print (strSQL1)

CurrentDb.Execute strSQL1, dbFailOnError


rst.MoveNext
Loop
MsgBox "completed"
0
 
LVL 21
ID: 37838200
<<Secondly, its duplicating the lines over and over and assigning it the wrong text file ID >>

Well that is exactly what your code should be doing. Thsi means the code is executlting properly. It just may bot be what you want.  

Your code is running the exact  same append SQL

strSQL1 = "INSERT INTO tblMain (CCGROUP,COST_CENTRE_DESC,CostElement,CostElementDesc,ActualYTD,CommitmentYTD,TotalYTD,PlanYTD,YTDVariance,AnnualPlan,AnnualVariance,[Program ID]) SELECT CCGROUP,COST_CENTRE_DESC,CostElement,CostElementDesc,ActualYTD,CommitmentYTD,TotalYTD,PlanYTD,YTDVariance,AnnualPlan,AnnualVariance, " & Chr(34) & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"

Open in new window



for every record in the recordset (rst). The only change is the the Program ID will change each time the same data is append over and over again.  

It would help if you would explain what you are attempting  to do. Once we understand what you want to do then we can help you modify the code to get the desired results.
0
 

Author Comment

by:FAH_
ID: 37838619
What I want is the program to grab all fields from a text file then append it to a table with its filename in another field.

Originally the table would be empty then filled in with records imported from the text files.

In my opinion it should be that the tblTmp should be cleared once the records have been inserted into TblMain
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 37838843
<<Secondly, its duplicating the lines over and over and assigning it the wrong text file ID >>

Huh? ... what dos that have to do with the code you posted?

If you are wanting to append the data into from the temp table into the tblMain then why do you need the recordset?  One append query can move all the data from the temp table table to the tblMain  table.

I think that your program logic is not correct for what you want to accomplish. Your code is functioning  correctly for how it was written.  I recommend exampling the steps  in detail using plain English not in program logic.
0
 

Author Comment

by:FAH_
ID: 37838916
For example I have 10 text files each with its unique filename.

I need a loop to go through another table to see whether I want to import these text files based on their names.

After confirming that these files need to be imported

I would import these files into a table. Furthermore I need a field to populate the filename of the text file being imported currently.

This would be done until all the files have been imported.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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