How to bind dynamic stored procedure to a form or report?

  • Thread starter Thread starter XMan
  • Start date Start date
X

XMan

I'm looking for a way to improve the app performance by moving my queries
with filtering to SQL2K server.
Is there a way to bind a form or report to a dynamic query in a stored
procedure? TIA.
 
XMan said:
I'm looking for a way to improve the app performance by moving my queries
with filtering to SQL2K server.
Is there a way to bind a form or report to a dynamic query in a stored
procedure? TIA.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe the result set of an SP is read-only. Therefore, it would be
better to use a View as the RecordSource of a form on which you intend
to edit data. Since reports are not editable you could use the SP.

Are you using an .mdb Access file or an .adp? Which version of Access?

If .mdb file you can use a QueryDef as the form's/report's RecordSource.
When the form/report opens use the Open event to set up the QueryDef's
..SQL property. E.g.:

Private Sub Report_Open(Cancel As Integer)

' The query "QueryName" is the report's RecordSource

' The form "frmCriteria" has the SP's criteria
' stored in the TextBox "txtCriteria"

Dim db as DAO.Database
Dim qd As DAO.QueryDef
Set db = Currentdb
Set qd = db.QueryDefs("QueryName")
qd.SQL = "exec usp_ProcedureName " & Forms!frmCriteria!txtCriteria
qd.Close

End Sub

In .adp files you can upt the EXEC statement in the RecordSource (both
form's & reports). Just change it as needed in the form's/report's Open
event procedure. E.g.:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "EXEC usp_ProcedureName " & _
Forms!frmCriteria!txtCriteria

End Sub


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIWwNYechKqOuFEgEQJn0wCfRg0Bnup7R3GIgHfNy5aOYpGikp4An0Mn
AmFZfYvc8t3BsAmAyGZV9OqU
=CpyM
-----END PGP SIGNATURE-----
 
My app is running from MDB and Access2K.

Thanks for your excellent tips. I'll try them out.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes you can. In the OnOpen event procedure of the report.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIg95YechKqOuFEgEQKHfgCg1MColQSBGbEP1BeKemguR+dAQQQAoPhS
LPQmPBTgeJTfP590vqvDymRC
=45GB
-----END PGP SIGNATURE-----
 
Just tried it and Access2K came back with invalid statement error. It
requires SELECT, DELETE, UPDATE, INSERT, or PROCEDURE statement. What other
parameters I need to set so ODBC can pass the statement direct to SQL
Server?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure what's going on here. It seems that you have an .adp file,
since the error mentions "PROCEDURE," which, I believe, will only be
returned when the Access file type is .adp/e.

If your file is an .mdb, as you implied in an earlier post, and you are
using a SQL Pass Through (SPT) query, then the SPT QueryDef must have a
Description property (while in SQL view) that starts with "ODBC." Is
this true? If so, then make sure it has at least the following:

ODBC;Driver={Sql Server};Server=ServerName;Database=DatabaseName

This is a DSN-less connection string. If you are using NT-Login
authentication add ";Trusted_Connection=Yes" to the string.

If you are running an SPT qry then you have to change the .SQL propety
of the query def before the report runs. I showed how to do this in an
earlier post.

If you are running an .adp file type, the RecordSource of a report can
look like this:

Exec ProcedureName param1, param2

where param? is the value of any parameters that the SP requires.

I'm starting to repeat myself (previous posts). If you have any further
info that would clarify your situation it would be appreciated.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQI8IBYechKqOuFEgEQLXDgCg956YvnVmggJiFmjnr13o68CpD68AoM6F
kHD3aZFpAL8PoTD+wNZlHdVn
=SFbq
-----END PGP SIGNATURE-----
 
Back
Top