Running a long Access SQL query from Excel 2007

Yesterday I posted question on how to run an Access SQL query from VBA in Excel 2007 (here).

The solution works, however one of the queries I need to run is too long and gets truncated in VBA. The length of my query is about 350 characters and the maximum allowed length seems to be about 250.

Is there any way to get around this problem?
RothbardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Have you tried saving a parameter query in the Access db and then running that from the code?
0
RothbardAuthor Commented:
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?
0
Rory ArchibaldCommented:
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?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

RothbardAuthor Commented:
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

Open in new window

0
Rory ArchibaldCommented:
That string actually is 255 characters if B1 is blank, so I'm not sure what you are expecting? I just tested it with some additional text in B1 and the length of sSQL is reported as being 306 characters.
What actual error are you getting?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RothbardAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.