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:
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/