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

"bcap" wrote:

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

Amen and amen, brother.

Except you may be crediting him with too much intelligence. In this very
thread, he's whining about people "blaming" him for errors, even though no
one's done anything remotely resembling that.

The only blame that is attached to him is that he wrongly advises people,
almost every post, and there are likely an infinitesimally-small percentage
who actually follow his advice, and then blame it on the software.

He challenges someone who consistently gives helpful answers, some of which
have to point out erroneous answers by aaron, to show him what he's said that
is wrong. But the real challenge would be to identify that tiny percentage of
aaron's posts where he's said something that is correct.

Anony Mous

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

you don't need 3 tiers of tables.

keep data where it belongs- on a db server

Yep, as dcap says, "Aaron is an idiot, a liar, and a convicted criminal."

Nobody's suggested "3 tiers of tables." Nothing in this thread, except
aaron's rants, raves, and babbling had anything to do with "tiers of tables".
But using both local and remote (including server) tables can improve
performance.

Oh, and sorry, spoke too soon about eliminating this thread... guess it was
just the "online interface screwing up", not "the wimps protecting aaron from
his own blunders".

Anony Mous

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

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

Except for raving idiots (or even lower intelligence) who know nothing about
database, the answer to Jet problems is _almost never_ to "move away from
Jet", and, when it is, that move only sometimes should be to SQL Server. But,
this numbskull knows no other solution to any problem.

Somebody said that was because he worked in the marketing mailroom in Remond
before they "ran his sorry ass off" and all he did all day long was read the
SQL Server marketing material. But, just as likely, he was on the street,
unwashed, unloved, homeless, and unwanted, and crawled in Microsoft's
dumpster, covered up with that marketing material, and read it because he had
nothing else to do.

Bcap's got him figured! Look up any of bcap's responses to him.

I had been compacting/repairing. That said, the database Tools.mdb did get hammered numerous time
during development. I did not think of starting from scratch and importing modules. Great idea.
Thanks for considering this challenge, your time and knowledge. EagleOne

"Larry Linson" wrote:

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

What an excellent idea! Thanks


Andrew Thompson wrote:

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/

I appreciate all the time that all provided. The solution was/not what your were suspecting.
Believe me, I had intent nor purpose to mislead in anyway.

The issue was database corruption period - even though it was compacted/repaired multiple time
"whether it needed it or not."

After hearing multiple times about my code which was never going to work; was FUBARed; etc, the
inverse was true as it works extremely well. I did get some excellent information but the solutions
(excluding the corruption) should have been simple to even a modest Access user - I am admittedly
new . Yet, the hazing and brow-beating inhibited many advice-givers from seeing and/or
communicating solutions which were low-hanging fruit.


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

YOU MVP DORKS are defending 3 tiers-- if not FOUR tiers of Jet
databases.

a) one for lookup tables
b) one for big tables
c) one for temporary tables
d) one for front end.

Personally-- I think that having FOUR MDB files-- or ONE ADP? I choose
ONE ADP any day of the week.

-Aaron





On Aug 11, 11:43*am, posted_by_anonymous
wrote:
"a a r o n . k e m p f @ g m a i l . c o" wrote:

you don't need 3 tiers of tables.

keep data where it belongs- on a db server

Yep, as dcap says, "Aaron is an idiot, a liar, and a convicted criminal."

Nobody's suggested "3 tiers of tables." *Nothing in this thread, except
aaron's rants, raves, and babbling had anything to do with "tiers of tables".
But using both local and remote (including server) tables can improve
performance.

Oh, and sorry, spoke too soon about eliminating this thread... guess it was
just the "online interface screwing up", not "the wimps protecting aaron from
his own blunders".

*Anony Mous

PERIOD. IF YOUR DATABASE CORRUPTS. PERIOD.
MOVE TO SQL SERVER.
PERIOD






On Aug 11, 1:38*pm, wrote:
I appreciate all the time that all provided. *The solution was/not what your were suspecting.
Believe me, I had intent nor purpose to mislead in anyway.

The issue was database corruption period - even though it was compacted/repaired multiple time
"whether it needed it or not."

After hearing multiple times about my code which was never going to work; was FUBARed; etc, the
inverse was true as it works extremely well. *I did get some excellent information but the solutions
(excluding the corruption) should have been simple to even a modest Access user - I am admittedly
new . *Yet, the hazing and brow-beating inhibited many advice-givers from seeing and/or
communicating solutions which were low-hanging fruit.

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

1. Your Execute statement as you originally posted it could not possibly
have worked. If it works now it's because you have corrected several errors
as pointed out by various posters.

2. The more extensive code you later posted was, as was pointed out to you,
riddled with mistakes and poor practice. This is not to say that such
mistakes would have prevented it from running, but the mere fact that it
runs should not lead you to think you are going in the right direction; you
would be well advised to study the suggestions you have been given and to
learn from them.

3. You wasted a lot of peoples' time by claiming that you had a working
query and you were simply struggling to generate the same SQL from VBA,
whereas in reality the working SQL you had was *very different* to the SQL
you were trying to generate in VBA.

3. Whilst I suppose it's possible that a database corruption was preventing
your query from executing, I find it very hard to believe. If it's working
now, it's most likely that you have changed something in the query or
elsewhere (possibly without realising the effect of your change).

This is the second time to my knowledge that you have attacked people in
this newsgroup who have *volunteered* to try to help you, and on both
occasions the fundamental problem has been your inability or unwillingness
to express yourself clearly and to post accurate and complete information.

I'm afraid you'll be getting no future help from me. Goodbye.

wrote in message
news
I appreciate all the time that all provided. The solution was/not what
your were suspecting.
Believe me, I had intent nor purpose to mislead in anyway.

The issue was database corruption period - even though it was
compacted/repaired multiple time
"whether it needed it or not."

After hearing multiple times about my code which was never going to work;
was FUBARed; etc, the
inverse was true as it works extremely well. I did get some excellent
information but the solutions
(excluding the corruption) should have been simple to even a modest Access
user - I am admittedly
new . Yet, the hazing and brow-beating inhibited many advice-givers from
seeing and/or
communicating solutions which were low-hanging fruit.

you the kid says he has db corruption.
you don't believe him?

you're such a loser, bcap.

if your database corrupts-- move to a real database.

PERIOD.




On Aug 11, 10:48*pm, "bcap" wrote:
1. Your Execute statement as you originally posted it could not possibly
have worked. *If it works now it's because you have corrected several errors
as pointed out by various posters.

2. The more extensive code you later posted was, as was pointed out to you,
riddled with mistakes and poor practice. *This is not to say that such
mistakes would have prevented it from running, but the mere fact that it
runs should not lead you to think you are going in the right direction; you
would be well advised to study the suggestions you have been given and to
learn from them.

3. You wasted a lot of peoples' time by claiming that you had a working
query and you were simply struggling to generate the same SQL from VBA,
whereas in reality the working SQL you had was *very different* to the SQL
you were trying to generate in VBA.

3. Whilst I suppose it's possible that a database corruption was preventing
your query from executing, I find it very hard to believe. *If it's working
now, it's most likely that you have changed something in the query or
elsewhere (possibly without realising the effect of your change).

This is the second time to my knowledge that you have attacked people in
this newsgroup who have *volunteered* to try to help you, and on both
occasions the fundamental problem has been your inability or unwillingness
to express yourself clearly and to post accurate and complete information..

I'm afraid you'll be getting no future help from me. *Goodbye.

wrote in message

news
I appreciate all the time that all provided. *The solution was/not what
your were suspecting.
Believe me, I had intent nor purpose to mislead in anyway.

The issue was database corruption period - even though it was
compacted/repaired multiple time
"whether it needed it or not."

After hearing multiple times about my code which was never going to work;
was FUBARed; etc, the
inverse was true as it works extremely well. *I did get some excellent
information but the solutions
(excluding the corruption) should have been simple to even a modest Access
user - I am admittedly
new . *Yet, the hazing and brow-beating inhibited many advice-givers from
seeing and/or
communicating solutions which were low-hanging fruit.