Using SaveToDB Add-In with VBA

Using SaveToDB Add-In with VBA

You can use the SaveToDB add-in as a VBA library.

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

You can easily save data changes back to Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, PostgreSQL, and SQLite databases.

These features allow building VBA applications to eliminate issues with database layers.

The programming model is simple:

  • Configure SaveToDB add-in behavior in the design mode.
  • Call SaveToDB methods from your VBA macros.

For example, use the following code to get the reference of the SaveToDB add-in:

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

and use the following code to save changes back to a database:

addIn.Save

It is easy.

You can use the Save method with the specified ListObject object in one of the forms:

addIn.Save ListObject

Call addIn.Save(ListObject)

If Not addIn.Save(ListObject) Then
    MsgBox addIn.LastResultMessage
End If

Also, you can use the following snippets to load data from databases, the web, or text files:

addIn.Load

addIn.Load ListObject

Call addIn.Load(ListObject)

If Not addIn.Load(ListObject) Then
    MsgBox addIn.LastResultMessage
End If

Use the code like this to update query parameters in named cells at once and then to load data from databases, the web, or text files with new parameters:

Application.EnableEvents = False

Range("Account").Value = 123
Range("Company").Value = "ABC"

Application.EnableEvents = True

addIn.RebuildCommandTextFromNamedCells

Also, you can use direct calls to set parameter values:

addIn.ParameterValue(ListObject, "Account") = 123
addIn.ParameterValue(ListObject, "Company") = "ABC"

Call addIn.Load(ListObject)

This way does not require creating named cells.

See complete lists of methods and properties available with VBA: