Pass thru query to SQL server with parameters

[Guest]

I am NOT an access developer, so go slow. I am using MS Access 2000 and SQL
Serevr 2k. I would like to pass parameters to SQL Server stored procedures
via ODBC. The query output is used for a ms access report.

Do I have to write any code, or can I prompt in a similar way to a parameter
query, ie: PARAMETER or using [parm name]?? Any help is appreciated.

 

[MGFoster]

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

There are two ways to connect to SQL'r from Acc2000: one is by ADO (the
default); the other is by DAO. Which are you using?

If DAO (easiest IMNHO):

In the report's OnOpen event get the parameters from the user (I use a
acDialog form that gets all required criteria for the report); sets up a
QueryDef w/ the SP name & it's parameters; stuff the QueryDef's name in
the Report's RecordSource & continue. E.g.:

Private Sub Report_Open(Cancel As Integer)

Dim frm As Form

DoCmd.OpenForm FORM_CRITERIA, WindowMode:=acDialog

If isOpen(FORM_CRITERIA) Then
Set frm = Forms(FORM_CRITERIA)

' The SP requires 2 params: start date & end date.
' I format the dates so SQL'r can conver them to
' DateTime data types

' Here's where I stuff in the params.
' SQL will look like this: sp_name '20041001', '20041031'

CurrentDb.QueryDefs("queryName").SQL = _
"sp_name '" & Format(frm!txtStart,"YYYYMMDD") & "', '" & _
Format(frm!txtEnd,"YYYYMMDD") & "'"

' Put the Query's name in this Report's RecordSource
Me.RecordSource = "queryName"

Else
Cancel = True
End If

End Sub

If using ADO the Report's RecordSource would be the stored procedure's
name. Then you could use the report's Input Parameters property. E.g.,
using my above SP's parameters:

InputParameters: @Start DateTime = Forms!frmReportCriteria!txtStart,
@End DateTime = Forms!frmReportCriteria!txtEnd

You'd need to open the frmReportCriteria in the Report's OnOpen event,
as I did above in the DAO example.

Or, you could just set the Parameters in the Stored Procedure (when in
SP design view, select View > Properties from the main menu. Look at
the "Stored Procedure Parameters" tab). When the report runs you will
get a parameter prompt for each of the SP's parameters.

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

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

iQA/AwUBQfl7CYechKqOuFEgEQIvdQCgv/EK/oF+wVV9+QIGsCck01/erdIAnRpm
0AkGVb2KzN8PotTYqDSP7LbY
=fS6W
-----END PGP SIGNATURE-----

 

[Douglas J. Steele]

Unfortunately, you can't use parameters with pass-through queries. This is
because the queries run on the server, which doesn't know anything about
what's happening on the workstation.

What you can do, though, is determine what the values are (have a form with
all of the necessary fields on it), and then dynamically rewrite the SQL to
include the values before submitting the query.

Unfortunately, this does require write VBA code.

To change the SQL associated with a query, you'd use something like:

Dim qdfCurr As DAO.QueryDef

qdfCurr = CurrentDb().QueryDefs("nameofquery")
qdfCurr.SQL = "SELECT Field1, Field2 " & _
"FROM Table1 " & _
"WHERE Field3 = " & lngField3Value & _
" AND Field4 = '" & strField4Value & "'"

 

[Guest]

Thanks very much - this helped. I think I will just use inputboxes for
parameters

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

There are two ways to connect to SQL'r from Acc2000: one is by ADO (the
default); the other is by DAO. Which are you using?

If DAO (easiest IMNHO):

In the report's OnOpen event get the parameters from the user (I use a
acDialog form that gets all required criteria for the report); sets up a
QueryDef w/ the SP name & it's parameters; stuff the QueryDef's name in
the Report's RecordSource & continue. E.g.:

Private Sub Report_Open(Cancel As Integer)

Dim frm As Form

DoCmd.OpenForm FORM_CRITERIA, WindowMode:=acDialog

If isOpen(FORM_CRITERIA) Then
Set frm = Forms(FORM_CRITERIA)

' The SP requires 2 params: start date & end date.
' I format the dates so SQL'r can conver them to
' DateTime data types

' Here's where I stuff in the params.
' SQL will look like this: sp_name '20041001', '20041031'

CurrentDb.QueryDefs("queryName").SQL = _
"sp_name '" & Format(frm!txtStart,"YYYYMMDD") & "', '" & _
Format(frm!txtEnd,"YYYYMMDD") & "'"

' Put the Query's name in this Report's RecordSource
Me.RecordSource = "queryName"

Else
Cancel = True
End If

End Sub

If using ADO the Report's RecordSource would be the stored procedure's
name. Then you could use the report's Input Parameters property. E.g.,
using my above SP's parameters:

InputParameters: @Start DateTime = Forms!frmReportCriteria!txtStart,
@End DateTime = Forms!frmReportCriteria!txtEnd

You'd need to open the frmReportCriteria in the Report's OnOpen event,
as I did above in the DAO example.

Or, you could just set the Parameters in the Stored Procedure (when in
SP design view, select View > Properties from the main menu. Look at
the "Stored Procedure Parameters" tab). When the report runs you will
get a parameter prompt for each of the SP's parameters.

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

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

iQA/AwUBQfl7CYechKqOuFEgEQIvdQCgv/EK/oF+wVV9+QIGsCck01/erdIAnRpm
0AkGVb2KzN8PotTYqDSP7LbY
=fS6W
-----END PGP SIGNATURE-----

I am NOT an access developer, so go slow. I am using MS Access 2000 and SQL
Serevr 2k. I would like to pass parameters to SQL Server stored procedures
via ODBC. The query output is used for a ms access report.

Do I have to write any code, or can I prompt in a similar way to a parameter
query, ie: PARAMETER or using [parm name]?? Any help is appreciated.