Knowledge Base | Using SaveToDB Add-In as VBA Library

Using SaveToDB Add-In as VBA Library

As a VBA application developer, you can use the SaveToDB add-in in your projects.

You can easily load data from database tables, views, stored procedures, from the web and text files.

You can easily save data changes back to databases.

The add-in supports Microsoft SQL Server, Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB, Snowflake, SQL Server Compact, and SQLite.

It has built-in data providers and allows using OLEDB providers, ODBC drivers, and DSN connections.

Use the following code to get a reference to the SaveToDB add-in:

Dim com As COMAddIn
Dim addIn As Object
Set com = Application.COMAddIns("SaveToDB")
Set addIn = com.Object

Use the following code to save changes back to a database:

addIn.Save

It is easy. And you can save changes of data loaded from tables, views, and stored procedures!

You can change query parameters using named cells. For example, use the code like this to reload data from a database with new parameters:

Application.EnableEvents = False
Range("AccountName").Value = "My Bank"
Range("CompanyName").Value = "Carnation, Inc"
Range("ItemName").Value = Nothing
Application.EnableEvents = True
addIn.RebuildCommandTextFromNamedCells

Simple Sample

For example, you have the following database tables to store payments:

Payment Register Database Tables

You need to implement a form like this to enter new payments:

New Payment Form using VBA

Key Ideas

Basically, you have to do the following tasks:

  1. Implement loading Account, Company and Item lists into Excel to validate cell inputs.
  2. Implement saving new records.
  3. Implement the form.

The first and third tasks are not complicated.

But the second task is not so easy, especially when the project is deployed on multiple computers or the database connection requires installing ODBC drivers or OLEDB providers.

The SaveToDB add-in solves such tasks easily.

You can do the following initial steps:

  1. Load data into an Excel table.
  2. Configure saving changes from Excel to a database using SaveToDB features (including using stored procedures).

and use the following steps in your macro:

  1. Add a row to the Excel table and copy form values into the new row.
  2. Call the Save method of the SaveToDB add-in.

Initial Steps

In our example, you can choose two ways to save changes:

  1. Saving data directly into a database table
  2. Saving data using stored procedures

The first way is much easier. However, it has security issues as users can update the table directly.

The second way requires additional efforts but solves all security issues completely.
Also, you can implement any business logic in stored procedures on the server-side instead of macros.

Take a look at the screenshot:

SaveToDB Controls for Using in VBA Applications

You can use the Connection Wizard to connect to tables, views, and stored procedures.

Then you can save data changes back to a database using the Save button.

You can check and change the active database object using the Query List.

You can change query parameters using the ribbon parameters or named cells.

When a table is connected and supports saving changes, you can use the Save method in your VBA code.

Loading Database Data Using VBA

You can easily control data loaded from a database in your VBA macros.

For stored procedures, the SaveToDB add-in places parameters on the ribbon automatically.

However, for tables and views, users can select fields to place on the ribbon and use them to change the WHERE clause values.

You can define named cells with the parameter names on the worksheet to use the code like this:

Range("AccountName").Value = "My Bank"
Range("CompanyName").Value = "Carnation, Inc"
Range("ItemName").Value = "Revenue"

This example shows this technique: 

Filtered Data using VBA

SaveToDB 8 even allows changing ribbon parameters from the VBA macros directly.

Next Steps

We discussed a simple sample that shows basic SaveToDB features available with VBA.

Please refer to the Using SaveToDB with VBA topic of the SaveToDB documentation and check the available methods at SaveToDB Interface Methods for VBA.

Please note that you can use all the methods except for saving changes in a free SaveToDB edition.

Also, download the "10 Steps for VBA Developers" e-book and its source codes below.

Use the SaveToDB add-in in your VBA applications, and feel free to contact us .

 

Downloads
SaveToDB Add-In for Microsoft Excel
The add-in allows using Microsoft Excel as a customizable client for corporate applications
Version: 9.10 | 08/21/2020 | 19.0MB | Getting Started | What's New | SDK | E-Books | Previous Versions
Download
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 | Read online
Download
Source codes of 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 | 0.2MB
Download