Appendix A. Database Source Code

You can download the source code at

https://www.savetodb.com/downloads/10-steps-for-vba-developers.zip

You can download the SaveToDB add-in at www.savetodb.com.

To use all described features, install version SaveToDB 7.2 or higher.

Master Tables

CREATE TABLE [dbo67].[Accounts] (
      [ID] int IDENTITY(1,1) NOT NULL
    , [Name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_Accounts_dbo67] PRIMARY KEY ([ID])
    , CONSTRAINT [IX_Accounts_dbo67] UNIQUE ([Name])
)
GO

CREATE TABLE [dbo67].[Companies] (
      [ID] int IDENTITY(1,1) NOT NULL
    , [Name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_Companies_dbo67] PRIMARY KEY ([ID])
    , CONSTRAINT [IX_Companies_dbo67] UNIQUE ([Name])
)
GO

CREATE TABLE [dbo67].[Items] (
      [ID] int IDENTITY(1,1) NOT NULL
    , [Name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_Items_dbo67] PRIMARY KEY ([ID])
    , CONSTRAINT [IX_Items_dbo67] UNIQUE ([Name])
)
GO

Add UNIQUE constraints to your tables to avoid name doubles.

Table dbo67.Payments

CREATE TABLE [dbo67].[Payments] (
      [ID] int IDENTITY(1,1) NOT NULL
    , [Date] datetime NULL
    , [Sum] money NULL
    , [AccountID] int NULL
    , [CompanyID] int NULL
    , [ItemID] int NULL
    , [Comment] nvarchar(255) NULL
    , CONSTRAINT [PK_Payments_dbo67] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [dbo67].[Payments] ADD CONSTRAINT [FK_Payments_Accounts_dbo67]
FOREIGN KEY ([AccountID]) REFERENCES [dbo67].[Accounts] ([ID]) ON UPDATE CASCADE
GO

ALTER TABLE [dbo67].[Payments] ADD CONSTRAINT [FK_Payments_Companies_dbo67]
FOREIGN KEY ([CompanyID]) REFERENCES [dbo67].[Companies] ([ID]) ON UPDATE CASCADE
GO

ALTER TABLE [dbo67].[Payments] ADD CONSTRAINT [FK_Payments_Items_dbo67]
FOREIGN KEY ([ItemID]) REFERENCES [dbo67].[Items] ([ID]) ON UPDATE CASCADE
GO

View dbo67.viewPayments

CREATE VIEW [dbo67].[viewPayments]
AS

SELECT
    p.ID
    , p.[Date]
    , p.[Sum]
    , p.AccountID
    , p.CompanyID
    , p.ItemID
    , p.Comment

FROM
    dbo67.Payments p

GO

The view selects data from a single table that makes it updateable.

You can use INSERT, UPDATE, and DELETE statements for such views directly. The add-in uses this feature.

Stored Procedure dbo67.uspPayments

CREATE PROCEDURE [dbo67].[uspPayments]
    @AccountID int = NULL
    , @CompanyID int = NULL
    , @ItemID int = NULL
AS
BEGIN

SET NOCOUNT ON

SELECT
    p.ID
    , p.[Date]
    , p.[Sum]
    , p.AccountID
    , p.CompanyID
    , p.ItemID
    , p.Comment

FROM
    dbo67.Payments p
WHERE
    COALESCE(@AccountID, p.AccountID, 0) = COALESCE(p.AccountID, 0)
    AND COALESCE(@CompanyID, p.CompanyID, 0) = COALESCE(p.CompanyID, 0)
    AND COALESCE(@ItemID, p.ItemID, 0) = COALESCE(p.ItemID, 0)

END
GO

The stored procedure has parameters used to filter source table data. The NULL value means "all values."

Use SET NOCOUNT ON as the first command. Otherwise, Excel cannot load data from the procedure.