Why saving failed with following code

Hi, I wrote following code to query some tables (sybase)

sqlStr = "SELECT r.Desk, r.LimitSubGroup, l.LimitName, r.RealDataColumnShift," & _
" l.ReportType, r.Column, r.RowNum, s.Value" & _
" FROM ReportTemplate r, LimitDefinition l, RiskRptDetails s" & _
" WHERE r.SheetName = 'CreditDeltaDetail' " & _
" AND r.TemplateName='GFM.xlt' " & _
" AND s.PositionDate='" & Format(positionDate, "mm/dd/yyyy") & "'" & _
" AND r.LimitId = l.LimitId" & _
" AND l.ReportType not in('DeskID')" & _
" AND r.Desk = s.Grouping" & _
" AND r.LimitSubGroup = s.SubGroup" & _
" AND r.LimitId = s.LimitId"

rstData.Open sqlStr, dbconn, adOpenKeyset, adLockReadOnly
....
Then I clicked save button for this VBAproject, I got error message saying "Excel cannot save this file" and excel renamed my worksheet to temporay name such as E03F0190.

Finally I changed above code to

sqlStr = "SELECT r.Desk, r.LimitSubGroup, l.LimitName, r.RealDataColumnShift," & _
" l.ReportType, r.Column, r.RowNum, s.Value" & _
" FROM ReportTemplate r, LimitDefinition l, RiskRptDetails s" & _
" WHERE r.SheetName = 'CreditDeltaDetail' AND r.TemplateName='GFM.xlt'" & _
" AND s.PositionDate='" + positionDateStr + "'" & _
" AND r.LimitId = l.LimitId" & _
" AND r.Desk = s.Grouping" & _
" AND r.LimitSubGroup = s.SubGroup" & _
" AND r.LimitId = s.LimitId"

rstData.Open sqlStr, dbconn, adOpenKeyset, adLockReadOnly
......

And excel allowed saving this time.

Is this because sqlStr is too large? I checked String limit(2^16). sqlStr is definitely within that limit. So what's wrong with the first sqlStr?

Thanks a lot
Wendy

 

You might try Debug>Compile to see if there were any compile errors. Also,
I've run into occasions where I had too many line continuation characters. I
don't remember what the limit was though.

--
----
Ed
----

"Excel cannot save this file" and excel renamed my worksheet to temporay
name such as E03F0190.

is definitely within that limit. So what's wrong with the first sqlStr?

 

Try the following and see if it makes any difference.

sqlStr = "SELECT r.Desk, r.LimitSubGroup, l.LimitName,
r.RealDataColumnShift, _
l.ReportType, r.Column, r.RowNum, s.Value _
FROM ReportTemplate r, LimitDefinition l, RiskRptDetails s _
WHERE (r.SheetName = 'CreditDeltaDetail' _
AND r.TemplateName='GFM.xlt' _
AND s.PositionDate='" & Format(positionDate, "mm/dd/yyyy") & "'
_
AND r.LimitId = l.LimitId _
AND l.ReportType not in('DeskID') _
AND r.Desk = s.Grouping _
AND r.LimitSubGroup = s.SubGroup _
AND r.LimitId = s.LimitId)"

 

Wendy,

Are you hitting an Access database?

Bob

 

Thanks a lot for replying. I am accessing an Sybase database. It is not line continuation problem. The limit for line continuation is 25. Is there any limit for query size? Does VBA has size limit for module?

 

Thanks a lot. The line continuation limit is 25. I cannot use Debug >compile to check since it crashed too. I am wondering if there are any limit for query size? Any limit for module size?