Report Recordsource too large - A97

[Kahuna]

Hi Folks - not come across this one before.

I use code behind the report to use complex rowsource SQL dependant upon
what the client has requested in the set-up form.

All was well until I expanded the report to encompass many more data items
and calculations - with the query SQL supplying just the required end
figures.

Because this recordsource SQL is now relatively large - A97 is balking at
its size suggesting that 2048 is the maximum characters allowed (I am
assuming it means as a SQL recordsource property - error is vague). The SQL
works fine in the query window. So here's my question:

I can save this query and use it as the recordsource directly in the
properties of the report but...

How can I supply the name of a saved query to the recordsource of the report
at runtime (what's the syntax)?

This is essential because I'll now have several saved queries.

Or... would it be better to save a query, set it as the recordsource in the
report property, and set its sql properties to my SQL at run time??? If so,
suggestions on syntax for that would be useful too.

 

[Marshall Barton]

Hi Folks - not come across this one before.

I use code behind the report to use complex rowsource SQL dependant upon
what the client has requested in the set-up form.

All was well until I expanded the report to encompass many more data items
and calculations - with the query SQL supplying just the required end
figures.

Because this recordsource SQL is now relatively large - A97 is balking at
its size suggesting that 2048 is the maximum characters allowed (I am
assuming it means as a SQL recordsource property - error is vague). The SQL
works fine in the query window. So here's my question:

I can save this query and use it as the recordsource directly in the
properties of the report but...

How can I supply the name of a saved query to the recordsource of the report
at runtime (what's the syntax)?

This is essential because I'll now have several saved queries.

Or... would it be better to save a query, set it as the recordsource in the
report property, and set its sql properties to my SQL at run time??? If so,
suggestions on syntax for that would be useful too.

It is rarely necessary to use different queries for a
report. One reason is to calculate some fields differently,
so you may have one of those cases.

If your queries have long complicated expressions, then you
might be better off doing the calculations with code in a
report event procedure.

OTOH, you can use one character alias names for tables in
the query and drastically reduce the length of each field
reference. If the queries only refer to a single table, you
do not need to use the table name in any field reference.

If these suggestions are not sufficient, then we'll need
more details, especially a Copy/Paste of one of the queries.

 

[Kahuna]

Thanks for the feedback Marshal - expeditious as usual! (Think that's an
actual word lol)

My code develops the SQL separately since there are some 24 different
possibilities or subtleties in the field members for the report. Using
separate SQL for each and assigning it as Recordsource work really well but
now its just too long I guess. I built initially to use a code builder for
the required fields - but it got so messy I decided to use fixed strings.

Its a multi table (4) query.

The reason the SQL has expanded is because I wanted to get away from the
messy calcs in report fields, and get all of the rather simple calcs into
one Query string.

I reckon best practice will be to use a saved query as the recordsource then
set the query SQL dynamically. I am trying this out at the moment (test name
on the query BTW - don't wana get flamed for poor conventions lol).

'***********************
Set dbCurr = CurrentDb()
Set qdfSQL = dbCurr.OpenQueryDef("xxx_qryBudgetReport_25-1-07")
...
strRecSource = "Some SQL"
...
qdfSQL.SQL = strRecSource
'***********************

What do you think? Is there a better way forward?

 

[Marshall Barton]

Thanks for the feedback Marshal - expeditious as usual! (Think that's an
actual word lol)

My code develops the SQL separately since there are some 24 different
possibilities or subtleties in the field members for the report. Using
separate SQL for each and assigning it as Recordsource work really well but
now its just too long I guess. I built initially to use a code builder for
the required fields - but it got so messy I decided to use fixed strings.

Its a multi table (4) query.

The reason the SQL has expanded is because I wanted to get away from the
messy calcs in report fields, and get all of the rather simple calcs into
one Query string.

I reckon best practice will be to use a saved query as the recordsource then
set the query SQL dynamically. I am trying this out at the moment (test name
on the query BTW - don't wana get flamed for poor conventions lol).

'***********************
Set dbCurr = CurrentDb()
Set qdfSQL = dbCurr.OpenQueryDef("xxx_qryBudgetReport_25-1-07")
..
strRecSource = "Some SQL"
..
qdfSQL.SQL = strRecSource
'***********************

What do you think? Is there a better way forward?

That's a valid way to do it.

OTOH, I don't know if using full table names to qualify
every field reference is what's making the query so long. I
have seen queries where the table names use more than half
the text in the SQL statement.

Some times a WHERE clause with numerous OR conditions can be
significantly condensed by using IN(. . .) instead.

If you want detailed suggestions post the SQL statement.

 

[Kahuna]

That's a valid way to do it.

OTOH, I don't know if using full table names to qualify
every field reference is what's making the query so long. I
have seen queries where the table names use more than half
the text in the SQL statement.

Some times a WHERE clause with numerous OR conditions can be
significantly condensed by using IN(. . .) instead.

If you want detailed suggestions post the SQL statement.

Thanks again for that feedback Marshall.

Looking again the table names probably make up 20% of the space in the query
SQL so using Alias's probably would solve the dilemma in that way.

I have the saved query up and running now and it seem an appropriate way
forward - I'll remember the Alias tool for future reference though - really
hadn't though about it before!

Cheers Marshal