CADForums.net

wendy · Guest

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

Ed Jobe · Guest

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
----
"wendy" <nospam@address.withheld> wrote in message
news:20753400.1112798531703.JavaMail.jive@jiveforum2.autodesk.com...

Jorge Jimenez · Guest

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)"

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

"wendy" <nospam@address.withheld> wrote in message
news:20753400.1112798531703.JavaMail.jive@jiveforum2.autodesk.com...

bcoward · Guest

Wendy,

Are you hitting an Access database?

Bob

wendy · Guest

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?

wendy · Guest

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?

	CADForums.net Forum Index -> VBA	All times are GMT
Page 1 of 1