Thread: SQL code works in Access SQL window but not in VBA SQL code
View Single Post
  #5  
Old August 10th, 2008, 05:48 PM posted to microsoft.public.access
Michael Gramelspacher
external usenet poster
  
Posts: 482
Default SQL code works in Access SQL window but not in VBA SQL code
On Sun, 10 Aug 2008 09:30:25 -0700 (PDT), 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 )


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'));"