Help with calling Stored Procedure on SQL Server

[Ivan Grozney]

I have a stored procedure on my SQL 2000 Server that I would like to call. I
am using Doug Steele's DSN less connection for the database. The Stored
Procedure has two paramaters to pass in.

The SP calulates the number of points someone has earned by month. I would
like to not have to duplicate the code in access if possible

I have tried ADO (as best I could figure it out) and DAO. No matter what I
seem to get errors saying that I cannot do a SELECT Query. So I tried to
create a pass through query (I cannot figure out how to create a DSNless
connection in the pass through so I could use help with that too).

If I use this code

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "exec usp_pointTotals " & Me.txtYear & ", " & Me.txtLifeStepID
Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("ZeePassThrough")
qdfCurr.SQL = strSQL
DoCmd.RunSQL ("zeepassthrough")
qdfCurr.ReturnsRecords = True
' CurrentDb.Execute "zeepassthrough", dbFailOnError
' qdfCurr.Execute dbFailOnError

nothing works

CurrentDB.Execute gives me the 3065 cannot run a Select query
DoCmd.RunSQL gives me the 3129 needs to be insert, procedure, ...
qdfCurr.Execute gives me the 3065 cannot run a Select query

TIA

Vanya

 

[MGFoster]

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

Here's how I do it:

Function RunPassThruQuery(strSQL As String, strConnect As String, _
fReturn As Boolean) As DAO.Recordset
' Purpose:
' Run a temporary query with the indicated SQL statement, for
' the indicated Connection string. If fReturn = True return a
' DAO.Recordset.
' In:
' strSQL The statement to run
' strConnect The ODBC connect string
' fReturn Indicates whether to return a recordset
' Out:
' DAO.Recordset - Only if fReturn = True
' Errors, if any
' Created:
' mgf 27apr2001
' Modified:
'

On Error GoTo err_

Dim db As DAO.Database
Set db = CurrentDb

' Create a temporary QueryDef object to run statement.
Dim qd As QueryDef
Set qd = db.CreateQueryDef("")

With qd
.Connect = strConnect
.SQL = strSQL
.ReturnsRecords = fReturn
If fReturn Then
Set RunPassThruQuery = .OpenRecordset()
Else
.Execute
End If
End With

exit_:
On Error Resume Next
Set qd = Nothing
Set db = Nothing
Exit Function

err_:
Dim strError As String
Dim lngError As Long
' First get the ODBC errors
strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In DBEngine.Errors
strError = strError & e & vbCrLf
Next e
' Then the Access error
lngError = Err.Number
strError = strError & vbCrLf & "Access error: " & vbCrLf
strError = strError & Err.Description
' Clean up
On Error Resume Next
Set qd = Nothing
Set db = Nothing
' Send the error back to the calling routine
On Error GoTo 0
Err.Raise lngError, , strError

End Function


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR71okYechKqOuFEgEQLDNwCfVhrDapRX0RS1ghXhvRU5gS6fqzsAn0Tt
ikeQeWKIqqOed6HDec27aHbo
=2mEE
-----END PGP SIGNATURE-----

 

[Ivan Grozney]

Whoa! Up early or late, eh? Thank you. I'll give it a try and post back.

Vanya

 

[Ivan Grozney]

Thank you, MG!!!, it works GREAT!

One other quick (I hope) question. The record set I am returning is not the
primary record set for the form, it just gathers and totals some information.
So in order to display the data from the recordset this function returns on
my form, I had to put the display in the function.

So in the function I put in
me.txtJANtot = RunPassThruQuery.Fields(0) and so on for all 28 fields.

if I put the above right after my call to the function in the on current
event, I get

Compile error: Argument not optional

Any ideas? For now putting it in the function is okay but it kind of
ruins the idea of a generic function that I am sure I will be able use again
in future coding.

However, thanks again for this. It was getting frustrating and this
really helped a lot!

Vanya

 

[MGFoster]

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

I'm not quite sure I understand, but here's a stab at an answer:

You should run the RunPassThruQuery once to get the RS that has all the
data you want; then, cherry-pick the data from the RS like this:

Me!txtJANtot = rs!JanTotal
Me!txtFebTot = rs!FebTotal

You'll have to set up your SP to have column names "JanTotal,"
"FebTotal," etc. Perhaps, you could create a new SP that calls the old
SP successive times and stores the data in a temp table. Then when all
the data is collected the new SP returns the data to the Access calling
routine. Then you would cherry-pick the data as shown above.

Not knowing what your SP looks like I really can't give a better answer.

ALSO - sometimes calling a function that creates and disposes of a
temporary query in rapid succession can confuse Access (I believe it
gets discombobulated when requested to create a new query before it has
disposed of the old temporary query). You can put in a wait loop before
calling the function. Or, you could create a permanent, blank query
that you change the SQL on, over & over. Just change my function to
have something like this:

Public Function RunPassThruQuery(strSQL As String, strQuery As String,
fReturn As Boolean) As DAO.Recordset

Replacing the strConnect with strQuery. The permanent query should
already have the DSN-less connect string.

Then replace this:

Set qd = db.CreateQueryDef("")

with this:

Set qd = db.QueryDefs(strQuery)

And, remove the ".Connect = strConnect" after the "With qd"

Good luck,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR739dYechKqOuFEgEQJLywCfUtd5HfKCIZFyzrF+Xy6B3W/p4JsAoLMu
GGyxSs7jAT78fergPweTxSbt
=5GsW
-----END PGP SIGNATURE-----

 

[Ivan Grozney]

Thanks, I'll give it a try.

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

I'm not quite sure I understand, but here's a stab at an answer:

You should run the RunPassThruQuery once to get the RS that has all the
data you want; then, cherry-pick the data from the RS like this:

Me!txtJANtot = rs!JanTotal
Me!txtFebTot = rs!FebTotal

You'll have to set up your SP to have column names "JanTotal,"
"FebTotal," etc. Perhaps, you could create a new SP that calls the old
SP successive times and stores the data in a temp table. Then when all
the data is collected the new SP returns the data to the Access calling
routine. Then you would cherry-pick the data as shown above.

Not knowing what your SP looks like I really can't give a better answer.

ALSO - sometimes calling a function that creates and disposes of a
temporary query in rapid succession can confuse Access (I believe it
gets discombobulated when requested to create a new query before it has
disposed of the old temporary query). You can put in a wait loop before
calling the function. Or, you could create a permanent, blank query
that you change the SQL on, over & over. Just change my function to
have something like this:

Public Function RunPassThruQuery(strSQL As String, strQuery As String,
fReturn As Boolean) As DAO.Recordset

Replacing the strConnect with strQuery. The permanent query should
already have the DSN-less connect string.

Then replace this:

Set qd = db.CreateQueryDef("")

with this:

Set qd = db.QueryDefs(strQuery)

And, remove the ".Connect = strConnect" after the "With qd"

Good luck,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR739dYechKqOuFEgEQJLywCfUtd5HfKCIZFyzrF+Xy6B3W/p4JsAoLMu
GGyxSs7jAT78fergPweTxSbt
=5GsW
-----END PGP SIGNATURE-----