S
SteveJ
Problem: When specifying a long (>4000 character) SELECT statement, Access
reports (not unreasonably!) that this is too long.
To work around this I've tried:
Method 1
Create a view with the same SELECT statement, and then set the report's
datasource as the view. This works except that the SELECT statement varies
and so the view has to be created at run time. Unfortunately this mean that
it is not available until that particular code has finished executing, which
is obvoiusly too late.
Method 2
Create a stored procedure to the return the appropriate recordset and set
the report's datasource as this procedure. To pass the SELECT statement to
the procedure, it needs to be set in the Input Parameters field. Attempting
to do this creates an error since Access reports that it is too long.
What I need is some temporary storage within the server in which to hold
this SELECT statement, which I can then access via a stored procedure. I
have looked at creating a default, but this seems an inappropriate use for
this feature, and I wouldn't be surprised to find that it has not been
committed in time for me to access with a single procedure.
Anyone got any suggestions?
TIA
Steve
reports (not unreasonably!) that this is too long.
To work around this I've tried:
Method 1
Create a view with the same SELECT statement, and then set the report's
datasource as the view. This works except that the SELECT statement varies
and so the view has to be created at run time. Unfortunately this mean that
it is not available until that particular code has finished executing, which
is obvoiusly too late.
Method 2
Create a stored procedure to the return the appropriate recordset and set
the report's datasource as this procedure. To pass the SELECT statement to
the procedure, it needs to be set in the Input Parameters field. Attempting
to do this creates an error since Access reports that it is too long.
What I need is some temporary storage within the server in which to hold
this SELECT statement, which I can then access via a stored procedure. I
have looked at creating a default, but this seems an inappropriate use for
this feature, and I wouldn't be surprised to find that it has not been
committed in time for me to access with a single procedure.
Anyone got any suggestions?
TIA
Steve