If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
"Michael Gramelspacher" wrote in message ... On Sun, 10 Aug 2008 18:44:52 +0100, "bcap" wrote: "Michael Gramelspacher" wrote in message . .. On Sun, 10 Aug 2008 09:30:25 -0700 (PDT), wrote: try this dbs.Execute _ " a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _ " a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _ " a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _ " a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _ " a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _ " INTO [CHOOSERev] FROM [CHOOSEData]" & _ " WHERE (((a.TRAN_TYPE) IN ('1K','2D'))" & _ " AND ((a.LTrim_REG) '7'));" How can he execute that string, it isn't even a statement? There's no SELECT... Yes, of course. Just a clerical error on my part, but not hard to correct now that the error has been pointed out. Indeed not difficult to correct (sorry, I didn't actually look at your string closely enough to realise that *only* the "SELECT" was missing). Unfortunately, the OP's reply to you suggests that he tried to execute the code *exactly* as you posted it. |
#22
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
On Aug 10, 1:10*pm, "bcap" wrote:
"Michael Gramelspacher" wrote in message ... On Sun, 10 Aug 2008 18:44:52 +0100, "bcap" wrote: "Michael Gramelspacher" wrote in message . .. On Sun, 10 Aug 2008 09:30:25 -0700 (PDT), wrote: try this dbs.Execute _ " a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _ " a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _ " a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _ " a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _ " a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _ " INTO * [CHOOSERev] FROM * [CHOOSEData]" & _ " WHERE *(((a.TRAN_TYPE) IN ('1K','2D'))" & _ " AND ((a.LTrim_REG) '7'));" How can he execute that string, it isn't even a statement? *There's no SELECT... Yes, of course. *Just a clerical error on my part, but not hard to correct now that the error has been pointed out. Indeed not difficult to correct (sorry, I didn't actually look at your string closely enough to realise that *only* the "SELECT" was missing). Unfortunately, the OP's reply to you suggests that he tried to execute the code *exactly* as you posted it.- Hide quoted text - - Show quoted text - Do I understand this correctly? The user has opened Tool.mdb. The ChooseData table exists ChooseData in Recon. You want to dynamically create ChooseRev in Recon. Within the Tools.mdb, please try this model: Sub CopyData() Dim sInsert As String Dim sPathToRecon As String sPathToRecon = "C:\TEMP\Recon.mdb" sInsert = "SELECT ChooseData.* " _ & "INTO ChooseRev in " _ & Chr(34) & sPathToRecon & Chr(34) _ & "FROM ChooseData in " _ & Chr(34) & sPathToRecon & Chr(34) DoCmd.SetWarnings 0 DoCmd.RunSQL (sInsert) DoCmd.SetWarnings 1 End Sub |
#23
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
There is no way to take a recordset and save it as a table in one command.
You would need to first create a new, empty table, and then iterate through the recordset saving each record in turn. But, this is completely unnecessary. What you are saying is that this query works: SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7')) In which case, this code will work: dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))" There are three possible reasons why the query I gave you earlier didn't work: 1. There was a line wrap in your newsreader. In your code, every line except the last one should end with a line continuation character, an underscore. If this isn't the case, then use the VBA editor to delete the spurious linewraps caused by your newsreader. 2. The query you are constructing in VBA is just plain different to the one that works when you run it in the SQL window. Please make sure that the query in the SQL window still works (without prompting for parameters), and then post it's SQL. 3. When you ran the query in the SQL window, you erroneously did so in Tools.mdb, not in Recon.mdb, with the result that the query in the SQL window and the query in the VBA are running against *different tables* in *different databases*. Finally, I'm concerned that you say "I have set the Currentdb to "Recon.mdb"". This is impossible: CurrentDb is not an object variable, it is a method, and you cannot set it to anything. It might be a good idea for you to show us the code you are using to create and set the object variable "dbs". wrote in message ... Same error! error (#3601): Too few parameters. Expected 1" Trust me I am, at least, just as frustrated. What I believe is that the issue is so obvious that we are all walking right past it. Folks, please do not give up! **************** Please Continue Below *************************************** The VBA module from which this VBA is invoked, is in "Tools.mdb" While I am in Tools.mdb, I have set the Currentdb to "Recon.mdb" In Recon.mdb, I have previously created a table "ChooseData" I want to use Recon.mdb's "ChooseData" table to create a new table called "ChooseRev" in Recon.mdb !!!! PROGRESS !!!! NOTE: The following seems to create something (it does not bomb!) Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a solution? If it produces CHOOSERev, a "RecordSet" then what command can I use to save it in Recon.mdb (alias dbs and not in Tools.mdb?) EagleOne "bcap" wrote: dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev FROM CHOOSEData WHERE " & _ & "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" |
#24
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
maybe you should use a database engine with a future?
SQL Server typically has _MUCH_ better errors returned.. from the debug message that you are seeing it is hard to determine what is really happening. yet another reason to move to SQL Server-- (you could capture via SQL Profiler and then compare the real strings if you needed to) Soudns to me like you're just constantly frustrated by the limitations of JET. Do I really need to say it here? MOVE TO SQL SERVER, THINGS JUST WORK. You would have gotten a real error message if you used SQL Server. Sorry-- those are the facts -Aaron On Aug 10, 11:58*am, wrote: Ken, thanks for weighing in. *I still get the same error message. Please read my last posting (previous) above. I am concerned that the error message is incorrect as to the real reason. I am beginning to feel that attempting to establish a new table in project 2 from a table currently in table 2) from project 1's VBA module may be an issue that is masking the real error. Also, look at my comment about the successful SET (RecordSet) command. I am relatively new to VBA SQL Access. Therefore, if I have a RecordSet "CHOOSERev" from a Project "Recon.mdb" table "CHOOSEData", then how do I save the RecordSet "CHOOSERev" into Project "Recon.mdb" *table "CHOOSERev"? *(Again I am executing this code from Project Tools.mdb.) Ken Sheridan wrote: * *strSQL = "SELECT " & _ * * *" BFY, APPN_SYMB, SBHD, BCN, SA_SX," & _ * * *" AAA_UIC, ACRN, AMT, DOC_NUMBER, FIPC," & _ * * *" REG_NUMB, TRAN_TYPE, DOV_NUM, PAA," & _ * * *" COST_CODE, OBJ_CODE, EFY, REG_MO, RPT_MO," & _ * * *" EFFEC_DATE, Orig_Sort, LTrim_BFY, LTrim_AAA," & _ * * *" LTrim_REG, LTrim_DOV, AMT_Rev, CONCACT" & _ * * *" INTO CHOOSERev FROM CHOOSEData" & _ * * *" WHERE TRAN_TYPE IN (""1K"",""2D"")" & _ * * *" AND LTrim_REG ""7""" * *dbs.Execute strSQL |
#25
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
I don't know what you are trying to accomplish, but if the Project you are
talking about is an Access ADP, they don't support internal Tables, they use Tables from SQL Server (or, if you go back to the proper back-level of ADODB, an external Access MDB). If you are asking questions about an ADP Project, it's a really good idea to say so, up front... If not, I don't understand what you mean by your statement. Larry Linson Microsoft Office Access MVP wrote in message ... I made all of your suggested changes and I still get the same error. A Da! epiphany may have occured in my mind. I bet that a table is a RecordSet in a Project? No? If so, Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a solution? To be more clear: The VBA module is in "Tools.mdb" So I am in Tools.mdb and have set currentdb to "Recon.mdb" In Recon.mdb I have previously created a table "ChooseData" I want to use "ChooseData" to create a new "ChooseRev" table in Recon.mdb Then I close Recon.mdb and stay in "Tools.mdb" to create another Recon.mdb as Recon2.mdb. Any other thoughts? What about John Spencer wrote: Try replacing Chr(10) with VbCRLF or with Chr(13) & Chr(10) dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & vbcrlf & "FROM CHOOSEData" & vbcrlf _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" '=============================================== ===== John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '=============================================== ===== wrote: 2003 The following code works in the Access Query SQL-view window but when I try to run it in VBA, it produces the following error (#3601): "Too few parameters. Expected 1" dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & Chr(10) & "FROM CHOOSEData" & Chr(10) _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" I copied the Access query which works! exactly from the SQL window (Except I had to parse the lines with &, " etc. I guess the syntax I am using is incorrect after the .Execute command. (Note the . INTO CHOOSERev 10th line Any help greatly appreciated! EagleOne |
#26
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Please learn to read. The original question was about an SQL statement
executed from VBA. It did not remotely resemble a question asking what database engine to use. You will find basic literacy training is available at no or low cost from local welfare and educational organizations for illiterate individuals, as you clearly seem to be. That's not to excuse the original poster for beginning vast projects with half-vast understanding of his tools, but the answer to his question is not to convert to a different database engine, either local or remote, file-server or server. Sorry, but THESE are the facts. 2008 Learn-to-Read Campaign "a a r o n . k e m p f @ g m a i l . c o" wrote: maybe you should use a database engine with a future? SQL Server typically has _MUCH_ better errors returned.. from the debug message that you are seeing it is hard to determine what is really happening. yet another reason to move to SQL Server-- (you could capture via SQL Profiler and then compare the real strings if you needed to) Soudns to me like you're just constantly frustrated by the limitations of JET. Do I really need to say it here? MOVE TO SQL SERVER, THINGS JUST WORK. You would have gotten a real error message if you used SQL Server. Sorry-- those are the facts -Aaron |
#27
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Sub CreateReconFile()
' ' The purpose of Tools.mdb is to create 172 ReconXXX.mdb files monthly ' this sub is in Tools.mdb ' up to this point all prior code lines involved Tools.mdb ' next is the creation of Recon/mdb ' myPath = "C:\Access" dbsfilename = "Recon.mdb" Set appAccess = CreateObject("Access.Application") appAccess.NewCurrentDatabase myPath & dbsfilename Set dBs = appAccess.CurrentDb ' at this Currentdb.Name returns Recon.dbf dBs.Execute "SELECT * INTO STARSData FROM [Text;FMT= _ Fixed;HDR=No;DATABASE=" & myPath & ";].[STARSData#txt];", dbFailOnError Set rs = dBs.OpenRecordset("STARSData") Set rs = Nothing dBs.Execute "SELECT * INTO CHOOSEData FROM [Text;FMT= _ Delimited;HDR=No;DATABASE=" & myPath & ";].[CHOOSEData#txt];", dbFailOnError Set rs = dBs.OpenRecordset("CHOOSEData") ' The code in the next "With ....." works fine With appAccess.CurrentDb .Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_BFY VarChar(5);" .Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_AAA VarChar(7);" .Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_REG VarChar(5);" .Execute "ALTER TABLE CHOOSEData ADD COLUMN LTrim_DOV VarChar(9);" .Execute "ALTER TABLE CHOOSEData ADD COLUMN AMT_Rev VarChar(5);" .Execute "ALTER TABLE CHOOSEData ADD COLUMN CONCACT VarChar(25);" .Execute "Update CHOOSEData Set LTrim_BFY=IIf(Left([BFY],1)=""0"",Mid([BFY],2,1),[BFY]);" .Execute "Update CHOOSEData Set _ LTrim_AAA=IIf(Len([AAA_UIC])=6,Trim(Mid([AAA_UIC],2,6)),[AAA_UIC]);" .Execute "Update CHOOSEData Set _ LTrim_REG=IIf(Left([REG_NUMB],1)=""0"",Mid([REG_NUMB],2,1),[REG_NUMB]);" .Execute "Update CHOOSEData Set _ LTrim_DOV=IIf(Left([DOV_NUM],4)=""0000"",Mid([DOV_NUM],5,4)," _ & " IIf(Left([DOV_NUM],3)=""000"",Mid([DOV_NUM],4,5)," _ & " IIf(Left([DOV_NUM],2)=""00"",Mid([DOV_NUM],3,6)," _ & " IIf(Left([DOV_NUM],1)=""0"",Mid([DOV_NUM],2,7),[DOV_NUM]))));" .Execute "Update CHOOSEData Set AMT_Rev=[AMT]*-1;" .Execute "Update CHOOSEData Set CONCACT=CHOOSEData.LTrim_BFY & CHOOSEData.APPN_SYMB " _ & "& CHOOSEData.SBHD & CHOOSEData.BCN & CHOOSEData.SA_SX & CHOOSEData.TRAN_TYPE " _ & "& CHOOSEData.AMT_Rev;" End With ' Now the problem code ' With appAccess.CurrentDb .Execute "SELECT BFY, APPN_SYMB, " _ & "SBHD, BCN, SA_SX, AAA_UIC, " _ & "ACRN, AMT, DOC_NUMBER, " _ & "FIPC, REG_NUMB, TRAN_TYPE, " _ & "DOV_NUM, PAA, COST_CODE, " _ & "OBJ_CODE, EFY, REG_MO, " _ & "RPT_MO, EFFEC_DATE, Orig_Sort, " _ & "LTrim_BFY, LTrim_AAA, LTrim_REG, " _ & "LTrim_DOV, AMT_Rev, CONCACT " _ & " INTO CHOOSERev " & Chr(10) _ & "FROM CHOOSEData" & Chr(10) _ & " WHERE (((TRAN_TYPE) In ('1K','2D')) And ((LTrim_REG)'7'));" End With ' ' Other code lines End sub "bcap" wrote: There is no way to take a recordset and save it as a table in one command. You would need to first create a new, empty table, and then iterate through the recordset saving each record in turn. But, this is completely unnecessary. What you are saying is that this query works: SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7')) In which case, this code will work: dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))" There are three possible reasons why the query I gave you earlier didn't work: 1. There was a line wrap in your newsreader. In your code, every line except the last one should end with a line continuation character, an underscore. If this isn't the case, then use the VBA editor to delete the spurious linewraps caused by your newsreader. 2. The query you are constructing in VBA is just plain different to the one that works when you run it in the SQL window. Please make sure that the query in the SQL window still works (without prompting for parameters), and then post it's SQL. 3. When you ran the query in the SQL window, you erroneously did so in Tools.mdb, not in Recon.mdb, with the result that the query in the SQL window and the query in the VBA are running against *different tables* in *different databases*. Finally, I'm concerned that you say "I have set the Currentdb to "Recon.mdb"". This is impossible: CurrentDb is not an object variable, it is a method, and you cannot set it to anything. It might be a good idea for you to show us the code you are using to create and set the object variable "dbs". wrote in message .. . Same error! error (#3601): Too few parameters. Expected 1" Trust me I am, at least, just as frustrated. What I believe is that the issue is so obvious that we are all walking right past it. Folks, please do not give up! **************** Please Continue Below *************************************** The VBA module from which this VBA is invoked, is in "Tools.mdb" While I am in Tools.mdb, I have set the Currentdb to "Recon.mdb" In Recon.mdb, I have previously created a table "ChooseData" I want to use Recon.mdb's "ChooseData" table to create a new table called "ChooseRev" in Recon.mdb !!!! PROGRESS !!!! NOTE: The following seems to create something (it does not bomb!) Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a solution? If it produces CHOOSERev, a "RecordSet" then what command can I use to save it in Recon.mdb (alias dbs and not in Tools.mdb?) EagleOne "bcap" wrote: dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev FROM CHOOSEData WHERE " & _ & "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" |
#28
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
The original poster is learning. We all started from zero. Please consider that fact and consider
posting a solution instead of perpetuating an out-of-control flammer. Please Learn to Read wrote: Please learn to read. The original question was about an SQL statement executed from VBA. It did not remotely resemble a question asking what database engine to use. You will find basic literacy training is available at no or low cost from local welfare and educational organizations for illiterate individuals, as you clearly seem to be. That's not to excuse the original poster for beginning vast projects with half-vast understanding of his tools, but the answer to his question is not to convert to a different database engine, either local or remote, file-server or server. Sorry, but THESE are the facts. 2008 Learn-to-Read Campaign "a a r o n . k e m p f @ g m a i l . c o" wrote: maybe you should use a database engine with a future? SQL Server typically has _MUCH_ better errors returned.. from the debug message that you are seeing it is hard to determine what is really happening. yet another reason to move to SQL Server-- (you could capture via SQL Profiler and then compare the real strings if you needed to) Soudns to me like you're just constantly frustrated by the limitations of JET. Do I really need to say it here? MOVE TO SQL SERVER, THINGS JUST WORK. You would have gotten a real error message if you used SQL Server. Sorry-- those are the facts -Aaron |
#29
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
The actual Access SQL-view code is:
SELECT CHOOSE_Add_Fields.BFY, CHOOSE_Add_Fields.APPN_SYMB, CHOOSE_Add_Fields.SBHD, CHOOSE_Add_Fields.BCN, CHOOSE_Add_Fields.SA_SX, CHOOSE_Add_Fields.AAA_UIC, CHOOSE_Add_Fields.ACRN, CHOOSE_Add_Fields.AMT, CHOOSE_Add_Fields.DOC_NUMBER, CHOOSE_Add_Fields.FIPC, CHOOSE_Add_Fields.REG_NUMB, CHOOSE_Add_Fields.TRAN_TYPE, CHOOSE_Add_Fields.DOV_NUM, CHOOSE_Add_Fields.PAA, CHOOSE_Add_Fields.COST_CODE, CHOOSE_Add_Fields.OBJ_CODE, CHOOSE_Add_Fields.EFY, CHOOSE_Add_Fields.REG_MO, CHOOSE_Add_Fields.RPT_MO, CHOOSE_Add_Fields.EFFEC_DATE, CHOOSE_Add_Fields.Orig_Sort, CHOOSE_Add_Fields.LTrim_BFY, CHOOSE_Add_Fields.LTrim_AAA, CHOOSE_Add_Fields.LTrim_REG, CHOOSE_Add_Fields.LTrim_DOV, CHOOSE_Add_Fields.AMT_Rev, CHOOSE_Add_Fields.LTrim_BFY & CHOOSE_Add_Fields.APPN_SYMB & CHOOSE_Add_Fields.SBHD & CHOOSE_Add_Fields.BCN & CHOOSE_Add_Fields.SA_SX & CHOOSE_Add_Fields.TRAN_TYPE & CHOOSE_Add_Fields.AMT_Rev AS CONCACT INTO CHOOSERev FROM CHOOSE_Add_Fields WHERE (((CHOOSE_Add_Fields.TRAN_TYPE) In ('1K','2D')) AND ((CHOOSE_Add_Fields.LTrim_REG)'7')); I am not sure what my web browser did to the above code; that said, it is strictly copy/paste "bcap" wrote: There is no way to take a recordset and save it as a table in one command. You would need to first create a new, empty table, and then iterate through the recordset saving each record in turn. But, this is completely unnecessary. What you are saying is that this query works: SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7')) In which case, this code will work: dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))" There are three possible reasons why the query I gave you earlier didn't work: 1. There was a line wrap in your newsreader. In your code, every line except the last one should end with a line continuation character, an underscore. If this isn't the case, then use the VBA editor to delete the spurious linewraps caused by your newsreader. 2. The query you are constructing in VBA is just plain different to the one that works when you run it in the SQL window. Please make sure that the query in the SQL window still works (without prompting for parameters), and then post it's SQL. 3. When you ran the query in the SQL window, you erroneously did so in Tools.mdb, not in Recon.mdb, with the result that the query in the SQL window and the query in the VBA are running against *different tables* in *different databases*. Finally, I'm concerned that you say "I have set the Currentdb to "Recon.mdb"". This is impossible: CurrentDb is not an object variable, it is a method, and you cannot set it to anything. It might be a good idea for you to show us the code you are using to create and set the object variable "dbs". wrote in message .. . Same error! error (#3601): Too few parameters. Expected 1" Trust me I am, at least, just as frustrated. What I believe is that the issue is so obvious that we are all walking right past it. Folks, please do not give up! **************** Please Continue Below *************************************** The VBA module from which this VBA is invoked, is in "Tools.mdb" While I am in Tools.mdb, I have set the Currentdb to "Recon.mdb" In Recon.mdb, I have previously created a table "ChooseData" I want to use Recon.mdb's "ChooseData" table to create a new table called "ChooseRev" in Recon.mdb !!!! PROGRESS !!!! NOTE: The following seems to create something (it does not bomb!) Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a solution? If it produces CHOOSERev, a "RecordSet" then what command can I use to save it in Recon.mdb (alias dbs and not in Tools.mdb?) EagleOne "bcap" wrote: dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev FROM CHOOSEData WHERE " & _ & "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" |
#30
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Larry,
I know that I am out on a long limb. I have no idea where I am at in ADP, ADODB or SQL Server or even plain ol'e Access! I am just a everyday accountant trying to accomplish a task. Yes, I am grabbing at code pieces I get from the Web. Actually, I have gotten quite far. I am 95% through this "Project." What I need is a solution as to why I cannot seem to use this dbs.Execute command. We all learn while doing. Thanks "Larry Linson" wrote: I don't know what you are trying to accomplish, but if the Project you are talking about is an Access ADP, they don't support internal Tables, they use Tables from SQL Server (or, if you go back to the proper back-level of ADODB, an external Access MDB). If you are asking questions about an ADP Project, it's a really good idea to say so, up front... If not, I don't understand what you mean by your statement. Larry Linson Microsoft Office Access MVP wrote in message .. . I made all of your suggested changes and I still get the same error. A Da! epiphany may have occured in my mind. I bet that a table is a RecordSet in a Project? No? If so, Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a solution? To be more clear: The VBA module is in "Tools.mdb" So I am in Tools.mdb and have set currentdb to "Recon.mdb" In Recon.mdb I have previously created a table "ChooseData" I want to use "ChooseData" to create a new "ChooseRev" table in Recon.mdb Then I close Recon.mdb and stay in "Tools.mdb" to create another Recon.mdb as Recon2.mdb. Any other thoughts? What about John Spencer wrote: Try replacing Chr(10) with VbCRLF or with Chr(13) & Chr(10) dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & vbcrlf & "FROM CHOOSEData" & vbcrlf _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" '============================================== ====== John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '============================================== ====== wrote: 2003 The following code works in the Access Query SQL-view window but when I try to run it in VBA, it produces the following error (#3601): "Too few parameters. Expected 1" dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & Chr(10) & "FROM CHOOSEData" & Chr(10) _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" I copied the Access query which works! exactly from the SQL window (Except I had to parse the lines with &, " etc. I guess the syntax I am using is incorrect after the .Execute command. (Note the . INTO CHOOSERev 10th line Any help greatly appreciated! EagleOne |
Thread Tools | |
Display Modes | |
|
|