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

[Larry Linson]

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

 

[bcap]

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.

 

[a a r o n . k e m p f]

yah no shit it's either

a) some form of corruption
b) jet query engine throwing a tissy because things get complex
c) some BS you kids blame on the network.

and you think that this is my fault?

you dance around a piece of crap database and then you blame it on ME
when it craps out?

grow up kids

 

[bcap]

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

 

[a a r o n . k e m p f]

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

 

[a a r o n . k e m p f]

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

 

[bcap]

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

 

[Andrew Thompson]

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

 

[posted_by_anonymous]

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

 

[posted_by_anonymous]

:

Looka dat -- somebody in Redmond is protecting Little Aaron's tender
sensibilities again, lest somebody correct his errors and offend him in doing
so. They have eliminated an entire message thread, forgetting that those
messages are already distributed to mirrors around the world.

What a crybaby that aaron is, but what dorks they are to listen to him
whine. Poor aaron, all he has to do is be correct and be helpful and no one
will have need to correct him.

Anony Mous

 

[posted_by_anonymous]

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

 

[posted_by_anonymous]

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.

 

[EagleOne]

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

 

[EagleOne]

What an excellent idea! Thanks

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

 

[EagleOne]

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.

 

[a a r o n . k e m p f]

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

 

[a a r o n . k e m p f]

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

 

[bcap]

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.

 

[a a r o n . k e m p f]

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.

 

[bcap]

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