Best practice call stored procedure

[zz12]

Hello. Would anyone happen to know or point me to some info on what is the
best practice in calling sql server stored procedures from ms access vba?
Seems like perhaps a Pass-Through query and was wondering if one would need
to create a pass-through query for each and every associating stored
procedure or could just one generic pass-through query be used?

 

[Albert D. Kallal]

Just create one passh thought query.

then, in code go:

dim rst as DAO.recordset
dim qdf as DAO.QueryDef
Set qdf = currentdb.QueryDefs("qryExecuteMySP")
qdf.SQL = "exec MySP "
qdf.execute

To return data, you could go

set rst = qdf.OpenReocrdSet

You can wrap the whole thing into a public functon. And, even add some
opitonal paramters....

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

bullshit faggot

best practice is to use SQL Server and Access DAta Projects

docmd.openstoredprocedure if you must

 

[Crossh]

I split an Access 2003 mdb to access front end/sql server back end. I got
everything to work, but some forms are slow when remote users are trying to
access them. I want to optimize the Access database to make better use of Sql
Server. I don't fully understand the Stored Procedure concept and pass
through queries. I was able to create a pass through query, but don't
understand how can you pass parameters from the input forms to these. The
record source for these forms is coded in VBA (using DAO) based on selections
on the form. Can anyone direct me to where I can get some information on
this? Links or books or classes (Phila area).

I just read a whole book on upsizing from Access to SQL Server and it said
to turn the mdb into adp. I was ready to start this today, but after reading
more about mdb's and adp's in this site, I'm thinking that this isn't the
best way to go.