Avoid This Common Error When Executing Stored Procedure in MS Access

 

We love running stored procedures from our VBA code, but there is one issue you need to watch out for: Executing a procedure that affects data already loaded on your form. If you’re not careful you will get the following message:

Avoid This Common Error When Executing Stored Procedure in MS Access

Fortunately, it’s an easy fix, here are some suggestions:

  • Close the form and then run your stored procedure. This assumes you may not wish to review the altered data on the same form.
  • Set the forms recordsource to nothing, then set it back to the original data source, (see code below).

Close the form and then run the stored procedure

Here is some pseudo-code to get you started:

Private Sub RunSomeProcedure()
Dim lngOrderID as Long

'Assuming you need to pass information from your form to the stored procedure, for example, an OrderID
lngOrderID = Me.OrderID 'If you don't store the info it will not be available after closing the form

DoCmd.Close acForm, Me.Name 'This closes the form
ExecuteMyCommand "uspStoredProcedureName " & lngOrderID

End Sub

Not familiar with ExecuteMyCommand? You can look it up here.

Set the form’s recordsource to null

Private Sub RunSomeProcedure()
Dim lngOrderID as Long
Dim strRecordSource as String

'Assuming you need to pass information from your form to the stored procedure, for example, an OrderID
lngOrderID = Me.OrderID 'If you don't store the info it will not be available after closing the form

strRecordSource = Me.RecordSource 'Store the recordsource for later use
Me.RecordSource = vbNullString
ExecuteMyCommand "uspStoredProcedureName " & lngOrderID
Me.RecordSource = strRecordSource 'Restore the form so that the user can see the updated data

End Sub

Join me on March 9th with special guest Ebo Quansah!

Come and learn the latest on Microsoft Access with Ebo, the product manager for the Access group. For details please click here: https://accessusergroups.org/sql-server-with-access/event/sql-server-with-access-whats-new-in-access-a-presentation-by-the-access-product-manager/