As a VBA application developer, you may use the SaveToDB add-in in your projects.
You may easily load data from database tables, views, stored procedures, from the web and text files.
You may easily save data changes back to Microsoft SQL Server, Oracle Database, IBM DB2, MySQL, MariaDB and NuoDB databases.
Use the following code to get reference to the SaveToDB add-in:
Dim addIn As COMAddIn Dim automationObject As Object Set addIn = Application.COMAddIns("SaveToDB") Set automationObject = addIn.Object
Use the following code to save changes back to a database:
And use the code like this to load data from databases with new parameters:
Application.EnableEvents = False Range("AccountName").Value = "My Bank" Range("CompanyName").Value = "Carnation, Inc" Range("ItemName").Value = Nothing Application.EnableEvents = True automationObject.RebuildCommandTextFromNamedCells
For example, you have the following database tables of payment register application:
And you need to implement the form like this to entry new payments:
Basically, you have to execute the following tasks:
- Implement loading Account, Company and Item lists into Excel to validate input.
- Implement saving new records.
- Implement the form.
The first and the 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 issues.
So you may do the following preparation steps:
- Load data into an Excel table.
- Configure saving changes from Excel to a database using SaveToDB features.
and use the following steps in your macro:
- Add a row to Excel table and copy form values into the new row.
- Call the Save method of the SaveToDB add-in.
In our example you may select two ways to save changes:
- Save data directly into database tables.
- Save data using stored procedures.
The first way is much easier but has security issues as users can update the table.
The second way requires additional efforts but solves all security issues completely.
Also, you may implement any business logic in stored procedures on the server side, not in macros.
Take a look at the screenshot:
You may use Connection Wizard to connect tables, views, and stored procedures.
And you may save data back to a database using the Save button.
You may see and change active database object using the Query List.
You may change query parameters using the ribbon parameters or named cells.
When the table is connected and supports saving changes, you may use the Save method in your VBA code.
Loading Database Data Using VBA
You may easily filter data loaded from a database in your VBA macros.
The SaveToDB add-in places stored procedure parameters to the ribbon automatically.
For tables and views, you may select fields to place to the ribbon and change the WHERE clause values.
You may define named cells with 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"
Here is an example of this technique:
Download and install the SaveToDB add-in, and download and try the package for VBA developers that includes examples and source codes:
|Packages for VBA Developers|
|SaveToDB Add-In for Microsoft Excel|
|Examples for VBA Developers
The package includes Excel application examples with source codes for VBA developers
Version: 7.9 | 08/01/2017 | 15.9MB | Home
You will find the described example in the package. You may also download the SaveToDB SDK.
Use the SaveToDB add-in in your VBA applications. And feel free to contact us.