ODBC excel Macro help needed

[Dan]

Can someone tell me what is wrong with this?

With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Commerce
Center;DATABASE=CommerceCenter;Trusted_Connection=Yes"))
.CommandText = Array( _
"SELECT *" _
, _
"" & Chr(13) & "" & Chr(10) & "" _
, _
"FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" _
)
.Refresh BackgroundQuery:=False
End With


I'm in an excel spreadsheet. I can edit the query and that works fine but
from within the macro I keep getting a "Run-time error '1004' -
Application-defined or object-defined error". I have shortened this query
down as much a possible but still get the error.

Dan

 

[Bill Renaud]

Why:

"SELECT *" _
, _
"" & Chr(13) & "" & Chr(10) & "" _
, _
"FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" _

instead of:

"SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast"

 

[Dan]

I don't know. You mean the "& Chr(13) & Chr(10) &"?
They represtent Carrage Return and Line Feed. That is only way that it
worked. I have to place then at the end of each line before another commmand
like FROM, WHERE, ORDER etc.

I don't even remember where I found this. Anyway I have probably 30+
spreadsheets that have macros that are calling queries from a SQL2000
database.

Dan

 

[Bill Renaud]

I have never seen Carriage Return and Line Feed embedded in the middle
of an SQL query before.
Anybody else know if this is legal syntax?

(There may be something else wrong with your overall code; I just
happened to spot something that stood out.)

 

[Dan]

I understand. And I am no programmer. At least not in 20 years but that is
the only way it will work.

This is wierd though. I just took one of my working macros and deleted just
one of the CR LF (the one after the SELECT stmnt) in a statement and it
worked. Then I deleted the other two (after FROM and WHERE stmnts) and I get
this error "Run-time error '1004': SQL Syntax Error"

By the way if there is a better way to do ODBC queries in a macro let me
know. Like the connection statement. Can I connect once to the database and
then just call the tables that I need?

Back to my original problem: Could something have changed in Excel 2007? I
save my work and got on my onter computer running Excel 2003 and it worked.
I finished up the entire program and saved it. Pulled it up in Excel 2007
and I got the error. Strange.

Dan

 

[urkec]

I understand. And I am no programmer. At least not in 20 years but that is
the only way it will work.

This is wierd though. I just took one of my working macros and deleted just
one of the CR LF (the one after the SELECT stmnt) in a statement and it
worked. Then I deleted the other two (after FROM and WHERE stmnts) and I get
this error "Run-time error '1004': SQL Syntax Error"

By the way if there is a better way to do ODBC queries in a macro let me
know. Like the connection statement. Can I connect once to the database and
then just call the tables that I need?

Back to my original problem: Could something have changed in Excel 2007? I
save my work and got on my onter computer running Excel 2003 and it worked.
I finished up the entire program and saved it. Pulled it up in Excel 2007
and I got the error. Strange.

What was the original error that made you insert Chr(13) & "" & Chr(10) & in
..CommandText? What did the Sql string look like before that?

Dan

Neither have I. I made some test using Excel 2007, Sql Server 2000 and 2005,
with and without array function, using Chr(13) & "" & Chr(10) & or vbCrLf, or
just setting CommandText to a single line string, and it all worked.

 

[Bill Renaud]

From reading Excel Help, my best guess is that your code should look
something more like the following:

With Selection.QueryTable
.Connection = "ODBC;DSN=Commerce
Center;DATABASE=CommerceCenter;Trusted_Connection=Yes"
.CommandText = "SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast
WHERE condition op value"
.Refresh BackgroundQuery:=False
End With

I don't understand why your original code has all the ARRAY functions in
it, as the values for the Connection and CommandText properties do not
require an array, but a simple text string.

Also, in your SQL query ("SELECT * ...") there probably should be a
condition clause somewhere, unless you want to return the entire table.
i.e. "WHERE Cost <= 100". It appears that you have listed the table name
twice.

HTH

 

[Bill Renaud]

As an afterthought, sometimes VBA won't let you use Selection or
ActiveCell to do things. You HAVE to use an object variable. The macro
recorder sometimes does not always record the exact code that you really
need to use.

Dim rngMyDataRange as Range

Set rngMyDataRange = Worksheets("Sheet1").Range("A1:B2")

With rngMyDataRange.QueryTable
....
End With

I don't know exactly what the range would have to be in this case,
whether you can just use the range of the upper-left corner of the data
area being used by the SQL query, or whether you need to specify the
entire worksheet, or entire data area, or what.

 

[Bill Renaud]

Dim qt As QueryTable

Set qt = Worksheets("Sheet1").QueryTable(1)

With qt
.Connection =
"ODBC;DSN=CommerceCenter;DATABASE=CommerceCenter;Trusted_Connection=Yes"
.CommandText = "SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast
WHERE condition op value"
.Refresh BackgroundQuery:=False
End With