A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SQL code works in Access SQL window but not in VBA SQL code



 
 
Thread Tools Display Modes
  #11  
Old August 10th, 2008, 06:28 PM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

Louis,

My goal is to do the task in VBA so it is repeatable and the "query" is imbedded in the VBA and not
in a Query which can be changed by other OPs.

If it were a One-time issue, you are correct.

Thanks EagleOne

wrote:

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.

  #12  
Old August 10th, 2008, 06:38 PM posted to microsoft.public.access
bcap[_2_]
external usenet poster
 
Posts: 260
Default SQL code works in Access SQL window but not in VBA SQL code

I guess it's Sunday and everyone's a bit sleepy, but you've been given some
pretty weird suggestions so far.

One problem seems blindingly obvious to me: I can't imagine why you'd want
to put a line-feed character in the middle of an SQL string. Replacing it
with vbCRLF is just as bizarre. Try this:

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



wrote in message
...
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



  #13  
Old August 10th, 2008, 06:42 PM posted to microsoft.public.access
bcap[_2_]
external usenet poster
 
Posts: 260
Default SQL code works in Access SQL window but not in VBA SQL code


wrote in message
...
On Aug 10, 9:01 am, 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" _
& Chr(10) & "FROM CHOOSEData" & Chr(10) _
& " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'));"



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".


This is the nub of the problem. All that is needed is a space before and
after.

--There is a semi-colon embedded in the sql string.


It's the statement terminator, which is fine. You don't have to put one,
but it's OK if you do.

--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 )


No, that limit only applies to an SQL string used as a RecordSource property
or RowSource property.



  #15  
Old August 10th, 2008, 06:47 PM posted to microsoft.public.access
bcap[_2_]
external usenet poster
 
Posts: 260
Default SQL code works in Access SQL window but not in VBA SQL code

Hi John,

The line feed character shouldn't be there at all (a linefeed in the middle
of an SQL statement???), adding a carriage return to it isn't going to help!

"John Spencer" wrote in message
...
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
'================================================= ===



  #17  
Old August 10th, 2008, 07:16 PM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 87
Default SQL code works in Access SQL window but not in VBA SQL code

On Aug 10, 10:47*am, "bcap" wrote:
Hi John,

The line feed character shouldn't be there at all (a linefeed in the middle
of an SQL statement???), adding a carriage return to it isn't going to help!

"John Spencer" wrote in message

...



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
'================================================= ===- Hide quoted text -


- Show quoted text -


bcap:

Sorry if I misled the group. The limitation on the length of a string
comes from the limitation on continuation characters.

Sub Main()

Dim sqlStmt As String

sqlStmt = String(80, "A") & _
String(80, "B") & _
String(80, "C") & _
String(80, "D") & _
String(80, "E") & _
String(80, "F") & _
String(80, "G") & _
String(80, "H") & _
String(80, "B") & _
String(80, "I") & _
String(80, "J") & _
String(80, "K") & _
String(80, "L") & _
String(80, "M") & _
String(80, "N") & _
String(80, "O") & _
String(80, "P") & _
String(80, "Q") & _
String(80, "R") & _
String(80, "S") & _
String(80, "T") & _
String(80, "U") & _
String(80, "V") & _
String(80, "W")


MsgBox (sqlStmt)
End Sub

Try to add an "X" string and an error will occur. My recollection
about the allowable length of an SqlStatement was faulty.
  #18  
Old August 10th, 2008, 07:34 PM posted to microsoft.public.access
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default SQL code works in Access SQL window but not in VBA SQL code

The Chr(10) line-feed characters serve no purpose here. Just use a space
character. As you are only returning columns from one table there is no need
to qualify the column names with the table names, so:

Dim dbs as DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

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

I'm assuming 'CONCACT' is not a typo.

Ken Sheridan
Stafford, England

" 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


  #19  
Old August 10th, 2008, 07:45 PM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

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

  #20  
Old August 10th, 2008, 07:58 PM posted to microsoft.public.access
[email protected]
external usenet poster
 
Posts: 308
Default SQL code works in Access SQL window but not in VBA SQL code

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.