Microsoft Query in VBA

I just noticed this if it will help anyone explain what to do, but looks like the macro recorder only recorded part of the sql code from the microsoft query. Maybe it is too long?


Is it possible to split the sql code in 2 pcs and place back together at the
".commandText"

Thanks for looking, Mike

It appears to have missed this part of the code:

Please Login or Register  to view this content.

Try taxing the .CommandText and assigning it to a string variable. Then do .CommandText = StringVariable

Please Login or Register  to view this content.

Thanks for looking, however...

I'm getting an ODBC Error at this line of your script:

Please Login or Register  to view this content.

I have also tried this below: But I get an SQL Syntax Error.

The SQL Script works in the microsoft query wizard so not sure how to correct either error. I will verify the SQL script but i'm sure it works.

Also noticed something when I use the recorder and it brings over all kinds of extra stuff for example the CHR(13) & CHR(10) and seems to add "," to each line of code. This wasn't a problem till the script got larger.

Please Login or Register  to view this content.

Chr(10) and Chr(13) are just line breaks. You do not need them as part of the SQL statement. They merely ake it easier to read for the human eye. The "," is because it is creating an array out of your SQL statement. Each line in your original SQL statement gets a new row in the array. There is no reason for this. You can get rid of the commas and the Array() function.

For your code you just posted, make sure you include trailing spaces at the end of each line.

When you concatenate this
"ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3,SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY)"
with this
"WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY)ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX"

You get this

"ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3,SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY)WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY)ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX"

It looks like I had a similar issue with my last code, which may be the reason for the error on Refresh.

Yeah, I see what you mean. I added the spaces in to the second script and now that one gets the ODBC Error, But not the SQL Error.

I'll try to work with both scripts some to see if I can get something back.

Let me know if you have any more thoughts.

Thanks for the help.

Mike

Originally Posted by snb

In the method 'querytables.add' you use 4 arguments.
As far as I know it takes only 3.

This line came from the Macro Recorder

Please Login or Register  to view this content.

I added this

Please Login or Register  to view this content.

to try this method out:

Please Login or Register  to view this content.

But I could not get the sConn part to work for me.

Would you have any ideas?

Thank You, Mike

I believe Sql:=sSql) should be CommandText := sSql

I could be wrong.

I got this working it was just a manner of putting the spaces in the right places and or adding some.

Much like whiz said...

For anyone doing projects like this I found it easier to do my sql outside the VBA editor and then copy it back into the script. I also did a small bit of code at a time till it all worked correcly.

Any Thanks All for the help...

Mike

Please Login or Register  to view this content.