Running a long Access SQL query from Excel 2007

Have you tried saving a parameter query in the Access db and then running that from the code?

I don't know how to do that. Is there a way to increase the maximum length of a String to something higher than 250, perhaps?

Have you tried creating a new Command object in code and setting its CommandText property to your SQL string?
The maximum length of a String variable is huge (about a billion characters) so that is not the problem.
I know there used to be an issue with the SQL property of query tables being limited to 255 characters, but I'm not aware of that applying to connection objects - how are you determining that the string gets truncated?

Have a look at the code below. I am breaking down the query into three strings: sSQL1, sSQL2, sSQL3, which I then concatenate into one string, sSQL. When I debug through the code I can see that all the characters after 250 are missing from sSQL, and I get an error when the (truncated) query is run.

Someone else reported a similar problem here, but I don't understand the problem they were having, or the solution proposed there. Could you help by applying that solution to my problem?

Sub Access_Data()
         Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
         Dim MyConn, sSQL, sSQL1, sSQL2, sSQL3 As String
         Dim Rw As Long, Col As Long, c As Long
         Dim MyField, DestCell As Range
          
         '~~> Set destination
         Set DestCell = [A3]
         
         '~~> Set Table Path
     
         MyConn = ActiveWorkbook.Path & "\DAX Option Data.accdb"
         
         '~~> Create query
     	 sSQL1 = "SELECT t1.Date,t1.Price, t1.IsPut, t2.TradingDate, t1.Value, t1.Volume FROM Options t1,"
         sSQL2 = "TradingDates t2, EndDates t3 WHERE t1.Endmonth = t2.Month And t1.EndYear = t2.Year And t2.Month = "
     	 sSQL3 = "t3.Month And t2.Year = t3.Year And t2.Day = t3.EndDay AND t1.Date= #" & Range("B1").Value & "#;"
     
         sSQL = sSQL1 & sSQL2 & sSQL3
         
          
         '~~> Create RecordSet
         Set Cn = New ADODB.Connection
         With Cn
             .Provider = "Microsoft.Ace.OLEDB.12.0"
             .Open MyConn
             Set Rs = .Execute(sSQL)
         End With
          
         '~~> Write RecordSet to A3
         Rw = DestCell.Row
         Col = DestCell.Column
         c = Col
         Do Until Rs.EOF
             For Each MyField In Rs.Fields
                 Cells(Rw, c) = MyField
                 c = c + 1
             Next MyField
             Rs.MoveNext
             Rw = Rw + 1
             c = Col
         Loop
         Set DestCell = Nothing
         Set Cn = Nothing
End Sub

Select allOpen in new window

Sorry, I'm an idiot - the error was in the SQL query. The string wasn't actually truncated at 250, it just appeard that way in the debugger.