|
| 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 |
|
#1
August 10th, 2008, 04:56 PM
posted to microsoft.public.access
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
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 
|
|
#2
August 10th, 2008, 05:01 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
|
|
#3
August 10th, 2008, 05:30 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
On Aug 10, 9:01*am, wrote:
Bottom line, I am attempting to create, in "dbs" project, a new table CHOOSERev from CHOOSEData 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- Hide quoted text - - Show quoted text - Sometimes Access gives strange error messages. I would look at the following: --There is no space after " INTO CHOOSERev". --There is a line-feed character before and after "FROM CHOOSEData". --There is a semi-colon embedded in the sql string. --There is a limit to the length of an sql statement and you are wasting part of that limited number of characters by repeating "CHOOSEDATA.". ( I think the limit is 255 characters )
|
|
#5
August 10th, 2008, 05:48 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
|
|
#6
August 10th, 2008, 05:57 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
On Sun, 10 Aug 2008 11:48:32 -0500, Michael Gramelspacher wrote:
" INTO [CHOOSERev] FROM [CHOOSEData]" & _ should be: " INTO [CHOOSERev] FROM [CHOOSEData] AS a" & _
|
|
#7
August 10th, 2008, 06:01 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
Louis,
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. wrote: On Aug 10, 9:01*am, wrote: Bottom line, I am attempting to create, in "dbs" project, a new table CHOOSERev from CHOOSEData 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- Hide quoted text - - Show quoted text - Sometimes Access gives strange error messages. I would look at the following: --There is no space after " INTO CHOOSERev". --There is a line-feed character before and after "FROM CHOOSEData". --There is a semi-colon embedded in the sql string. --There is a limit to the length of an sql statement and you are wasting part of that limited number of characters by repeating "CHOOSEDATA.". ( I think the limit is 255 characters )
|
|
#8
August 10th, 2008, 06:09 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
Mike,
Received an error that related to "a" as Could not find table "a" Thanks Michael Gramelspacher wrote: " INTO [CHOOSERev] FROM [CHOOSEData] AS a" & _
|
|
#9
August 10th, 2008, 06:11 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
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
|
|
#10
August 10th, 2008, 06:22 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL code works in Access SQL window but not in VBA SQL code
On Aug 10, 10:01*am, wrote:
Louis, 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. wrote: On Aug 10, 9:01*am, wrote: Bottom line, I am attempting to create, in "dbs" project, a new table CHOOSERev from CHOOSEData 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- Hide quoted text - - Show quoted text - Sometimes Access gives strange error messages. I would look at the following: * *--There is no space after " INTO CHOOSERev". * *--There is a line-feed character before and after "FROM CHOOSEData". * *--There is a semi-colon embedded in the sql string. * *--There is a limit to the length of an sql statement and you are wasting * * *part of that limited number of characters by repeating "CHOOSEDATA.". * * *( I think the limit is 255 characters )- Hide quoted text - - Show quoted text - OK. I remember. You want to copy a table from one database to another. You were given several suggestions as how to do this. You were not given the menu-driven suggestion: Open the receiving database. From the File Menu, choose Get External data. A dialog box will open. You want to import data from the sending database. Navigate in the file find dialog box until you see the originating database. Select it. See the table names in the database. Choose CHOOSEData. Click OK. CHOOSEDATA will appear in the receiving database if none had existed before. If a CHOOSEDATA exists, the table will appear named CHOOSEDATA1. In the Database Tables menu, highlight the "CHOOSEDATA" table and right mouse click. You will be allowed to rename the table to CHOOSERev. I am surprised the Execute method failed. The other option I can think of is to use the: DoCmd.RunSql sqlStmt But really, I think you are working too hard to copy a table from one MDB to another.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
