The length of my query is well over 255 characters and I get an
automation error trying to run the query. Any suggestions?

Thank you for your time.

Steve

I should have been more specific. I am querying an ODBC database and the
SQL query is too long.


John Bundy wrote:
There are a couple ways to do this if the first doesn't work but I use this
method, adapted from Microsoft.
Sub URL_Get_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;
http://www.moto-net.com/images/nouveautes2006/suzuki/bandit1200_static.jpg", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

Steve

Can you post your current code? I seem to remember that you can pass
longer SQL statements to a querytable object if you create the sql in
an array ie:

strSQL = Array("SELECT blah,blah, blah, blah")

Richard


steveh wrote:
I should have been more specific. I am querying an ODBC database and the
SQL query is too long.


John Bundy wrote:
There are a couple ways to do this if the first doesn't work but I use this
method, adapted from Microsoft.
Sub URL_Get_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;
http://www.moto-net.com/images/nouveautes2006/suzuki/bandit1200_static.jpg", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

Richard, I saw that somewhere as well, but I'm not sure how to pass the
array. My code chokes if it is an array instead of a String.

Here's the pertinent code:

Dim conn As ADODB.Connection
Dim recset As ADODB.Recordset
Dim sqlArray() As String
Dim sqlText As String

Set recset = New ADODB.Recordset
With recset
.ActiveConnection = conn
.Open sqlText ' automation error; if sqlArray run-time error
Sheet1.Range("A2").CopyFromRecordset recset
.Close
End With

How would I pass an array?

Also, and maybe I should start another thread, but do you know how to
retrieve the field (column) names as part of the recordset?

Thanks,
Steve


RichardSchollar wrote:
Steve

Can you post your current code? I seem to remember that you can pass
longer SQL statements to a querytable object if you create the sql in
an array ie:

strSQL = Array("SELECT blah,blah, blah, blah")

Richard


steveh wrote:
I should have been more specific. I am querying an ODBC database and the
SQL query is too long.


John Bundy wrote:
There are a couple ways to do this if the first doesn't work but I use this
method, adapted from Microsoft.
Sub URL_Get_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;
http://www.moto-net.com/images/nouveautes2006/suzuki/bandit1200_static.jpg", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

I fathom that the 255 limit depends on your database and your database
driver.

I use ODBC with DB2 and execute queries having over 3,000 characters.
All the time. No problems.

As to getting field names:

Dim rs As ADODB.Recordset
Set rs = command.Execute
Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field

Steve

It's not an area I have much experience with, but I think I was
incorrect with the array idea (it works with querytable objects, but
not seemingly with ADO recordset creation).

It is possible that you may get round the limit on the recordset SQL by
creating it from a Command Object. Give the following a try (this is
what I used for a sample Access Db on my harddrive, obviously you'll
have to amend to suit your specific circumstances):

Sub impo()
Dim com As ADODB.Command
Dim rst As ADODB.Recordset
Dim sqlText As String

sqlText = "SELECT * FROM Table1;"
Set com = New ADODB.Command
With com
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=C:\Documents and
Settings\Richard Schollar\My Documents\" _
& "db1.mdb;User Id=admin;Password=;"
.CommandText = sqlText
.CommandType = adCmdText
End With
Set rst = com.Execute
With ActiveSheet
.Cells.ClearContents
For i = 1 To rst.Fields.Count
.Cells(1, i) = rst.Fields(i - 1).Name
Next
.Cells(2, 1).CopyFromRecordset rst
End With
End Sub

I hope this may be of some help!

Richard

How would I pass an array?

Also, and maybe I should start another thread, but do you know how to
retrieve the field (column) names as part of the recordset?

Thanks,
Steve


RichardSchollar wrote:
Steve

Can you post your current code? I seem to remember that you can pass
longer SQL statements to a querytable object if you create the sql in
an array ie:

strSQL = Array("SELECT blah,blah, blah, blah")

Richard


steveh wrote:
I should have been more specific. I am querying an ODBC database and the
SQL query is too long.


John Bundy wrote:
There are a couple ways to do this if the first doesn't work but I use this
method, adapted from Microsoft.
Sub URL_Get_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;
http://www.moto-net.com/images/nouveautes2006/suzuki/bandit1200_static.jpg", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub