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 |
#51
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
"bcap" wrote:
Ignore Kempf, he is an idiot, a liar, and a convicted criminal. Amen and amen, brother. Except you may be crediting him with too much intelligence. In this very thread, he's whining about people "blaming" him for errors, even though no one's done anything remotely resembling that. The only blame that is attached to him is that he wrongly advises people, almost every post, and there are likely an infinitesimally-small percentage who actually follow his advice, and then blame it on the software. He challenges someone who consistently gives helpful answers, some of which have to point out erroneous answers by aaron, to show him what he's said that is wrong. But the real challenge would be to identify that tiny percentage of aaron's posts where he's said something that is correct. Anony Mous |
#52
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
"a a r o n . k e m p f @ g m a i l . c o" wrote:
you don't need 3 tiers of tables. keep data where it belongs- on a db server Yep, as dcap says, "Aaron is an idiot, a liar, and a convicted criminal." Nobody's suggested "3 tiers of tables." Nothing in this thread, except aaron's rants, raves, and babbling had anything to do with "tiers of tables". But using both local and remote (including server) tables can improve performance. Oh, and sorry, spoke too soon about eliminating this thread... guess it was just the "online interface screwing up", not "the wimps protecting aaron from his own blunders". Anony Mous |
#53
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
"a a r o n . k e m p f @ g m a i l . c o" wrote:
correction-- the answer to ALL jet problems is to move away from JET. Except for raving idiots (or even lower intelligence) who know nothing about database, the answer to Jet problems is _almost never_ to "move away from Jet", and, when it is, that move only sometimes should be to SQL Server. But, this numbskull knows no other solution to any problem. Somebody said that was because he worked in the marketing mailroom in Remond before they "ran his sorry ass off" and all he did all day long was read the SQL Server marketing material. But, just as likely, he was on the street, unwashed, unloved, homeless, and unwanted, and crawled in Microsoft's dumpster, covered up with that marketing material, and read it because he had nothing else to do. Bcap's got him figured! Look up any of bcap's responses to him. |
#54
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
I had been compacting/repairing. That said, the database Tools.mdb did get hammered numerous time
during development. I did not think of starting from scratch and importing modules. Great idea. Thanks for considering this challenge, your time and knowledge. EagleOne "Larry Linson" wrote: 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 |
#55
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
What an excellent idea! Thanks
Andrew Thompson wrote: 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/ |
#56
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
I appreciate all the time that all provided. The solution was/not what your were suspecting.
Believe me, I had intent nor purpose to mislead in anyway. The issue was database corruption period - even though it was compacted/repaired multiple time "whether it needed it or not." After hearing multiple times about my code which was never going to work; was FUBARed; etc, the inverse was true as it works extremely well. I did get some excellent information but the solutions (excluding the corruption) should have been simple to even a modest Access user - I am admittedly new . Yet, the hazing and brow-beating inhibited many advice-givers from seeing and/or communicating solutions which were low-hanging fruit. "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'));" |
#57
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
YOU MVP DORKS are defending 3 tiers-- if not FOUR tiers of Jet
databases. a) one for lookup tables b) one for big tables c) one for temporary tables d) one for front end. Personally-- I think that having FOUR MDB files-- or ONE ADP? I choose ONE ADP any day of the week. -Aaron On Aug 11, 11:43*am, posted_by_anonymous wrote: "a a r o n . k e m p f @ g m a i l . c o" wrote: you don't need 3 tiers of tables. keep data where it belongs- on a db server Yep, as dcap says, "Aaron is an idiot, a liar, and a convicted criminal." Nobody's suggested "3 tiers of tables." *Nothing in this thread, except aaron's rants, raves, and babbling had anything to do with "tiers of tables". But using both local and remote (including server) tables can improve performance. Oh, and sorry, spoke too soon about eliminating this thread... guess it was just the "online interface screwing up", not "the wimps protecting aaron from his own blunders". *Anony Mous |
#58
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
PERIOD. IF YOUR DATABASE CORRUPTS. PERIOD.
MOVE TO SQL SERVER. PERIOD On Aug 11, 1:38*pm, wrote: I appreciate all the time that all provided. *The solution was/not what your were suspecting. Believe me, I had intent nor purpose to mislead in anyway. The issue was database corruption period - even though it was compacted/repaired multiple time "whether it needed it or not." After hearing multiple times about my code which was never going to work; was FUBARed; etc, the inverse was true as it works extremely well. *I did get some excellent information but the solutions (excluding the corruption) should have been simple to even a modest Access user - I am admittedly new . *Yet, the hazing and brow-beating inhibited many advice-givers from seeing and/or communicating solutions which were low-hanging fruit. "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'));" |
#59
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
1. Your Execute statement as you originally posted it could not possibly
have worked. If it works now it's because you have corrected several errors as pointed out by various posters. 2. The more extensive code you later posted was, as was pointed out to you, riddled with mistakes and poor practice. This is not to say that such mistakes would have prevented it from running, but the mere fact that it runs should not lead you to think you are going in the right direction; you would be well advised to study the suggestions you have been given and to learn from them. 3. You wasted a lot of peoples' time by claiming that you had a working query and you were simply struggling to generate the same SQL from VBA, whereas in reality the working SQL you had was *very different* to the SQL you were trying to generate in VBA. 3. Whilst I suppose it's possible that a database corruption was preventing your query from executing, I find it very hard to believe. If it's working now, it's most likely that you have changed something in the query or elsewhere (possibly without realising the effect of your change). This is the second time to my knowledge that you have attacked people in this newsgroup who have *volunteered* to try to help you, and on both occasions the fundamental problem has been your inability or unwillingness to express yourself clearly and to post accurate and complete information. I'm afraid you'll be getting no future help from me. Goodbye. wrote in message news I appreciate all the time that all provided. The solution was/not what your were suspecting. Believe me, I had intent nor purpose to mislead in anyway. The issue was database corruption period - even though it was compacted/repaired multiple time "whether it needed it or not." After hearing multiple times about my code which was never going to work; was FUBARed; etc, the inverse was true as it works extremely well. I did get some excellent information but the solutions (excluding the corruption) should have been simple to even a modest Access user - I am admittedly new . Yet, the hazing and brow-beating inhibited many advice-givers from seeing and/or communicating solutions which were low-hanging fruit. |
#60
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
you the kid says he has db corruption.
you don't believe him? you're such a loser, bcap. if your database corrupts-- move to a real database. PERIOD. On Aug 11, 10:48*pm, "bcap" wrote: 1. Your Execute statement as you originally posted it could not possibly have worked. *If it works now it's because you have corrected several errors as pointed out by various posters. 2. The more extensive code you later posted was, as was pointed out to you, riddled with mistakes and poor practice. *This is not to say that such mistakes would have prevented it from running, but the mere fact that it runs should not lead you to think you are going in the right direction; you would be well advised to study the suggestions you have been given and to learn from them. 3. You wasted a lot of peoples' time by claiming that you had a working query and you were simply struggling to generate the same SQL from VBA, whereas in reality the working SQL you had was *very different* to the SQL you were trying to generate in VBA. 3. Whilst I suppose it's possible that a database corruption was preventing your query from executing, I find it very hard to believe. *If it's working now, it's most likely that you have changed something in the query or elsewhere (possibly without realising the effect of your change). This is the second time to my knowledge that you have attacked people in this newsgroup who have *volunteered* to try to help you, and on both occasions the fundamental problem has been your inability or unwillingness to express yourself clearly and to post accurate and complete information.. I'm afraid you'll be getting no future help from me. *Goodbye. wrote in message news I appreciate all the time that all provided. *The solution was/not what your were suspecting. Believe me, I had intent nor purpose to mislead in anyway. The issue was database corruption period - even though it was compacted/repaired multiple time "whether it needed it or not." After hearing multiple times about my code which was never going to work; was FUBARed; etc, the inverse was true as it works extremely well. *I did get some excellent information but the solutions (excluding the corruption) should have been simple to even a modest Access user - I am admittedly new . *Yet, the hazing and brow-beating inhibited many advice-givers from seeing and/or communicating solutions which were low-hanging fruit. |
Thread Tools | |
Display Modes | |
|
|