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

Excellent idea! I'll post the results tomorrow morning.

"David W. Fenton" wrote:

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.

Very fair and logical.

This is an Access.mdb issue.

I have been able to get "*" to work, whereas the detailed fields list did not work.

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.


"Larry Linson" wrote:

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

you don't need 3 tiers of tables.

keep data where it belongs- on a db server


On Aug 10, 4:24*pm, "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

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

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

Ignore Kempf, he is an idiot, a liar, and a convicted criminal.

again-- if your so called 'query engine' had a decent debugger.

honestly at this point; I would just put the SQL statement in query
analyzer and it would give me a real error message.
It must really suck to be stuck with JET and not get error messages in
plain english

I just love how SQL Server will TELL YOU WHAT PART OF THE SYNTAX IS
WRONG instead of just throwing up about a parameter incorrect



On Aug 10, 11:04*pm, "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'));"

correction-- the answer to ALL jet problems is to move away from JET.

SQL Server gives you a real query engine- with real error messages
Don't hate the messenger-- but JET just doesn't spit out logical error
messages very often




On Aug 10, 4:35*pm, Please Learn to Read
wrote:
Please learn to read. The original question was about an SQL statement
executed from VBA. It did not remotely resemble a question asking what
database engine to use. You will find basic literacy training is available at
no or low cost from local welfare and educational organizations for
illiterate individuals, as you clearly seem to be.

That's not to excuse the original poster for beginning vast projects with
half-vast understanding of his tools, but the answer to his question is not
to convert to a different database engine, either local or remote,
file-server or server.

Sorry, but THESE are the facts.

*2008 Learn-to-Read Campaign

"a a r o n . k e m p f @ g m a i l . c o" wrote:

maybe you should use a database engine with a future?

SQL Server typically has _MUCH_ better errors returned.. from the
debug message that you are seeing it is hard to determine what is
really happening.
yet another reason to move to SQL Server--

(you could capture via SQL Profiler and then compare the real strings
if you needed to)

Soudns to me like you're just constantly frustrated by the limitations
of JET.
Do I really need to say it here?

MOVE TO SQL SERVER, THINGS JUST WORK.
You would have gotten a real error message if you used SQL Server.

Sorry-- those are the facts

-Aaron

Ignore Kempf, he is an idiot, a liar, and a convicted criminal.

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/

--
Andrew Thompson
http://pscode.org/