Using SaveToDB with VBA

Using SaveToDB with VBA

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

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, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, PostgreSQL, and SQLite databases.

These features allow building VBA applications eliminating 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 may 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 may 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 may use direct calls to set parameter values starting SaveToDB 6.8:

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

Call addIn.Load(ListObject)

This way does not require creating named cells. So, it is universal. However, this method is not applicable prior SaveToDB 6.8.

You may find VBA examples in SaveToDB Developer Guide 6.8 in the Wizards, Workbook Templates and Examples menu.

See also complete lists of properties and methods available with VBA:

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.