Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to pass a parameter into a Stored Procedure from Access 97

22 views
Skip to first unread message

AK

unread,
Jan 21, 2003, 5:23:55 PM1/21/03
to

Hi,

I have an application written using Access97
for the front-end and SQL Server 7.0 for the
backend. I have kept all of the queries in Access97
instead of creating stored procedures on SQL Server.
I want to make the application more efficient
and quicker, so I have started creating stored
procedures but the problem is, how do I pass
a parameter to the stored procedure?
Note that I will be using the Pass-Through
query in access to call the Stored Procedure.

For example, if I have a query in Access that
gets some kind of input from the user (ex: searches
for Companies based on the name entered by the
user in a textbox on the form), and I want to
convert this query to a stored procedure, how
would I pass that criteria/parameter into the
stored procedure since it's getting the
value from a textbox on the form??

Here's how I am calling the stored procedure
using a Pass-Through Query:

execute GetCustomerName Forms![Customer_Frm]![txtCustomer]

GetCustomerName is the name of the Stored Procedure
on the SQL Server.

Forms![Customer_Frm]![txtCustomer] is the paramter
being passed thru from the form.

Thanks,

AK

Duane H

unread,
Jan 21, 2003, 7:44:15 PM1/21/03
to
Use code to change the SQL of the P-T query. You would pass in the name of
the P-T and the new SQL

strOldSQL = ChangeSQL("qsptMyPT","execute GetCustomerName " &
Forms![Customer_Frm]![txtCustomer])

Function ChangeSQL(strQuery as String, strSQL as String) as String
Dim db As DAO.Database
Dim qdf as DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)
ChangeSQL = qdf.SQL
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
End Function

--
Duane Hookom
MS Access MVP


"AK" <Afshin.K...@aig.com> wrote in message
news:050901c2c19b$c8e09bd0$d2f82ecf@TK2MSFTNGXA09...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003


AK

unread,
Jan 23, 2003, 2:54:11 PM1/23/03
to

Works like a charm !!!

Thanks so much for the help !

AK

>.
>

0 new messages