FAH_
asked on
Error 3061 : Expected 2 MS Access
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-DivisionN ames-Table ].[Program ID]"
strSQL = strSQL & "FROM [BudgetHeirarchy-DivisionN ames-Table ]"
strSQL = strSQL & "WHERE ((([BudgetHeirarchy-Divisi onNames-Ta ble].Files ToRun)=-1) );"
Set rst = CurrentDb.OpenRecordset(st rSQL)
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
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-DivisionN
strSQL = strSQL & "FROM [BudgetHeirarchy-DivisionN
strSQL = strSQL & "WHERE ((([BudgetHeirarchy-Divisi
Set rst = CurrentDb.OpenRecordset(st
DoCmd.RunSQL ("Delete * from tblTMP")
Do Until rst.EOF = True
strcriteria = rst![Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "Dex1", "tblTMP", "c:\SAP Extracts\DivisionExtract\"
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I entered the SQL its giving me now an unidentified field error 3127 for [Program ID]
ASKER
This is debug output
INSERT INTO tblMain ([Program ID]) SELECT "AF-B01" AS [Program ID] FROM tblTmp;
INSERT INTO tblMain ([Program ID]) SELECT "AF-B01" AS [Program ID] FROM tblTmp;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;"
strSQL1 = "INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, " & Chr(34) & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"
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;"
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;"
I find it best to avoid SELECT * or SELECT tblTmp.*. I always list every field. This way I am sure it is correct
ASKER
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
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
ASKER
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
I have checked all the field names. I'm attaching the database for your reference
Database2.accdb
PA-B01.txt
ASKER
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
I'm getting multiple instances of the same line over and over in tblMain
However in tblTmp its perfectly fine
ASKER
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, CostElemen t,CostElem entDesc,Ac tualYTD,Co mmitmentYT D,TotalYTD ,PlanYTD,Y TDVariance ,AnnualPla n,AnnualVa riance,[Pr ogram ID]) SELECT CCGROUP,COST_CENTRE_DESC,C ostElement ,CostEleme ntDesc,Act ualYTD,Com mitmentYTD ,TotalYTD, PlanYTD,YT DVariance, AnnualPlan ,AnnualVar iance, " & Chr(34) & rst.[Program ID] & Chr(34) & " AS [Program ID] FROM tblTmp;"
Debug.Print (strSQL1)
CurrentDb.Execute strSQL1, dbFailOnError
rst.MoveNext
Loop
MsgBox "completed"
strcriteria = rst![Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "Dex1", "tblTMP", "c:\SAP Extracts\DivisionExtract\"
strSQL1 = "INSERT INTO tblMain (CCGROUP,COST_CENTRE_DESC,
Debug.Print (strSQL1)
CurrentDb.Execute strSQL1, dbFailOnError
rst.MoveNext
Loop
MsgBox "completed"
<<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
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.
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;"
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER