How To Call A SQL Stored Procedure From Access

[R Tanner]

Hi,

I have the following function which I call whenever a user hits submit
on my access form.

Public Function UpdateInstances(DAT As Date, MyID As Long, InstanceID
As Long, CTypeID As Long, RFC As Long)

Dim MySQL As String
Dim rs As ADODB.Recordset
Dim DB As Database
Set DB = CurrentDb

Set rs = New ADODB.Recordset



With rs
.ActiveConnection = CurrentProject.Connection
.Open "Instances", , adOpenDynamic, adLockOptimistic
.AddNew
!EmployeeID = MyID
!IssueID = InstanceID
!DAT = DAT
!CTypeID = CTypeID
!RFCID = RFC
.Update
End With

rs.Close
DB.Close
Set DB = Nothing

Set rs = Nothing




End Function


This function is updating a SQL database. I also have the following
stored procedure in the SQL database which I want to call whenever the
above function is called. Or rather, maybe I should do away with the
above function and just call my stored procedure. My stored procedure
will do the job, if I can figure out how to call it from my access
application and pass parameters to the stored procedure. Does anyone
who has more experience in this have any suggestions? I think what I
want to do is figure out how to execute my stored procedure from
Access. I can figure out the rest...

CREATE PROCEDURE WhenDBUpdated
(@EmpID Tinyint, @IssID Tinyint, @CTpID Tinyint, @RfcID
Tinyint)

DECLARE @ID int
SET @ID = (SELECT MAX(ID) FROM [Reason Codes].ReasonCodes.Instances)
SET @ID = @ID + 1

DECLARE @Shift Text
DECLARE @Tme Time
SET @Tme = CONVERT(Time, GETDATE())

IF @Tme BETWEEN '07:00:00' AND '15:00:00'
BEGIN
SET @Shift='D'
END
ELSE IF @Tme BETWEEN '15:00:01' AND '23:00:00'
BEGIN
SET @Shift='S'
END
ELSE IF @Tme BETWEEN ('23:00:01' AND '23:59:59') OR ('00:00:01' AND
'06:59:59')
BEGIN
SET @Shift='G'
END


AS

INSERT INTO [Reason Codes].ReasonCodes.Instances (ID, DAT, EmployeeID,
IssueID, CTypeID,
RFCID, Shift)
VALUES(@ID, @Tme, @EmpID, @IssID, @CTpID, @RfcID, @Shift)

 

[MGFoster]

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

I set up an empty SQL pass thru query and just fill it's .SQL property
when I want to call a stored procedure:

Dim db as DAO.Database
Dim qd as DAO.QueryDef

set db = CurrentDB
set qd = db.QueryDefs("ThePassThruQuery")
with qd
.SQL = "exec WhenDBUpdated " & bytEmployeeID & ", " & _
IssID & ", " & CTypeID & "," & RFC
.Execute dbFailOnError
End With

Handle any errors with something like this:

strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In Errors
strError = strError & e & vbCrLf
Next e
MsgBox strError

--
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/AwUBSWzvOIechKqOuFEgEQIJZgCgtrm1y5r4V3tdHVFAIvNuaEa7vAgAnjV5
EOZDw+G+h3bYKmcshlobH55l
=UcAf
-----END PGP SIGNATURE-----

Hi,

I have the following function which I call whenever a user hits submit
on my access form.

Public Function UpdateInstances(DAT As Date, MyID As Long, InstanceID
As Long, CTypeID As Long, RFC As Long)

Dim MySQL As String
Dim rs As ADODB.Recordset
Dim DB As Database
Set DB = CurrentDb

Set rs = New ADODB.Recordset



With rs
.ActiveConnection = CurrentProject.Connection
.Open "Instances", , adOpenDynamic, adLockOptimistic
.AddNew
!EmployeeID = MyID
!IssueID = InstanceID
!DAT = DAT
!CTypeID = CTypeID
!RFCID = RFC
.Update
End With

rs.Close
DB.Close
Set DB = Nothing

Set rs = Nothing




End Function


This function is updating a SQL database. I also have the following
stored procedure in the SQL database which I want to call whenever the
above function is called. Or rather, maybe I should do away with the
above function and just call my stored procedure. My stored procedure
will do the job, if I can figure out how to call it from my access
application and pass parameters to the stored procedure. Does anyone
who has more experience in this have any suggestions? I think what I
want to do is figure out how to execute my stored procedure from
Access. I can figure out the rest...

CREATE PROCEDURE WhenDBUpdated
(@EmpID Tinyint, @IssID Tinyint, @CTpID Tinyint, @RfcID
Tinyint)

DECLARE @ID int
SET @ID = (SELECT MAX(ID) FROM [Reason Codes].ReasonCodes.Instances)
SET @ID = @ID + 1

DECLARE @Shift Text
DECLARE @Tme Time
SET @Tme = CONVERT(Time, GETDATE())

IF @Tme BETWEEN '07:00:00' AND '15:00:00'
BEGIN
SET @Shift='D'
END
ELSE IF @Tme BETWEEN '15:00:01' AND '23:00:00'
BEGIN
SET @Shift='S'
END
ELSE IF @Tme BETWEEN ('23:00:01' AND '23:59:59') OR ('00:00:01' AND
'06:59:59')
BEGIN
SET @Shift='G'
END


AS

INSERT INTO [Reason Codes].ReasonCodes.Instances (ID, DAT, EmployeeID,
IssueID, CTypeID,
RFCID, Shift)
VALUES(@ID, @Tme, @EmpID, @IssID, @CTpID, @RfcID, @Shift)