SQL string truncated at 255 characters

[Robert]

The following code works when the rs.Open string is less than 255
characters, but does not work when the sting is a complex query that
goes over 255 characters. Does anyone have a workaround or an
alternative solution?

Thanks in advance.


Dim db As Connection
Set db = New Connection

db.CursorLocation = adUseClient
db.Open "DBase", "userid", "password"
Set rs = New Recordset

rs.Open "SELECT * from sometable", db

Dim myRow
Dim myColumn

myRow = 1
myColumn = 1

Do Until rs.EOF
For Each fld In rs.Fields
Worksheets("Sheet1").Cells(myRow, myColumn).Value = fld.Value
myColumn = myColumn + 1
Next fld

myRow = myRow + 1
myColumn = 1
rs.MoveNext
Loop


Excel Templates Training & Add-ins

 

[onedaywhen]

Are you sure you've diagnosed the problem correctly? I know of no 255
character limit to the command text in DAO (nor ADO).

Perhaps you've made an error in your query? A query of more than 255
characters could be fairly complex. Post the query along with some
further details (table schema, sample data, etc).