Using SaveToDB with VBA

Using SaveToDB with VBA

Using with VBA

You may use the SaveToDB add-in with your VBA macros as a free 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, 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:

IAddInUtilities Interface

The IAddInUtilities interface defines properties and functions implemented by the SaveToDB add-in.

You may use these methods with VBA macros and VSTO add-ins.

<ComVisible(True), Guid("CF0170F1-310E-421F-87DF-C177753EEDD5")>
Public Interface IAddInUtilities

    ' Last Error Message

    ReadOnly Property LastResultMessage As String

    ' Options

    ReadOnly Property Options As IAddInOptions

    ' Ribbon

    Property FullRibbon() As Boolean

    Property RibbonTabVisible(Optional Workbook As Workbook = Nothing) As Boolean

    Function ActivateTab() As Boolean

    ' Execute

    Function ExecuteNonQuery(ConnectionString As String, CommandText As String) As Boolean

    Function ExecuteReader(ConnectionString As String, CommandText As String) As <MarshalAs(UnmanagedType.BStr)> System.Array

    Function RunCmd(Command As String) As Boolean

    ' Insert ListObject

    Function InsertConnectedListObject(TargetRange As Range, ConnectionString As String, QueryObject As String, ObjectType As String) As Boolean

    ' Save

    Function Save(Optional ListObject As ListObject = Nothing) As Boolean

    Function SavePivotTable(PivotTable As PivotTable) As Boolean

    Function SaveByMerge(Optional ListObject As ListObject = Nothing) As Boolean

    Function GetSaveSQL(Optional ListObject As ListObject = Nothing) As String

    Function GetSaveByMergeSQL(Optional ListObject As ListObject = Nothing) As String

    Function SaveByMergeDirect(ListObject As ListObject, ConnectionString As String, QueryObject As String, ObjectType As String) As Boolean

    Function GetSaveByMergeDirectSQL(ListObject As ListObject, ConnectionString As String, QueryObject As String, ObjectType As String) As String

    Function SaveAllSheetTables(Optional Worksheet As Worksheet = Nothing) As Boolean

    Function SaveAllWorkbookTables(Optional Workbook As Workbook = Nothing, Optional UseWizard As Boolean = True) As Boolean

    ' Load

    Function Load(Optional ListObject As ListObject = Nothing,
        Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False) As Boolean

    Function LoadPivotTable(PivotTable As PivotTable,
        Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False) As Boolean

    Function LoadAllSheetTables(Optional Worksheet As Worksheet = Nothing,
        Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False) As Boolean

    Function LoadAllWorkbookTables(Optional Workbook As Workbook = Nothing,
        Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False, Optional UseWizard As Boolean = True) As Boolean

    Function RebuildCommandTextFromNamedCells(Optional Worksheet As Worksheet = Nothing) As Boolean

    Function ReloadQueryList(Optional ListObject As ListObject = Nothing) As Boolean

    ' Connection Strings

    Property ExcelConnectionString(Optional ListObject As ListObject = Nothing) As String

    Property ConnectionString(Optional ListObject As ListObject = Nothing) As String

    Property PivotTableExcelConnectionString(Optional PivotTable As PivotTable = Nothing) As String

    Property PivotTableConnectionString(Optional PivotTable As PivotTable = Nothing) As String

    ' Connection String Properties

    Property ProviderName(Optional ListObject As ListObject = Nothing) As String

    Property PivotTableProviderName(Optional PivotTable As PivotTable = Nothing) As String

    ReadOnly Property ConnectionStringIntegratedSecurity(Optional ListObject As ListObject = Nothing) As Boolean

    Property ConnectionStringUserName(Optional ListObject As ListObject = Nothing) As String

    Property ConnectionStringPassword(Optional ListObject As ListObject = Nothing) As String

    ReadOnly Property PivotTableConnectionStringIntegratedSecurity(Optional PivotTable As PivotTable = Nothing) As Boolean

    Property PivotTableConnectionStringUserName(Optional PivotTable As PivotTable = Nothing) As String

    Property PivotTableConnectionStringPassword(Optional PivotTable As PivotTable = Nothing) As String

    ' Protecting Passwords and Usernames

    Function HideConnectionStringPassword(Optional ListObject As ListObject = Nothing) As Boolean

    Function HidePivotTableConnectionStringPassword(Optional PivotTable As PivotTable = Nothing) As Boolean

    Function SetConnectionStringPassword(Password As String, Optional ListObject As ListObject = Nothing) As Boolean

    Function SetPivotTableConnectionStringPassword(Password As String, Optional PivotTable As PivotTable = Nothing) As Boolean

    Function HideConnectionStringUserNameAndPassword(Optional ListObject As ListObject = Nothing) As Boolean

    Function HidePivotTableConnectionStringUserNameAndPassword(Optional PivotTable As PivotTable = Nothing) As Boolean

    Function SetConnectionStringUserNameAndPassword(UserName As String, Password As String, Optional ListObject As ListObject = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    Function SetPivotTableConnectionStringUserNameAndPassword(UserName As String, Password As String, Optional PivotTable As PivotTable = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    ' Protecting Passwords and Usernames at Worksheet and Workbook Levels

    Function HideWorksheetConnectionStringPasswords(Optional Worksheet As Worksheet = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    Function HideWorkbookConnectionStringPasswords(Optional Workbook As Workbook = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    Function HideWorksheetConnectionStringUserNamesAndPasswords(Optional Worksheet As Worksheet = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    Function HideWorkbookConnectionStringUserNamesAndPasswords(Optional Workbook As Workbook = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    Function SetWorksheetConnectionStringPasswords(Password As String, Optional Worksheet As Worksheet = Nothing) As Boolean

    Function SetWorkbookConnectionStringPasswords(Password As String, Optional Workbook As Workbook = Nothing) As Boolean

    Function SetWorksheetConnectionStringUserNamesAndPasswords(UserName As String, Password As String, Optional Worksheet As Worksheet = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    Function SetWorkbookConnectionStringUserNamesAndPasswords(UserName As String, Password As String, Optional Workbook As Workbook = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean

    ' Protecting Workbook Connections

    Function HideWorkbookConnectionPasswords(Optional Workbook As Workbook = Nothing) As Boolean

    Function HideWorkbookConnectionUserNamesAndPasswords(Optional Workbook As Workbook = Nothing) As Boolean

    Function SetWorkbookConnectionPasswords(Password As String, Optional Workbook As Workbook = Nothing) As Boolean

    Function SetWorkbookConnectionUserNamesAndPasswords(UserName As String, Password As String, Optional Workbook As Workbook = Nothing) As Boolean

    ' CommandText and QueryList

    Property CommandText(ListObject As ListObject) As String

    Property QueryObject(ListObject As ListObject) As String

    Property QueryList(ListObject As ListObject) As String

    Property QueryLocked(ListObject As ListObject) As Boolean

    Function GetQueryListItems(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array

    ' Fields and Parameters

    Function GetFields(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array

    Function GetParameters(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array

    Property IsRibbonField(ListObject As ListObject, FieldName As String) As Boolean

    Function HasParameter(ListObject As ListObject, ParameterName As String) As Boolean

    Property ParameterValue(ListObject As ListObject, ParameterName As String) As Object

    Property ParameterRibbonValue(ListObject As ListObject, ParameterName As String) As String

    ' Cursors

    Function AddTableCursor(Optional ListObject As ListObject = Nothing) As Boolean

    Function RemoveTableCursor(Optional ListObject As ListObject = Nothing) As Boolean

    Function DeleteTableCursor(Optional ListObject As ListObject = Nothing) As Boolean

    Function DeleteAllTableCursors(Optional Workbook As Workbook = Nothing) As Boolean

    Function MoveNext(Optional ListObject As ListObject = Nothing) As Boolean
    Function MoveToNext(Optional ListObject As ListObject = Nothing) As Boolean

    Function MovePrevious(Optional ListObject As ListObject = Nothing) As Boolean
    Function MoveToPrevious(Optional ListObject As ListObject = Nothing) As Boolean

    Function MoveFirst(Optional ListObject As ListObject = Nothing) As Boolean
    Function MoveToFirst(Optional ListObject As ListObject = Nothing) As Boolean

    Function MoveLast(Optional ListObject As ListObject = Nothing) As Boolean
    Function MoveToLast(Optional ListObject As ListObject = Nothing) As Boolean

    ' Form Fields

    Function AddFormFields(ListObject As ListObject, TopLeftCell As Range) As Boolean

    Function RemoveFormFields(ListObject As ListObject) As Boolean

    Function GetFormFieldCellName(ListObject As ListObject, ColumnName As String) As String

    ' Add-In Worksheets

    Function InsertAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean

    Function RemoveAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean

    Function DeleteAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean

    Function ShowAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean

    Function HideAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean

    Function CleanAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean

    ' Table Views

    Function ApplyTableView(ListObject As ListObject, name As String) As Boolean

    Function SaveTableView(ListObject As ListObject, name As String) As Boolean

    Function DeleteTableView(ListObject As ListObject, name As String) As Boolean

    Function DeleteTableViews(ListObject As ListObject) As Boolean

    Function DeleteAllTableViews(Workbook As Workbook) As Boolean

    Function GetTableViews(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array

    Function CopyAndInsertRows(Optional Range As Range = Nothing) As Boolean

End Interface

IAddInOptions Interface

The IAddInOptions interface defines SaveToDB add-in options.

You may use these properties with VBA macros and VSTO add-ins.

<ComVisible(True), Guid("CF0170F1-310E-421F-87DF-C177753EEDD9")>
Public Interface IAddInOptions

    ' Common Options

    Property DisplayLanguage As String

    Property DefaultDataLanguage As String

    Property WorkbookDataLanguage(Optional Workbook As Workbook = Nothing) As String

    Property EncryptPasswordsByDefault As Boolean

    Property EncryptWorkbookPasswords(Optional Workbook As Workbook = Nothing) As Boolean?

    Property ConnectionTimeout As Int32

    Property CommandTimeout As Int32

    Property GenerateSingleLineSQL As Boolean

    ' Excel Options

    Property ShowUrlMenu As Boolean

    Property ShowContextMenu As Boolean

    Property AutoActivateTableRelatedWindows As Boolean

    Property AutoArrangeWindows As Boolean

    Property AutoHideTaskbarMdiWindows As Boolean

    Property ShowDoubleClickCalendar As Boolean

    Property ShowDoubleClickPivot As Boolean

    Property AutoFilterRow As Boolean

    Property AutoOpenLastFile As Boolean

    Property AutoCleanBadNamedCells As Boolean

    ' Ribbon Options

    Property HideAdvancedMenuItems As Boolean

    Property ParameterHistoryLimit As Int32

    ' Options Menu

    Property ShowCellEditor As Boolean

    Property ShowListEditor As Boolean

    ' Special Options

    Property TemplatePath As String

    Property ShowToolsMenu As Boolean?

    ' TaskPanes

    Property TaskPaneDockPosition As Int32

    ' Cell Editor

    Property CellEditorDockPosition As Int32

    Property CellEditorTop As Int32

    Property CellEditorLeft As Int32

    Property CellEditorHeight As Int32

    Property CellEditorWidth As Int32

    Property CellEditorDockHeight As Int32

    Property CellEditorDockWidth As Int32

    Property CellEditorFontSize As Single

    Property CellEditorFontName As String

    ' List Editor

    Property ListEditorDockPosition As Int32

    Property ListEditorTop As Int32

    Property ListEditorLeft As Int32

    Property ListEditorHeight As Int32

    Property ListEditorWidth As Int32

    Property ListEditorDockHeight As Int32

    Property ListEditorDockWidth As Int32

    Property ListEditorFontSize As Single

    Property ListEditorFontName As String

End Interface