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 |
#41
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Excellent idea! I'll post the results tomorrow morning.
"David W. Fenton" wrote: wrote in : 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 Assign the SQL string to a variable (ignoring the word wrap from my post): Dim strSQL As String strSQL = "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'));" Debug.Print strSQL dbs.Execute strSQL, dbFailOnError The Debug statement will show you exactly what string is getting executed. Copy it and paste into SQL View of the QBE and try running it. Likely it will fail and then you can figure out what's wrong. |
#42
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Very fair and logical.
This is an Access.mdb issue. I have been able to get "*" to work, whereas the detailed fields list did not work. Exclusive of length of the strSQL string (not the real issue) the only other issue is some hidden character(s) in the string. That said, the working Access SQL-view information was used after copy paste in/out of the VBA Editor. "Larry Linson" wrote: Access, and Microsoft, aren't as consistent as we might like in use of the term "Project", which is why I asked. When you close Access, what is the file extension of the file in which your datbase is saved? Windows explorer, etc., may hide that information from you, so you may need to go the the menu and choose to see it. The reason for asking is that the ADP "Project" is quite a different thing from an Access MDB "Project". Early on, 'Softies were using "Access Data Project" for ADP, but someone in Redmond, in their wisdom, decided just "Project" would sound better. Of course, the Access documentation was already riddled with uses of "project" in a generic term referring to MDB databases. The MDB (or, the newer ACCDB of Access 2007) actually contains Tables. An ADP uses tables in an external database, SQL Server, or (at least in early versions an external MDB with an appropriate ADODB data provider). The same approach does not work for both of them, so just copying snippets of code or SQL without knowing where and how they are intended to be used may not work (at least not until you do a lot of sorting-out, later) for you. Unfortunately, as you have seen, remote debugging is, at its best, problematic. Without appropriate information, it is impossible. That's why so many have asked you for so many clarifications; we have to rely on you to see, read, and tell us, because, of course, we can't see your database to determine those things for ourselves. I point out that "learning by doing" can sometimes be the equivalent of "self-flagellation" and it is often better to invest some time and effort "learning by reading". A couple of really good books, depending on the Access version you are using, are (1) Microsoft Access 2003 Inside Out, by John Viescas, and (2) Microsoft Access 2007 Inside Out, by John Viescas and Jeff Conrad, both published by Microsoft Press (or maybe, now, "Microsoft Learning"?). Larry Linson Microsoft Office Access MVP wrote in message .. . 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 |
#43
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
you don't need 3 tiers of tables.
keep data where it belongs- on a db server On Aug 10, 4:24*pm, "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 |
#44
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
wrote
Very fair and logical. This is an Access.mdb issue. Good, that'll be easier for most of us to help with... there were some strong advocates, including aaron kempf, of ADPs, but most of us saw there was no significant advantage, and stuck with MDBs (and MDEs). I have been able to get "*" to work, whereas the detailed fields list did not work. Hmm. Exclusive of length of the strSQL string (not the real issue) the only other issue is some hidden character(s) in the string. That said, the working Access SQL-view information was used after copy paste in/out of the VBA Editor. I'm trying to remember... there's a common cause for Access expecting paramters where there are none... maybe it was database corruption. Maybe someone will jump in and clarify the most likely cause. If there is corruption, then you'll need to first try compact and repair. But you might also have to create a new DB, and import all the working objects into it. Larry Linson Microsoft Office Access MVP |
#45
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
You said in your original post that the query worked OK when you ran it from
the SQL window, but not when you ran it from VBA. However, now you have posted the query you are running from the SQL window, it is crystal clear that the two queries are *completely different*. They are selecting *completely different* fields from *completely different* tables. Quite obviously, in the query you are constructing in VBA, you have got a field name wrong - maybe it's a typo, or maybe it's a field which simply doesn't exist, but you have misled us all by leading us to believe that all you had done was to copy some SQL from the SQL window and tried to render the same SQL as a string in VBA. Howsoever, *none* of us can help you any further because none of us can see your tables or have any way of knowing which of your field names is wrong. David Fenton's advice elsewhere about using the debugger should enable you to quickly identify which is the incorrect field name, or you could simply eyeball your SQL alongside the source table design to see where you have gone wrong. Either way, it's up to you now. BTW, the advice you have been given previously about getting the spaces right and getting rid of those pointless line-feed characters still applies. wrote in message ... 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'));" |
#46
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Ignore Kempf, he is an idiot, a liar, and a convicted criminal.
|
#47
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
again-- if your so called 'query engine' had a decent debugger.
honestly at this point; I would just put the SQL statement in query analyzer and it would give me a real error message. It must really suck to be stuck with JET and not get error messages in plain english I just love how SQL Server will TELL YOU WHAT PART OF THE SYNTAX IS WRONG instead of just throwing up about a parameter incorrect On Aug 10, 11:04*pm, "bcap" wrote: You said in your original post that the query worked OK when you ran it from the SQL window, but not when you ran it from VBA. *However, now you have posted the query you are running from the SQL window, it is crystal clear that the two queries are *completely different*. *They are selecting *completely different* fields from *completely different* tables. Quite obviously, in the query you are constructing in VBA, you have got a field name wrong - maybe it's a typo, or maybe it's a field which simply doesn't exist, but you have misled us all by leading us to believe that all you had done was to copy some SQL from the SQL window and tried to render the same SQL as a string in VBA. *Howsoever, *none* of us can help you any further because none of us can see your tables or have any way of knowing which of your field names is wrong. David Fenton's advice elsewhere about using the debugger should enable you to quickly identify which is the incorrect field name, or you could simply eyeball your SQL alongside the source table design to see where you have gone wrong. *Either way, it's up to you now. BTW, the advice you have been given previously about getting the spaces right and getting rid of those pointless line-feed characters still applies. wrote in message ... 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'));" |
#48
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
correction-- the answer to ALL jet problems is to move away from JET.
SQL Server gives you a real query engine- with real error messages Don't hate the messenger-- but JET just doesn't spit out logical error messages very often On Aug 10, 4:35*pm, 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 |
#49
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Ignore Kempf, he is an idiot, a liar, and a convicted criminal.
|
#50
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
On Aug 11, 6:55*am, "bcap" wrote:
... There are three possible reasons why the query I gave you earlier didn't work: 1. * *There was a line wrap in your newsreader. *... "The Text Width Checker (TWC)* is a small tool that allows easy checking of the number of characters in lines of plain text. This can be useful when drafting to text only mediums such as usenet or web forums or when preparing text based documentation. " * http://pscode.org/twc/ -- Andrew Thompson http://pscode.org/ |
Thread Tools | |
Display Modes | |
|
|