getting around the recordsource length limitation

[Tom Carter]

I'm moving an application from being a pure Access MDB to an Access 2000
project against SQL Server. I have encountered one situation which has me
frustrated for lack of an easy solution.

In the MDB code, I had one dynamic SQL string to execute that was about 3000
characters long. Please note that I have already trimmed table name
qualifiers and used short aliases where possible to shorten the query, so
it's not necessary to recommend that.

To get around the recordsource length limitation with the MDB, it was simple
enough to assign the query to the .SQL property of a QueryDef
(CurrentDb.QueryDefs("myquery").SQL = "SELECT...") and then assign the name
of the query to the recordsource. Since each query is stored in a local MDB
file which connects to a backend database, this is considered multi-user
"safe".

How do I achieve the same result with an Access project against SQL Server?
It appears that the recordsource length limitation still applies, but
creating a view on the server is not safe in a multi-user situation because
multiple users might concurrently be trying to create or access the same
view with the same name.

I've come up with what I would consider two "hacks".

1.) Create a view dynamically with a different name everytime ("myview1",
"myview2", "myview3", ...)

2.) Create a stored procedure which takes a large number of parameters

I really don't like #1, since a large number of views might be left on the
server is the code to remove them has any problems.

Is #2 really the only way? If so, can I assign a stored procedure call with
parameters to a recordsource? If so, what is the syntax for this? Are there
any issues with setting the controlsource of controls on the form? Is it
simple enough to just name the returned columns from a stored procedure and
use that name as I would the name of a column from a view?

Thanks in advance,
Tom

 

[Lyle Fairfield]

I'm moving an application from being a pure Access MDB to an Access 2000
project against SQL Server. I have encountered one situation which has me
frustrated for lack of an easy solution.

In the MDB code, I had one dynamic SQL string to execute that was about 3000
characters long. Please note that I have already trimmed table name
qualifiers and used short aliases where possible to shorten the query, so
it's not necessary to recommend that.

To get around the recordsource length limitation with the MDB, it was simple
enough to assign the query to the .SQL property of a QueryDef
(CurrentDb.QueryDefs("myquery").SQL = "SELECT...") and then assign the name
of the query to the recordsource. Since each query is stored in a local MDB
file which connects to a backend database, this is considered multi-user
"safe".

How do I achieve the same result with an Access project against SQL Server?
It appears that the recordsource length limitation still applies, but
creating a view on the server is not safe in a multi-user situation because
multiple users might concurrently be trying to create or access the same
view with the same name.

I've come up with what I would consider two "hacks".

1.) Create a view dynamically with a different name everytime ("myview1",
"myview2", "myview3", ...)

2.) Create a stored procedure which takes a large number of parameters

I really don't like #1, since a large number of views might be left on the
server is the code to remove them has any problems.

Is #2 really the only way? If so, can I assign a stored procedure call with
parameters to a recordsource? If so, what is the syntax for this? Are there
any issues with setting the controlsource of controls on the form? Is it
simple enough to just name the returned columns from a stored procedure and
use that name as I would the name of a column from a view?

Thanks in advance,
Tom

I confess that I have wrestled with this for a long time, that is making
the recordsource for an ADP-SQL Server to be dynamic.

I've tried:

1. using a dynamic SQL string; I want to keep such things on the server ...
and this assumes that the user has select permissions on the tables and
views; it's not what I want.
2. set the report's recordset to an ADO generated recordset (available only
in XP and 2003) ... this was very powerful but very difficult to work with.
3. creating a new view for each running of the report ... assumes the user
has create permissions on the server which I did not want to give

and where I am now

4. A Stored Procedure selects from a View. The view is quite complicated,
but it's static and once done, it seldom requires any more attention. The
SP has input parameters which govern what is to be selected. To set these I
set the Report's Input Parameters in the Property Sheet to:

=GetRptOrganizationandStaffingInputParameters()

and the GetRptOrganizationandStaffingInputParameters() Public Function
examines a number of possibilites and returns a string of what is needed
for the correct records to be shown.

Is there a better way? I would be glad to know it.