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.
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.
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
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.
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.