DBAddin

DBAddin is a ExcelDNA based Add-in for Database interoperability.

First, DBAddin provides DB Functions (see DBFuncs User-doc), which are an alternative to the Excel built-in MSQuery (integrated statically into worksheets having severe limitations in terms of querying and constructing parameterized queries (MS-Query allows parameterized queries only in simple queries that can be displayed graphically)).

Next, methods for working with database data (DBModifications: DBMapper, DBAction and DBSequences) are included. This also includes a row entry oriented way to modify data in so called DBSheets (see DBSheets).

DBAddin.NET is the successor to the VB6 based Office Database Add-in, see also the slide-show for a quick overview.

Installation

If any of these are missing, please install them yourself before starting DBAddin.

Download the latest zip package in https://github.com/rkapl123/DBAddin/tags, unzip to any location and run deployAddin.cmd in the folder Distribution. This copies DBAddin.xll, DBAddin.xll.config, DBAddinUser.config and DBAddinCentral.config to your %appdata%\Microsoft\AddIns folder and tries to register in HKEY_CURRENT_USER\Software\Microsoft\Office<OfficeVersion>.0\Excel\Options\OPEN to activate DB Add-in (if this is not successful you have to register it yourself in the Add-ins).

Settings

Settings can be configured in three config files, depending on your distribution requirements:

In the DBAddin settings Group, there is a drop-down named “settings”, where you can modify these three settings inside Excel.

After installation you’d want to adapt the connection strings (ConstConnStringN) that are globally applied if no function-specific connection string is given and environment N is selected. This can be done by modifying DBAddin.xll.config or the referred DBAddinUser.config or DBAddinCentral.config (in this example the settings apply to environment 3):

<appSettings>
    <add key="ConfigName3" value="MSSQL"/>
    <add key="ConstConnString3" value="provider=SQLOLEDB;Server=Lenovo-PC;Trusted_Connection=Yes;Database=pubs;Packet Size=32767"/>
    <add key="ConfigStoreFolder3" value="C:\dev\DBAddin.NET\source\ConfigStore"/>
    <add key="ConnStringSearch3" value="provider=SQLOLEDB"/>
    <add key="ConnStringReplace3" value="driver=SQL SERVER"/>
    <add key="dbGetAll3" value="sp_helpdb"/>
    <add key="dbGetAllFieldName3" value="name"/>
    <add key="DBidentifierCCS3" value="Database="/>
    <add key="dbPwdSpec3" value="PWD="/>
    <add key="DBSheetConnString3" value="DRIVER=SQL SERVER;Server=Lenovo-PC;UID=sa;PWD=;Database=pubs;"/>
    <add key="DBSheetDefinitions3" value="C:\dev\DBAddin.NET\definitions"/>
    <add key="ownerQualifier3" value=".dbo."/>
    <add key="openingQuote3" value="["/>
    <add key="closingQuote3" value="]"/>
    <add key="closingQuoteReplacement3" value="]]"/>
    <add key="ConfigDocQuery3" value="SELECT databasename,case when objecttype='T' then objectname else parenttable end, case when objecttype='F' then objectname + ': ' else '' end + documentation FROM dbdocumentation ORDER BY case when objecttype='T' then objectname+'1' else parenttable+'2' end, objectname"/>
</appSettings>

Explanation:

Other Settings

Other (general) settings possible in DBAddin.xll.config (or DBAddinCentral.config/DBAddinUser.config):

    <add key="charBeforeDBnameConfigDoc" value="_" />
    <add key="CmdTimeout" value="30" />
    <add key="CnnTimeout" value="15" />
    <add key="ConfigSelect" value="SELECT TOP 10 * FROM !Table!" />
    <add key="ConfigSelectWithCount" value="SELECT (SELECT Count(*) FROM !Table!) Anzahl, TOP 10 * FROM !Table!" />
    <add key="ConfigSelect2" value="SELECT * FROM !Table!" />
    <add key="ConfigSelectPreference" value="WithCount" />
    <add key="connIDPrefixDBtype" value="MSSQL" />
    <add key="DBMapperCUDFlagStyle" value="TableStyleLight11" />
    <add key="DBMapperStandardStyle" value="TableStyleLight9" />
    <add key="DBSheetAutoname" value="True" />
    <add key="DebugAddin" value="False" />
    <add key="DefaultDBDateFormatting" value="0" />
    <add key="DefaultEnvironment" value="3" />
    <add key="disableSettingsDisplay" value="addin"/>
    <add key="DMLStatementsAllowed" value="True" />
    <add key="DontChangeEnvironment" value="False" />
    <add key="ExcelVersionForPivot" value="7" />
    <add key="legacyFunctionMsg" value="True" />
    <add key="LocalHelp" value="C:\dev\DBAddin.NET\docs\doc\rkapl123.github.io\DBAddin\index.html"/>
    <add key="localUpdateFolder" value="" />
    <add key="localUpdateMessage" value="New version available in local update folder, start deployAddin.cmd to install it:" />
    <add key="maxNumberMassChange" value="10" />
    <add key="repairLegacyFunctionsAutoOpen" value="True" />
    <add key="shortCutRefreshData" value="^R" />
    <add key="shortCutJumpButton" value="^J" />
    <add key="shortCutDeleteRow" value="^D" />
    <add key="shortCutInsertRow" value="^I" />
    <add key="updatesDownloadFolder" value="C:\temp\" />
    <add key="updatesMajorVersion" value="1.0.0." />
    <add key="updatesUrlBase" value="https://github.com/rkapl123/DBAddin/archive/refs/tags/" />

Explanation:

To change the settings, use the drop-down “settings” where you can modify the DBAddin.xll.config and the referred DBAddinCentral.config including XML validation. If you have multiple same named entries in your settings files, the last one is taken as the active setting. The settings dialog has a drop-down at its bottom providing all the available settings to be selected. On selection, the chosen setting is added to the bottom of the current open config. For + env settings, an input box will provide the possibility to set the number to be used instead of + env. There are other specific settings available for use with DB Functions, DB Modifications and DB Sheets, see there for more details.

Ribbon Menu: Environment, About Box, Settings, Log and setting DB Functions refresh prevention

image

The environment drop-down selector on top of the DBAddin settings Group allows to choose the environments defined with the ConfigNameN settings (and the associated data). If this drop-down is disabled, you can enable it by setting DontChangeEnvironmentto false`.

About Box, Updates and “fix legacy functions”

The About Box can be reached by clicking the small dialogBox Launcher in the right bottom corner of the DB Addin settings group of the DBAddin Ribbon:

image

You can get updates from here, in case there are new versions, this is shown with an orange background and a hint:

image

There is a possibility to set the future log events displayed (the starting value is set in the config file). You can also fix functions from the legacy DBAddin (VB6) using the “fix legacy funcs” button in case you decided to skip the possibility offered on opening a Workbook.

Settings

In the DBAddin settings Group, there is a drop-down “settings”, where you can modify the DBAddin.xll.config and the referred DBAddinUser.config and DBAddinCentral.config including XML validation. At the bottom of all settings dialogs, there is a drop-down showing the available settings. Those settings that are environment-dependent (ConfigNameN) are automatically displayed with the current selected environment number after the name. If you select the setting, it will be added at the bottom of all settings, you can cut/paste it anywhere you want. If the setting is already existing, an error message is displayed and the existing setting is highlighted. Any XML validation errors display the location with a row and column number, however these refer to the flat version of the text (without any word-wrap).

Custom workbook properties

Right besides the settings drop-down, there is a shortcut button to the Workbook properties (being the standard dialog Advanced Properties, accessible via File/Info) that allows you to change custom properties settings for DBAddin. A green check on that button shows that the custom property DBFskip is not set to true for this workbook, therefore DB functions are always refreshed on opening the workbook.

Log

To see the Log, there is a separate Button in the settings group of the DBAddin ribbon that also indicates the existence of warning log entries with a red exclamation mark.

DB Functions refresh prevention

To prevent DB Functions from refreshing, there is a toggle button in the settings group of the DBAddin ribbon. When activating it, no DB Function in any open Workbook will refresh (neither on recalculation nor when explicitly doing “refresh”). This setting is always set to disabled/refresh DB Functions when restarting the Add-in/Excel.

Tools

Besides the hierarchical menu “DBConfigs” (see DBFuncs User-doc) and the DBSheet Configuration (see DBSheets) there are other tools in the DB Add-in Tools group:

Purge

The DBListFetch’s and DBRowFetch’s target areas’ extents are stored in hidden named ranges assigned both to the calling function cell (DBFsource(Key)) and the target (DBFtarget(Key)). These hidden names are used to keep track of the previous content to prevent overwriting, clearing old values, etc. Sometimes during copying and pasting of DB Functions, these names can get mixed up, leading to strange results or a defect of the “jump” function. In these cases, there is a check/purge tool in the DB Add-in tools group, which may be used to “purge” these hidden named ranges in case of any strange behaviour due to multiple name assignments to the same area. This purging can be achieved by holding the Shift button while clicking check/purge. If the check/purge button is clicked while pressing the Ctrl Button, the hidden names used for the DB functions are unhidden and the Name manager is displayed. In case only the check/purge button is clicked, all problematic names (having #REF! errors, not having a source/target area available, etc.) are listed and a repair of these problems is offered. After this another check/repair for “orphaned” DB Functions that haven’t got any DBFsource names on their cells is offered.

Buttons

The button “Buttons” is used for switching design mode for DBModifier Buttons (identical to standard Excel button “Design Mode” in Ribbon “Developer tab”, Group “Controls”)

AdHocSQL Tool

Another tool is the entry of quick (ad hoc) SQL Commands in the combo box below the settings drop-down. Changing the combo box or clicking the dialog box launcher below it, opens the AdHoc SQL Command dialog:

image

Select Statements (beginning with select) are executed immediately, empty statements (using a space character in the combo-box) don’t execute anything, and everything else is regarded as a DML command and is only executed after confirmation:

image

For safety reasons, the DML commands are blocked until an additional setting <add key="DMLStatementsAllowed" value="True" /> is being set. This is indicated by an error message:

image

Results are shown below the SQL Command text entry, for row returning commands, the rows returned are shown including the time it took to finish the command at the bottom of the dialog. In case of an error the exception from the database command is displayed, for DML commands the records affected are shown (again including the time it took to finish the command):

image

You can modify the command in the AdHoc SQL Command dialog. By clicking Execute or pressing Ctrl-Return the command will be executed. To change the database context, use the drop-down DB:. To change the environment (connection string), use the drop-down Env:. To leave the dialog, hit ESC or click Close, in case the SQL command has changed, you will be prompted whether to add the new command to the combo-box:

image

To transfer the SQL command into the current cell, click Transfer or press Shift-Return. Depending on the type selected in the drop-down besides the Transfer button this will either

You can always interrupt long running commands by clicking Close (or hitting ESC) or Transfer. A question whether to cancel the interruption is provided then.

Issued commands are stored in the drop-down and persisted in the user settings after prompting the user, being reloaded at start-up of the Add-In (Excel). If you want to remove them, open the User-Settings as described in Settings and remove all unwanted entries starting with key="AdhocSQLcmd.." Also the chosen environment and the database context is stored along with each command (subsequent changes to the environment and database are stored without prompting), the transfer type is stored apart from that.

If the general DB Add-in environment is different from the stored environment of the command, a warning/question is displayed that allows to reset the environment to the general environment. If this is done, any changes to the environment and the database are not stored after closing the AdHocSQL Tool.

Building

All packages necessary for building are contained, simply open DBaddin.sln and build the solution. The script deployForTest.cmd can be used to quickly deploy the built xll and configurations to %appdata%\Microsoft\AddIns after choosing the solution configuration (Release or Debug).

Testing

Testing for MS SQL Server and other databases (MySQL, Oracle, PostgreSQL, DB2, Sybase and Access) can be done using the Testing Workbook “DBFuncsTest.xls”. To use that Testing Workbook you’ll need the pubs database, where I have scripts available for Oracle, Sybase, DB2, PostgreSQL and MySql here (the MS-SQLserver version can be downloaded here). I’ve also added a pubs.mdb Access database in the test folder.

When starting the Testworkbook, after waiting for the – probable – connection error, you have to change the connection string(s) to suit your needs (see below for explanations).

image

Several connection strings for “DBFuncsTest.xls” are placed to the right of the black line, the actual connection is then selected by choosing the appropriate short-name (drop-down) in the yellow input field. After the connection has been changed don’t forget to refresh the queries/DBforms by right clicking and selecting “refresh data”.

Road map

Following topics are still to be done:

Known Issues

History (from the very beginning)

API documentation

DBAddin API documentation generated with livedoc.