Using SaveToDB Add-In with VBA
The SaveToDB add-in can be utilized as a VBA library, allowing you to seamlessly load data from database tables, views, stored procedures, as well as from web and text files. You can also save data changes back to various databases, including Microsoft SQL Server, Oracle Database, MySQL, MariaDB, Snowflake, PostgreSQL, and SQLite.
These features enable you to build VBA applications that effectively manage database interactions.
The programming model is straightforward:
- Configure the SaveToDB add-in behavior in design mode.
- Call SaveToDB methods from your VBA macros.
For example, to get a reference to the SaveToDB add-in, use the following code:
Dim com As COMAddIn Dim addIn As Object Set com = Application.COMAddIns("SaveToDB") Set addIn = com.Object
To save changes back to a database, use:
addIn.Save
It's that simple.
You can call the Save method with a specified ListObject
in any of the following forms:
addIn.Save ListObject Call addIn.Save(ListObject) If Not addIn.Save(ListObject) Then MsgBox addIn.LastResultMessage End If
To load data from databases, the web, or text files, use these snippets:
addIn.Load addIn.Load ListObject Call addIn.Load(ListObject) If Not addIn.Load(ListObject) Then MsgBox addIn.LastResultMessage End If
To update query parameters in named cells and load data with the new parameters, use the following code:
Application.EnableEvents = False Range("Account").Value = 123 Range("Company").Value = "ABC" Application.EnableEvents = True addIn.RebuildCommandTextFromNamedCells
You can also set parameter values directly without creating named cells:
addIn.ParameterValue(ListObject, "Account") = 123 addIn.ParameterValue(ListObject, "Company") = "ABC" Call addIn.Load(ListObject)
For a complete list of methods and properties available with VBA, check out: