Repeated Parameter Passing - ADP to SQL 2000

[ChrisMN]

I have been working in Access (mdb) for ten years and have just started
moving databases to SQL with an ADP front end. I know there has to be a
simple answer to this question, but for some reason it is not sinking in. I
have an unbound form with two unbound controls Startdate and Enddate. When
you click the command button on this form it runs about 20 append queries
which populate a report table. Each of these queries uses values in the
controls on the form as criteria. I realize that moving to stored procedures
I need to pass these parameters in a different way. What would be the
simplest way to pass the parameters to SQL so they are available to each of
the 20 stored procedures? The program I am converting to ADP uses form date
range parameters on a regular basis, so I would like to make sure I pass
these parameters to SQL in the most efficient manner. Thanks in advance for
your assistance.

 

[aaron.kempf]

DOcmd.RunSql "exec mysproc1 '" & me.text1 & "'"
DOcmd.RunSql "exec mysproc2 '" & me.text1 & "'"
DOcmd.RunSql "exec mysproc3 '" & me.text1 & "'"

or if you needed to; you could bind the form to a sproc named
mySprocTest that takes parameter '@txtMyPK' and then you would just
have a text control on your form named 'txtMyPk' and it would
auto-negotiate

I really really really reccomend ADP; it is the best platform anywhere

-Aaron

 

[aaron.kempf]

and if it's always a simple begdate and enddate; i would probably just
store those in a table WHERE SPID = @@SPID and then refer to them in a
subQuery.

SQL subqueries rock; they're fast