13 – Calling A Stored Procedure From Access

Table of Contents

In order to fully maximise the effect of a SQL Server back-end, we are going to need to be able to called stored procedures from MS Access.

In this post we show you how to do it.

The Function

In order to call a SQL Server Stored Procedure from Access we will need to write a function that opens up an ADODB Recordset and executes the relevant procedure.

Put this function in the modSQLServer module.

Here is the code:

Public Function sp_UpdateCurrentGuest(GuestID As Long) As Boolean
sp_UpdateCurrentGuest = False

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "driver={sql server};server=ROBERTAUSTIN-PC\SQLEXPRESS;" & _
        "Database=HotelBookingsDB;UID=sa;PWD=password1;" & _
        "Trusted_Connection=Yes"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("Exec dbo.UpdateCurrent " & GuestID)

Set rs = Nothing
cnn.Close
Set cnn = Nothing

sp_UpdateCurrentGuest = True
ExitFunction:
    Exit Function
ErrorHandler:
    sp_UpdateCurrentGuest = False
    Resume ExitFunction

End Function

Public Function sp_UpdateCurrentGuest(GuestID As Long) As Boolean
We are using a function so we can return true if it completes successfully and false otherwise.

sp_UpdateCurrentGuest = False
We set the function to false as a default return value.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Declare cnn and rs as the connection and recordset respectively.

Set cnn = New ADODB.Connection
Instantiate the cnn variable.

cnn.ConnectionString = “driver={sql server};server=ROBERTAUSTIN-PC\SQLEXPRESS;” & _
“Database=HotelBookingsDB;UID=sa;PWD=password1;” & _
“Trusted_Connection=Yes”

This is the connection string required for the SQL Server. Make a note of the syntax as trying to work out if you have a correct connection string can be very trying.

cnn.Open
Open the connection

Set rs = New ADODB.Recordset
Instantiate the rs variable as an ADODB Recordset.

Set rs = cnn.Execute(“Exec dbo.UpdateCurrent ” & GuestID)
This is the key line. We are re-instantiating the rs variable and using it call the Stored Procedure. Please note that the string passed to cnn.Execute is the exact string we require to fire the Stored Procedure in SQL Server.

Set rs = Nothing
cnn.Close
Set cnn = Nothing

Kill the rs and cnn variables.

This function will update all of the Guests in the Guests table if you pass it -1 as the GuestID.

frmSplash

In order to call the function we need to add this line to the frmSplash OnOpen event:

If sp_UpdateCurrentGuest(-1) = False Then MsgBox "Unable to update current guests"

This line will only inform the user if the function returns false

The full code for the OnOpen event of frmSplash is:

Private Sub Form_Open(Cancel As Integer)
Dim myArray(12) As String

myArray(0) = "tblBookingDetails"
myArray(1) = "tblBookings"
myArray(2) = "tblDates"
myArray(3) = "tblExtraCharges"
myArray(4) = "tblGuests"
myArray(5) = "tblInvoicePayments"
myArray(6) = "tblInvoices"
myArray(7) = "tblReports"
myArray(8) = "tblRooms"
myArray(9) = "tblSettings"
myArray(10) = "tblUsers"
myArray(11) = "vwBookingsForInvoices"

'loop through array and add tables

Dim i As Integer

For i = 0 To UBound(myArray) - 1
    If AttachDSNLessTable(myArray(i), _
        "dbo." & myArray(i), _
        "ROBERTAUSTIN-PC\SQLEXPRESS", _
        "HotelBookingsDB", _
        "sa", _
        "password1") Then
    Else
        MsgBox "Could not relink " & myArray(i)
    End If
Next i

If sp_UpdateCurrentGuest(-1) = False Then MsgBox "Unable to update current guests"

End Sub

When you open up the database the Stored Procedure to update the IsCurrent field for the Guests table will fire.

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube