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