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.
