Using SaveToDB Add-In with VBA

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:

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.