Chapter 14. Configuring Saving Changes

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:

Sample configuration to save stored procedure data into a 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

Initial configuration to save stored procedure data into a table

to a new configuration with stored procedures:

Sample configuration to save stored procedure data using stored procedures

After these steps, we have a completely configured stored procedure.

Sample of editable stored procedure in Microsoft Excel

Let's change a couple of rows and check the generated SQL code using the Save, View Save Change SQL button.

Sample of the code used to save changes using stored procedures

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.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK