Chapter 14. Configuring Saving Changes
Saving changes to a single table
If we need to save changes to a single underlying table, we can specify the target table in the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields of the QueryList table:
In this example, this is a right case as the procedure selects data from a single table.
Saving changes to multiple tables
You can save changes from an Excel table to multiple database tables.
However, this requires using stored procedures or SQL codes.
Let's create such procedures for our example.
Here is the procedure used to insert new rows:
CREATE PROCEDURE [dbo67].[uspPayments_insert] @Date datetime = NULL , @Sum money = NULL , @AccountID int = NULL , @CompanyID int = NULL , @ItemID int = NULL , @Comment nvarchar(255) = NULL AS BEGIN SET NOCOUNT ON INSERT INTO dbo67.Payments ( [Date] , [Sum] , AccountID , CompanyID , ItemID , Comment ) VALUES ( @Date , @Sum , @AccountID , @CompanyID , @ItemID , @Comment ) END GO
You can see that the procedure has the parameters named as the selected column names.
So, the add-in just calls the procedure passing values from a new row.
You can implement any logic in stored procedures. This procedure just inserts a row into the Payments table.
Here is the code of the update procedure:
CREATE PROCEDURE [dbo67].[uspPayments_update] @ID int , @Date datetime = NULL , @Sum money = NULL , @AccountID int = NULL , @CompanyID int = NULL , @ItemID int = NULL , @Comment nvarchar(255) = NULL AS BEGIN SET NOCOUNT ON UPDATE dbo67.Payments SET [Date] = @Date , [Sum] = @Sum , AccountID = @AccountID , CompanyID = @CompanyID , ItemID = @ItemID , Comment = @Comment WHERE ID = @ID END GO
You can see that the logic is the same. The procedure declares and uses parameters with the column names.
The update procedure has the @ID parameter as the updated row exists. The insert procedure has not.
Here is the code of the delete procedure:
CREATE PROCEDURE [dbo67].[uspPayments_delete] @ID int AS BEGIN SET NOCOUNT ON DELETE dbo67.Payments WHERE ID = @ID END GO
In most cases, delete procedures use primary key column values only.
Now, we can replace the configuration created in the previous step
to a new configuration with stored procedures:
After these steps, we have a completely configured stored procedure.
Let's change a couple of rows and check the generated SQL code using the Save, View Save Change SQL button.
As we see, the add-in generated EXEC commands.
Using stored procedures to save changes is a common way. In this case, we have four procedures like these:
If you use the _insert, _update, and _delete name convention, the add-in links edit procedures automatically, and you can skip adding the configuration to the QueryList table.
Saving changes using VBA
We have used the Save method to save data changes for tables in the previous chapters.
We use it also to save data changes for data loaded from stored procedures in the same manner:
' Saving changes, Chapter 14 Sub Chapter14_1_SaveChanges() Dim addIn As Object Set addIn = GetAddInAndCheck() If addIn Is Nothing Then Exit Sub Dim lo As ListObject Set lo = GetActiveListObject() If lo Is Nothing Then Exit Sub If Not addIn.Save(lo) Then Debug.Print addIn.LastResultMessage End If End Sub
As a VBA developer, you do not care about the details.
If the saving changes is configured in a database, you just call the Save method.