In this article, we will talk about SaveToDB Excel Add-In for Microsoft Excel.
The add-in has powerful import-export features for Microsoft Excel 2007-2016 and Microsoft SQL Server 2000-2016:
The first three features do not require any configuration and are ready to use right after install.
For example, you can connect to a database table, edit the data, and save the changes to the database immediately, without any coding.
For the last two features, you should develop configuration views and stored procedures.
However, only the server-side development is used. No coding in Microsoft Excel is required.
It is important to say that SaveToDB extends the behaviour of a native Excel QueryTable object.
So users can make what they do early and use new features to update database data.
The goal of the SaveToDB Excel Add-In is to empower database developers to deliver feature-rich database applications with Microsoft Excel as a client using only database development skills.
Table of Contents
SaveToDB Add-In contains a powerful Data Connection Wizard.
You can easily connect and reconnect to any database object including stored procedures.
The result of the SaveToDB connection wizard is a native Excel ListObject with QueryTable connected to a database table, view, or stored procedure.
The SaveToDB add-in contains a powerful Publish Wizard.
Using the Wizard you can:
Also, the wizard inserts formula columns from the source table and applies the source table formats.
After publishing, you can edit data in Microsoft Excel and save data changes back to a database.
This is the simplest way to implement a multi-user work with data stored in SQL Server or SQL Azure in Excel.
The source Excel data table:
Step 1. Connect to a server and a database
Step 2. Design the destination database table
Step 3. Define table's schema and name
Step 4. Verify and execute the script
Step 5. Verify the script results
Step 6. Finish the wizard and get a new Excel table connected to the created database table
Now you may edit data and save data changes back to a database.
If an Excel query table is connected to a database table, you may edit the data and save changes back to a database without any coding. Just click the Save button on the ribbon.
The SaveToDB add-in tracks data changes and generates the required INSERT, UPDATE and DELETE commands to update a database table.
This feature works with database tables and editable views only.
You should implement the SQL code to save changes of data from views or stored procedures.
You can configure stored procedures to update a database.
Particular server-side views are used for configuring.
In the example view you can see that:
SaveToDB automatically determines procedure parameters and calls the procedures for appropriate changes.
You can implement and modify any business logic using stored procedures.
Users should just click the Save button to save changes.
You can use stored procedures as Excel event handlers.
Below the example of the Role Members table. The table gets data from the dbo05.uspRoleMemberTable stored procedure.
Using a special configuration view, you can assign stored procedures to the Change, Double-Click or Context Menu events of any data table.
You can see that the dbo05.uspExcelEvent_Change_dbo05_uspRoleMemberTable stored procedure is used to handle the Change event of the dbo05.uspRoleMemberTable stored procedure.
The code of the dbo05.uspExcelEvent_Change_dbo05_uspRoleMemberTable procedure:
CREATE PROCEDURE [dbo05].[uspExcelEvent_Change_dbo05_uspRoleMemberTable] @User nvarchar(128) , @ColumnName nvarchar(128) , @CellValue nvarchar(255) AS BEGIN SET NOCOUNT ON IF @CellValue IS NULL EXEC sp_droprolemember @rolename = @ColumnName, @membername = @User ELSE EXEC sp_addrolemember @rolename = @ColumnName, @membername = @User END
The code is very simple. The procedure has parameters:
SaveToDB automatically determines procedure parameters and calls the procedure with proper values.
You can use in procedures any data from the base data table and the predefined values:
This is a very powerful feature.
The example implements the Role Members editor using Microsoft Excel with a dozen of SQL lines.
You may easily export Excel data to a new database table using the Publish Wizard of the SaveToDB add-in.
You may easily use Microsoft Excel as a database table editor.
You may easily transform Microsoft Excel to a feature-rich database client using server-side views and stored procedures.
|SaveToDB Add-In for Microsoft Excel|
|SaveToDB Add-In for Microsoft Excel||Download|
|E-book. Excel Applications. 10 Steps for Database Developers
The e-book shows how to create database client applications using Excel
Version: 1.0 | 03/20/2017 | 2.3MB
|E-book. Excel Applications. 10 Steps for VBA Developers
The e-book shows how to create database client applications with VBA
Version: 1.0 | 03/20/2017 | 2.4MB
|E-book. Excel Applications. 11 Steps for Advanced Users
The e-book shows how to create multi-user Excel applications with no SQL or VBA use
Version: 1.0 | 03/20/2017 | 2.7MB