Switching a Form's Recordsource at RunTime to a SP w/ a Parameter

[Ecomorph]

Hi - I have been upsizing a complex application to an ADP app (Access
2003 and SQL Server 2K) for the past few months, and have reached an
impasse that I can't just seem to get past, and am hoping for some
help. I have a form based on a view with several listboxes and subforms
based on SPs with parameters. I want to limit the default recordset to
records created in the past 60 days but need to allow the users to
expand all the way to 100% of records. Writing SQL via VBA is an
option, but in order to retain the functionality of the form,
performance over a WAN, and ease of maintanence, I'd prefer to use SPs
with parameters. I also don' want to clutter up the Form or change UI,
so I have added a command button that opens a 2nd form with a single
text control and Option frame where the user can specify the type of
search (e.g. Location, Account#, etc..). The users enter a text string,
hits the Find Records button, which verifies valid search criteria, and
then opens the target form. It passes the name of a stored procedure to
the Open Args via Select Case...and now I am stuck.
Private Sub Form_Open(Cancel As Integer)
' This works
If IsNull(Me.OpenArgs) Then
Me.InputParameters = ""
Me.RecordSource = "vw_NetworkFootprint"
Else
' Problems start here
Me.RecordSource = Me.OpenArgs
Me.InputParameters = "@txt_SearchBox nvarchar(25) =
Forms!frm_SMang.txt_SearchBox"

Here is where I am stuck.Can't pass the Param. I have tried defining
the InputParams via VBA several different ways, Executing a SP via the
Command object with Parameters, Creating a Function and attempting to
Call the Function as the row source, but nothing seems to do both (1)
Pass the Parameter or (2) Assign the Stored Procedure to the Form's
rowsource. Help! Have gone page after page of books and Google Groups,
written a # of iterations of code, but nothing I have come up seems to
cut it. Thanks for the help!

Private Function CallStoredProc()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command


Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = Me.OpenArgs
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@txt_SearchBox") = Forms!frm_SMang.txt_SearchBox
.Execute
End With
End Function

 

[Sylvain Lafontaine]

The easiest solution would be to set the RowSource of your controls and the
RecordSource of your form to a string representing the EXEC command followed
by the name of your stored procedure followed by the parameters:

Me.RecordSource = "exec vw_NetworkFootprint 'text to search' "

Of course, this string can be passed with Me.OpenArgs. You can also set the
RecordSource to the name of the SP, set the RecordSourceQualifier to its
owner (usually « dbo » ) and use the InputParameters property: see the
previous messages in this newsgroup to know how to do this. These past
messages are all on Google:

http://groups.google.ca/group/microsoft.public.access.adp.sqlserver

In your case, your exemple looks good; however, you may try setting the
RecordSourceQualifier to dbo ; it will help if your users doesn't login as
the owner of the database.

Also, the SP will be called when the RecordSource of the form will be
changed, so you must set the InputParameters before setting the
RecordSource. Take a look with the SQL-Server Profiler to be sure.

Don't forget to set the UniqueTable and ResyncCommand properties too!
Otherwise, you may have problems when editing data.

 

[Pat Hartman\(MVP\)]

I know you don't want to hear this but if it is not too late, go back to
using the MDB but link it to SQL Server if you need to support more users or
larger tables. Development on ADPs is no longer being done although they
will still be supported in the next version of Access.

 

[Ecomorph]

Thanks Sylvain,

I'll give it a try and let you know how it went. I really appreciate
the help!

David

 

[Ecomorph]

Pat,

You are correct! That is the most depressing news I have gotten in a
really long time. I have poured my heart and soul into this effort. I
have no choice but to complete what I have done, and from a performance
standpoint, the MDB performed horribly over the WAN - my MDB already
linked many of its tables from Server2K. I needed ADP for the Stored
Procedures. I am horribly dissapointed.

 

[Pat Hartman\(MVP\)]

If you join Jet tables to SQL Server tables, Jet has no option but to drag
the entire server side table down to the local PC for processing so it is
important for all your tables to be in the server database.

If you got poor performance with an .mdb linked to SQL Server, your
application was doing things "Access style" rather than "client/server
style". Take a look at the access client/server optimization articles in
the knowledge base or MSDN.

 

[david epsom dot com dot au]

If you join Jet tables to SQL Server tables, Jet has no option but to drag

the entire server side table down to the local PC for processing so it is

I know what you are saying, but I hate to see it expressed
that way.

If you join Jet tables to SQL Server tables, Jet has no option
but to drag the join fields from every selected record down
to the local PC for processing.

Unless you are careful about your query design, Jet is likely
to drag your entire server side database down to the local PC
for processing.

Of course that's still not technically true, but it covers a
wide range of best case and worst case scenarios.

Jet ODBC can only do one outer join per query (this was a
deliberate limitation in the ODBC SQL Specification) so a
large query to get all fields from all related records could
easily bring down all of your related tables, with all the
joins evaluated locally.

On the other hand, Jet has the option of applying server
side criteria before joining Jet and Server tables. In best
case scenarios, local tables have negligible affect.

(david)

 

[Ecomorph]

Thanks Sylvain - that was very helpfu. Much appreciated. Once I was
able to make it happen once, it really helped me understand the concept
of working with ADO and recordsets.