Pass Through to Stored Procedure

[Brian]

Hello all, I am just trying this for the first time and here is the
situation

I have created a Pass through query that all I want it to do it call a
stored procedure on a MSSQL server

Here is:

DECLARE @LLC Int
? mbomGetMaxLLC ("@LLC int")

This tells me the Count field is incorrect.

the actual procedure looks like this

CREATE PROCEDURE mbomGetMaxLLC @LLC int OUT AS begin SET nocount on
select @LLC = (select max(LLC) from IVR10015) end set nocount off
GO


This works but I don't know how to call it to return the value of @LLC

I have set up my ODBC connect Str which I beleive to be correct and set
Return records to be yes.. Not sure what the scoop is. I have looked around
and found nothing to helpfull.

 

[Guest]

There are two ways to accomplish this. If you are familiar with ADO, this will accomplish the task
You don’t need a pass-through query for this. Just assign the value of GetMaxLLC wherever you need that

Public Function GetMaxLLC() As Lon

Dim cmdNew As ADODB.Comman
Dim cnn As ADODB.Connectio
Dim strCnn As Strin

strCnn = "Provider=sqloledb;Data Source=YourServerName;" &
"Initial catalog=YourDB;User Id=whatever;Password=whatever;
Set cnn = New ADODB.Connectio
cnn.Open strCn

Set cmdNew = New ADODB.Comman
With cmdNe
Set .ActiveConnection = cn
.CommandText = "mbomGetMaxLLC
.CommandType = adCmdStoredPro

.Parameters.Append .CreateParameter("@LLC", adInteger, adParamOutput

.Execut

GetMaxLLC = .Parameters("@LLC").Valu

End Wit

cnn.Clos
Set cnn = Nothin

End Functio

If you don’t follow the above code, you can also use a pass-through query as follows

Public Function GetMaxLLC() As Lon

Dim rsMaxLLC As New ADODB.Recordse
Dim cmdNew As New ADODB.Comman

cmdNew.CommandText = "Select * From qryNameOfYourQuery
cmdNew.CommandType = adCmdTex
cmdNew.ActiveConnection = CurrentProject.Connectio

Set rsMaxLLC = cmdNew.Execute(

GetMaxLLC = rsMaxLLC!LL

rsNewPhoneID.Clos

End Functio

Your pass-through query would simply read

Execute mbomGetMaxLL

You may need an edit of your stored procedure to use the pass-through query. The first option above returns the output parameter to Access and should be good to go

Hope that helps
SMK

 

[Brian]

I appreciate the explanation that helps big time.. thanx

There are two ways to accomplish this. If you are familiar with ADO, this will accomplish the task:
You don't need a pass-through query for this. Just assign the value of

GetMaxLLC wherever you need that.

Public Function GetMaxLLC() As Long

Dim cmdNew As ADODB.Command
Dim cnn As ADODB.Connection
Dim strCnn As String

strCnn = "Provider=sqloledb;Data Source=YourServerName;" & _
"Initial catalog=YourDB;User Id=whatever;Password=whatever;"
Set cnn = New ADODB.Connection
cnn.Open strCnn

Set cmdNew = New ADODB.Command
With cmdNew
Set .ActiveConnection = cnn
.CommandText = "mbomGetMaxLLC"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@LLC", adInteger, adParamOutput)

.Execute

GetMaxLLC = .Parameters("@LLC").Value

End With

cnn.Close
Set cnn = Nothing

End Function


If you don't follow the above code, you can also use a pass-through query as follows:

Public Function GetMaxLLC() As Long

Dim rsMaxLLC As New ADODB.Recordset
Dim cmdNew As New ADODB.Command

cmdNew.CommandText = "Select * From qryNameOfYourQuery"
cmdNew.CommandType = adCmdText
cmdNew.ActiveConnection = CurrentProject.Connection

Set rsMaxLLC = cmdNew.Execute()

GetMaxLLC = rsMaxLLC!LLC

rsNewPhoneID.Close

End Function

Your pass-through query would simply read:

Execute mbomGetMaxLLC

You may need an edit of your stored procedure to use the pass-through

query. The first option above returns the output parameter to Access and
should be good to go.

 

[MGFoster]

I have created a Pass through query that all I want it to do it call a
stored procedure on a MSSQL server

Here is:

DECLARE @LLC Int
? mbomGetMaxLLC ("@LLC int")

This tells me the Count field is incorrect.

the actual procedure looks like this

CREATE PROCEDURE mbomGetMaxLLC @LLC int OUT AS begin SET nocount on
select @LLC = (select max(LLC) from IVR10015) end set nocount off
GO

This works but I don't know how to call it to return the value of @LLC

I have set up my ODBC connect Str which I beleive to be correct and set
Return records to be yes.. Not sure what the scoop is. I have looked around
and found nothing to helpfull.

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

Since only one value is returned by your SP's SELECT query you don't
need the OUT parameter. Your SP can be simplified like this:

CREATE PROCEDURE mbomGetMaxLLC
AS
SET NOCOUNT ON
SELECT MAX(LLC) As Max_LLC
FROM IVR10015

You need to retrieve the data returned by the SP in a recordset.
Example in DAO (air code):

dim db as dao.database
dim rs as dao.recordset
dim strSQL as string

strSQL = "exec mbomGetMaxLLC"

set db = currentdb
set rs = db.OpenRecordset(strSQL)

' This will print to the result of the query.
' You can do whatever you want.
'
if not rs.eof then debug.print rs(0)

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

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

iQA/AwUBQIQpXIechKqOuFEgEQKUAwCg8M+KkAr9MEHiyxmU/oHWVT3PqoAAn0/s
jOzsF7G9gOYNK39K2PCBcaQM
=TsoP
-----END PGP SIGNATURE-----