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:

1. dbo67.uspPayments
2. dbo67.uspPayments_insert
3. dbo67.uspPayments_update
4. dbo67.uspPayments_delete

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.