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

I notice that you have posted again with a missing space at...

-----& Chr(10) & "FROM CHOOSEData" & Chr(10) _-----

Change it to ....

& Chr(10) & " FROM CHOOSEData" & Chr(10) _
-------------------^ missing space here.......................

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

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

PS. I think "Chr(10)" should also include a space before this.

& " INTO CHOOSERev" _
& " " & Chr(10) & " FROM CHOOSEData" & Chr(10) _
----^---^-----------------^------

wrote:
& " INTO CHOOSERev" _
& Chr(10) & "FROM CHOOSEData" & Chr(10) _

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

Urg!!!!

& " INTO CHOOSERev" _
& " " & Chr(10) & " FROM CHOOSEData " & Chr(10) _
----^---^-----------------^-----------------------------^ a space here----

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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

"bcap" wrote in
:
wrote in message

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

The length of those properties is several times larger than 255
characters.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

wrote in
:

'
With appAccess.CurrentDb
.Execute "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 " & Chr(10) _
& "FROM CHOOSEData" & Chr(10) _
& " WHERE (((TRAN_TYPE) In ('1K','2D')) And
((LTrim_REG)'7'));"
End With


The key question:

- where is the source data?

If it's not in the new database, then you can't do it via the method
you're using.

Also, your code is terribly confused, with unused recordsets and a
database variable that is set to appAccess.CurrentDB at the same
time that you use appAccess.CurrentDB directly.

What you need, I think, is the ability to run a query in one MDB and
append its results to a table in a different MDB. You don't need to
initialize an entirely new instance of Access.

My suggestion would be something like this:

myPath = "C:\Access"
dbsfilename = "Recon.mdb"
Set dbs = DBEngine.OpenDatabase myPath & dbsfilename
strSQL = "SELECT ..."
strSQL = strSQL & " FROM CHOOSEData IN 'C:\Access\Tool.mdb'"
strSQL = strSQL & " WHERE ..."
dbs.Execute strSQL, dbFailOnError

What this does is use your Recon.mdb (the destination MDB) as the
operating database, but runs a query on Recon.mdb that will draw
data from Tool.mdb.

I think this is what you need here. It's much, much simpler than the
code you posted.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

wrote in
:

The original poster is learning. We all started from zero.
Please consider that fact and consider posting a solution instead
of perpetuating an out-of-control flammer.

Aaron's response was not at all relevant to your question.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thank you for your time and thoughts!


"David W. Fenton" wrote:

wrote in
:

'
With appAccess.CurrentDb
.Execute "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 " & Chr(10) _
& "FROM CHOOSEData" & Chr(10) _
& " WHERE (((TRAN_TYPE) In ('1K','2D')) And
((LTrim_REG)'7'));"
End With


The key question:

- where is the source data?

If it's not in the new database, then you can't do it via the method
you're using.

Also, your code is terribly confused, with unused recordsets and a
database variable that is set to appAccess.CurrentDB at the same
time that you use appAccess.CurrentDB directly.

What you need, I think, is the ability to run a query in one MDB and
append its results to a table in a different MDB. You don't need to
initialize an entirely new instance of Access.

My suggestion would be something like this:

myPath = "C:\Access"
dbsfilename = "Recon.mdb"
Set dbs = DBEngine.OpenDatabase myPath & dbsfilename
strSQL = "SELECT ..."
strSQL = strSQL & " FROM CHOOSEData IN 'C:\Access\Tool.mdb'"
strSQL = strSQL & " WHERE ..."
dbs.Execute strSQL, dbFailOnError

What this does is use your Recon.mdb (the destination MDB) as the
operating database, but runs a query on Recon.mdb that will draw
data from Tool.mdb.

I think this is what you need here. It's much, much simpler than the
code you posted.

The oddball spaces etc is coming from the web browser.

Thanks for your knowledge and time!

"AccessVandal via AccessMonster.com" u18947@uwe wrote:

Urg!!!!

& " INTO CHOOSERev" _
& " " & Chr(10) & " FROM CHOOSEData " & Chr(10) _
----^---^-----------------^-----------------------------^ a space here----