Introduction

DBAddin is two things in one:
  1. A COM/Automation Add-in for database querying by userdefined functions (DBFuncs). This is opposed to the Excel/Word integrated MS-Query, which is integrated statically into the worksheet and has some limitations in terms of querying possibilities and flexibility of constructing parameterized queries (MS-Query allows parameterized queries only in simple queries that can be displayed graphically). This also includes the possibility for filling "data bound" controls (ComboBoxes and Listboxes) with data from queries. Other useful functions for working with database data are included as well.
  2. A way to edit Database data directly in Excel using so called "DBSheets", which are special ExcelWorksheets that enable you to manipulate database data directly inside Excel (similar to MS Access table view). In DBSheets you can define a foreign key lookup resolution for foreign keys, so columns containing foreign IDs can be edited more easily. Another feature is the "jumping" to a referenced record in a foreign dependent table, if it's defined as a DBSheet in the same Workbook.
    A small, but useful additional database filling and updating tool is the "Mapper", which you can use to send an Excel range to the Database, updating/inserting the content into the table(s) given as arguments.

DBAddin has been tested extensively (actually it's in production) only with Excel XP and MS-SQLserver, other databases (MySQL, Oracle, PostgreSQL, DB2 and Sybase SQLserver) have just been tested with the associated Testworkbook "DBFuncsTest.xls".

To use that Testworkbook 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).

DBAddin is distributed under the GNU Public License V3.

This documentation is also available as a downloadable package here, for integration into DBAddins Help/About dialog, please see Installation.


DBFuncs

There are four ways to query data with DBAddin in Excel and one in Word:
  1. A (fast) list-oriented way using DBListFetch.
    Here the values are entered into a rectangular list starting from the TargetRange cell (similar to MS-Query, actually the QueryTables Object is used to fill the data into the Worksheet).
  2. A record-oriented way using DBRowFetch
    Here the values are entered into several ranges given in the Parameter list "TargetArray". Each of these ranges is filled in order of appearance with the results of the query.
  3. A "sentence" oriented way using DBCellFetch
    Here the returned values are concatenated into one "sentence" by using specific column separators and row separators. Additionally "final" column separators and row separators can be defined to present data in a natural sentence way (e.g.: 10, 20, 30 and 25. Here ", " is the column separator and " and " is the last row separator in a single column query.) 
  4. Loading data into userforms (Listbox and Dropdown) using DBMakeControl
    This is required for selecting values from a database. Max. 10 columns (incl. the invisible key column) can be defined in the associated query.
  5. In Word, the DBAddin supporting tool QueryBuilder creates a DATABASE field function that updates a Word table when being refreshed.

Three of those user-defined functions (DBListFetch, DBRowFetch and DBMakeControl) insert the queried data outside their calling cell context, which means that the target ranges can be put anywhere in the workbook (even outside of the workbook).

Additionally, some helper functions are available:

There is also a supporting tool available for building queries and placing them into the functions (based on MS-Query) and a "jump" feature that allows to move the focus from the DB function's cell to the data area and from the data area back to the DB function's cell (useful in complex workbooks with lots of remote (not on same-sheet) target ranges)

Using the Functions

DBListFetch

DBListFetch (Query, ConnectionString(optional), TargetRange, 
FormulaRange(optional), ExtendDataArea(optional),
HeaderInfo(optional), AutoFit(optional),
AutoFormat(optional), ShowRowNum(optional))

The select statement for querying the values is given as a text string in parameter "Query". This text string can be a dynamic formula, i.e. parameters are easily given by concatenating the query together from other cells, e.g. "select * from TestTable where TestName = "&A1

The query parameter can also be a Range, which means that the Query itself is taken as a concatenation of all cells comprising that Range, separating the single cells with blanks. This is useful to avoid the problems associated with packing a large (parameterized) Query in one cell, leading to "Formula is too long" errors. German readers might be interested in XLimits, describing lots (if not all) the limits Excel faces.  

The connection string is either given in the formula, or for standard configuration can be left out and is then set globally in the registry key [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\DBAddin\Settings\ConstConnString]

The returned list values are written into the Range denoted by "TargetRange". This can be

There is an additional FormulaRange that can be specified to fill “associated” formulas (can be put anywhere (even in other workbooks), though it only allowed outside of the data area). This FormulaRange can be

with formulas usually referring to cell-values fetched within the data area. All Formulas contained in this area are filled down to the bottom row of the TargetRange. In case the FormulaRange starts lower than the topmost row of TargetRange, then any formulas above are left untouched (e.g. enabling possibly different calculations from the rest of the data). If the FormulaRange starts above the TargetRange, then an error is given and no formulas are being refreshed down. If a FormulaRange is assigned within the data area, an error is given as well.

In case TargetRange is a named range and the FormulaRange is adjacent, the TargetRange is automatically extended to cover the FormulaRange as well. This is especially useful when using the compound TargetRange as a lookup reference (Vlookup).

The next parameter "ExtendDataArea" defines how DBListFetch should behave when the queried data extends or shortens:

The parameter headerInfo defines whether Field Headers should be displayed (TRUE) in the returned list or not (FALSE = Default).

The parameter AutoFit defines whether Rows and Columns should be autofitted to the data content (TRUE) or not (FALSE = Default). There is an issue with multiple autofitted target ranges below each other, here the autofitting is not predictable (due to the unpredictable nature of the calculation order), resulting in not fitted columns sometimes.

The parameter AutoFormat defines whether the first data row's format information should be autofilled down to be reflected in all rows (TRUE) or not (FALSE = Default).

The parameter ShowRowNums defines whether Row numbers should be displayed in the first column (TRUE) or not (FALSE = Default).

Connection String Special Settings:
In case the "normal" connection string's driver (usually OLEDB) has problems in displaying data with DBListFetch and the problem is not existing in conventional MS-Query based query tables (using special ODBC connection strings that can't be used with DB functions), then following special connection string setting can be used:

<Standard Connection String>;<special ODBC connection string>
Example:
provider=SQLOLEDB;Server=MULTIMEDIAPC;Trusted_Connection=Yes;Database=pubs;ODBC;DRIVER=SQL Server;SERVER=MULTIMEDIAPC;DATABASE=OEBFA;Trusted_Connection=Yes
This works around the issue with displaying GUID columns in SQL-Server.

DBRowFetch

DBRowFetch (Query, ConnectionString(optional), 
headerInfo(optional/ contained in paramArray), TargetRange(paramArray))
For the query and the connection string the same applies as mentioned for "DBListFetch".
The value targets are given in an open ended parameter array after the query, the connection string and an optional headerInfo parameter. These parameter arguments contain ranges (either single cells or larger ranges) that are filled sequentially in order of appearance with the result of the query.
For example:
DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs " &_
"where job_id = 1",,A1,A8:A9,C8:D8)
would insert the first returned field (job_desc) of the given query into A1, then min_lvl, max_lvl into A8 and A9 and finally job_id into C8.

The optional headerInfo parameter (after the query and the connection string) defines, whether field headers should be filled into the target areas before data is being filled.
For example:
DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs",_
,TRUE,B8:E8, B9:E20)
would insert the the headers (job_desc, min_lvl, max_lvl, job_id) of the given query into B8:E8, then the data into B9:E20, row by row.

The orientation of the filled rows is always determined by the first range within the TargetRange parameter array: if this range has more columns than rows, data is filled by rows, else data is filled by columns.
For example:
DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs",_
,TRUE,A5:A8, B5:I8)
would fill the same data as above (including a header), however column-wise. Typically this first range is used as a header range in conjunction with the headerInfo parameter.

Beware that filling of data is much slower than with DBlistFetch, so use DBRowFetch only with smaller data-sets.

DBCellFetch

DBCellFetch (Query, ConnectionString (optional), 
headerInfo(optional), colSep(optional), rowSep(optional), lastColSep(optional), lastRowSep(optional))
For the query and the connection string the same applies as mentioned for "DBListFetch".
headerInfo specifies if additionally the headings of query should be filled (default = none). colSep is the usual column separator (default: ","); rowSep is the usual row separator (default CR); lastColSep (if given) specifies how the last column is differently separated from others; and finally lastRowSep how the last row is differently separated from others. lastColSep and lastRowSep are replaced by colSep and rowSep resp. if not given.

Following Example should clarify everything (carriage returns are here just for clarity of display, actually this is rowSep, resp. lastRowSep):
header1 <colSep> header2 <colSep> header3 <colSep>... <colSep/lastColSep> headerN <rowSep>
value11 <colSep> value12 <colSep> value13 <colSep>... <colSep/lastColSep> value1N <rowSep>
...
v(M-1)1 <colSep> v(M-1)2 <colSep> v(M-1)3 ........ <colSep/lastColSep> v(M-1)N <rowSep/lastRowSep>
valueM1 <colSep> valueM2 <colSep> valueM3 <colSep>... <colSep/lastColSep> valueMN

DBMakeControl

DBMakeControl (Query, ConnectionString(optional), 
controlType(optional), headerInfo(optional), autoArrange(optional),
controlName(optional), dataTargetRange(optional), controlLocation(optional))

DBMakeControl creates DB bound Listboxes and Comboboxes (aka. Dropdowns). This is specified in the parameter controlType (0(default) is the Listbox and 1 the Combobox). 

headerInfo (default = True) additionally creates a gray textbox above the Listbox/Combobox with header information. Removing headers is only possible by setting headerInfo to False, if they are simply deleted they will be recreated as long as headerInfo is True.

autoArrange (default = False) resizes the field widths and total width automatically each time a recalc is done (This requires the use of a special font and font size, so setting autoArrange to True will override most of your customizations of the control).

Setting the controlName enables you to give a special Name (which has to be unique in the workbook however) during creation of the control. This is useful, if you further want to use the control in VBA (event) procedures. This is only regarded when creating the control with the first call of the function, any subsequent changes will not yield anything. If you want to "rename" an existing control, just delete the existing control (incl. the header), the next function call will create a control having the given controlName.

For DB bound controls the dataTargetRange corresponds to the LinkedCell of the control, here the selection value of the control is placed. The dataTargetRange must be given as a String literal, as using a range would lead to a direct circular dependency (indirect circular dependencies are allowed, there are some quirks with Listboxes however - see Known Issues). The dataTargetRange can also specify a different sheet using "<Tablename>!<CellAddress>".

controlLocation finally specifies similar to above the placement of the control. This also has to be a String literal, additionally it has to be the same sheet as dataTargetRange (requirement of MS forms).

Following is an example of a dropdown and a listbox created with following functions respectively:
Listbox:
=DBMakeControl("SELECT pub_id, pub_name, city, state, country FROM publishers";
activeConnString;;TRUE;TRUE)

Combobox:
=DBMakeControl("SELECT emp_id, fname, minit, lname, job_id,
convert(varchar(10),job_lvl) job_lvl, pub_id, hire_date FROM
employee";activeConnString;1;TRUE)


The DB bound control retain the selection during a refresh (e.g. saving/reopening the workbook).

chainCells(Range)

chainCells(ParameterList)

chainCells "chains" the values in the given range together by using "," as separator. It's use is mainly to facilitate the creation of the select field clause in the Query parameter, e.g.

DBRowFetch("select " & chainCells(E1:E4) & " from jobs where job_id = 1",_
,A1,A8:A9,C8:D8)

Where cells E1:E4 contain job_desc, min_lvl, max_lvl, job_id respectively.

concatCells

concatCells(ParameterList)
concatCells concatenates the values in the given range together. It's use is mainly to facilitate the building of very long and complex queries:
DBRowFetch(concatCells(E1:E4),,A1,A8:A9,C8:D8)

Where cells E1:E4 contain the constituents of the query respectively.

concatCellsSep

concatCellsSep(separator, ParameterList)
concatCellsSep does the same as concatCells, however inserting a separator between the concatenated values. It's use is the building of long and complex queries, too:
DBRowFetch(concatCellsSep(E1:E4),,A1,A8:A9,C8:D8)

Where cells E1:E4 contain the constituents of the query respectively.

All three concatenation functions (chainCells, concatCells and concatCellsSep) work with matrix conditionals, i.e. matrix functions of the form: {=chainCells(IF(C2:C65535="Value";A2:A65535;""))} that only chain/concat values from column A if the respective cell in column C contains "Value".

Both concatCells and concatCellsSep have a "Text" sibling that essentially does the same, except that it concats the displayed Values, not the true Values. So if you want to concatenate what you see, then concatCellsText and concatCellsSepText are the functions you need.

DBinClause

DBinClause(ParameterList)

Creates an in clause from cell values, strings are created with quotation marks, dates are created with DBDate (see there for details, formatting is 0).

DBinClause("ABC", 1, DateRange)
Would return  ”(‘ABC’,1,’20070115’)” , if DateRange contained 15/01/2007 as a date value.

DBString

DBString(ParameterList)

This builds a Database compliant string (quoted) from the open ended parameter list given in the argument. This can also be used to easily build wildcards into the String, like

DBString("_",E1,"%")

When E1 contains "test", this results in '_test%', thus matching in a like clause the strings 'stestString', 'atestAnotherString', etc.

DBDate

DBDate(DateValue, formatting (optional))

This builds from the date/datetime/time value given in the argument based on parameter formatting either

  1. (default formatting = DefaultDBDateFormatting Setting) A simple datestring (format 'YYYYMMDD'), datetime values are converted to 'YYYYMMDD HH:MM:SS' and time values are converted to 'HH:MM:SS'.
  2. (formatting = 1) An ANSI compliant Date string (format date 'YYYY-MM-DD'), datetime values are converted to timestamp 'YYYY-MM-DD HH:MM:SS' and time values are converted to time time 'HH:MM:SS'.
  3. (formatting = 2) An ODBC compliant Date string (format {d 'YYYY-MM-DD'}), datetime values are converted to {ts 'YYYY-MM-DD HH:MM:SS'} and time values are converted to {t 'HH:MM:SS'}.

An Example is give below:

DBDate(E1)

Of course you can also change the default setting for formatting by changing the setting "DefaultDBDateFormatting" in the global settings

"DefaultDBDateFormatting"="0"

Plookup

Plookup(inputRange, selectionRange, targetRange)
Does a pattern weighted lookup of values in range inputValues in pattern lookup area selectionRange,  returns the values contained in found row of range targetRange (if entered as a matrix function).

In case Plookup is not entered as a matrix function, Plookup returns the first column of the matching row of targetRange.
If more than one row matches, always return values from first matching row.

Example:

selection range | target range
1 * 3 4 5 | 11 12 13
* 2 3 * 5 | 21 22 23
* * 3 * 5 | 31 32 33
1 2 3 4 * | 41 42 43
1 2 3 4 5 | 51 52 53

input: 1 2 3 4 x > matches 4th row -> returns 41 42 43
input: 1 2 3 4 5 > matches 5th row -> returns 51 52 53
input: x y 3 z 5 > matches 3rd row -> returns 31 32 33
input: x 2 3 z 5 > matches both 2nd and 3rd row -> returns 21 22 23 because row 2 is more precise

Modifications of DBFunc Behaviour

There are some options to modify
of DB functions data area within a Workbook.

You can set these options in Excel's Custom Properties (Menu File/Properties, Tab "Customize"):

Skipping Data Refresh when opening Workbook


To disable refreshing of DBFunctions when opening the workbook create a boolean custom property "DBFSkip" set to "Yes" (set to "No" to disable skipping).

Prevent Storing of retrieved Data in the Workbook


To prevent storing of the contents of a DBListFetch or DBRowFetch when saving the workbook create a boolean custom property "DBFCC<DBFunctionSheet!DBFunctionAddress>" set to "Yes" (set to "No" to reenable storing). This clears the data area of the respective DB function before storing and refreshes it afterwards (Note: If the custom property "DBFSkip" is set to "Yes", then this refreshing is skipped like when opening the Workbook)

Example: The boolean custom property "DBFCCTable1!A1" would clear the contents of the data area for the DBFunction entered in Table1, cell "A1".

To prevent storing of the contents for all DBFunctions create a boolean Custom Property "DBFCC*" set to "Yes".

Excel however doesn't fill only contents when filling the data area, there's also formatting being filled along, which takes notable amounts of space (and saving time) in a workbook. So to really have a small/quick saving workbook, create a boolean custom property "DBFCA<DBFunctionSheet!DBFunctionAddress>" set to "Yes" (set to "No" to reenable storing). This clears everything in the the data area of the res

Example: The boolean custom property "DBFCATable1!A1" would clear everything from the data area for the DBFunction entered in Table1, cell "A1".

To prevent storing of everything (incl. formats) for all DBFunctions create a boolean Custom Property "DBFCA*" set to "Yes".

Supporting Tool Query Builder

There is a supporting tool for building queries. This is - currently - reusing following visual query construction tools as a frontend to build the query:

The query builder is invoked by right clicking on any cell in an open excel worksheet and selecting "build DBfunc query". This either

The resulting query can be inserted as one of the following functions:

In case you keep the SHIFT key pressed while right clicking, the visual query construction tool is skipped, leading directly to the following dialog. This is used for capturing queries from other sources (MS Query Analyzer or other editors, copy/pasting the query into the Worksheet) and just "wrapping" the DB functions around the inserted query.

Following dialog is used to achieve this:


First the DB function to be inserted has to be chosen by selecting in the "DB function" choice box. Then, depending on the above choice, the target cell for the function formula, resp. the target cells for the parameters of the function can be selected (similar to the RefEdit boxes in Excel you can select cells directly in the Worksheet, pressing F3 brings a list of Names defined in the Workbook you can insert):

Other possible choices are:

For DBCellFetch the Dialog looks a bit different allowing for the entry of colSep, rowSep, lastColSep and lastRowSep:



For DBMakeControl, the control type and the Control Location can be additionally defined:



If the query builder is invoked on a cell containing a DB function, the query in the first argument is evaluated and directly taken to the visual query construction tool.

One major restriction concerning the use of the visual query construction tools with the query builder tool is the inability to build parameterized queries inside the visual query construction tool, this feature would however not be really useful as DB functions allow full SQL integration into worksheet formulas.

In case DBAddin is used within Word, a very spare dialog is shown, just allowing additional displaying header information:

Global Connection Definition and Query Builder with MSQuery

There are two possibilities of connection strings: ODBC or OLEDB. ODBC hast the advantage to seamlessly work with MS-Query, native OLEDB is said to be faster and more reliable (there is also a generic OLEDB over ODBC by Microsoft, which emulates OLEDB if you have just a native ODBC driver available).

Now, if using ODBC connection strings (those containing "Driver="), there is a straightforward way to redefine queries directly from the cell containing the DB function: just right click on the function cell and select "build DBfunc query". Then MS-query will allow you to redefine the query which you can use to overwrite the function's query.

If using OLEDB connection strings, MS-query will try to connect using a system DSN named like the database as identified after the DBidentifierCCS given in the standard settings (see Installation section).

The DBidentifierCCS is used to identify the database within the standard default connection string, The DBidentifierODBC is used to identify the database within the connection definition returned by MS-Query (to compare and possibly allow to insert a custom connection definition within the DB function/control). Usually these identifiers are called "Database=" (all SQLservers, MySQL), "location=" (PostgreSQL), "User ID/UID" (oracle), "Data source=" (DB2)

Additionally the timeout (CnnTimeout, which can't be given in the functions) is also defined in the standard settings.

Supporting Tool Cell Config Deployment

To easen the distribution of complex DB functions (resp. queries), there is a special deployment mechanism in the DBAddin Commandbar: Saving of DB function configurations can be done with the button "Save Config", whereas for loading there are two possibilities: The button "Load Config" (displaying a simple load file dialog) and a tree-dropdown menu below "DBConfigs" that displays the file hierarchy beneath ConfigStoreFolder for easy retrieval of the configs.

Creating configs

"Save Config" asks you to select cells you want to store for others to import into their sheet. This is done by either selecting one contiguous area or by Ctrl+clicking the single cells you want to add to the distribution. Finally a Save Dialog asks you for the filename where these cell contents/formulas should be stored. If you choose an existing file, you're asked whether the config should be appended to that file.

Other users can simply look up those config files either with "Load Config" or the hierarchical menu "DBConfigs", which is hierarchically showing all config files under the ConfigStoreFolder (set in the global settings). Using folders, you can build categorizations of any depth here.

There is a helping script ("createTableViewConfigs.vbs") to create a DBListFetch with a "select TOP 10000 * from ..." for all tables and views in a given database (In order for that script to work, the ADO driver has to support the "OpenSchema" method of the connection object). The working of that script is quite simple: It takes the name of the folder it is located in, derives from that the database name by excluding the first character and opens the schema information of that database to retrieve all view and table names from that. These names are used to build the Excel and Word config files (XCL/WRD).

DBAddin has a convenient feature to hierarchically order those config files furthermore, if they are consistently named. For this to work, there either has to be a separation character between "grouping" prefixes (like "_" in "Customer_Customers", "Customer_Addresses", "Customer_Pets", etc.) for grouping similar objects (tables, views) together or "CamelCase" Notation is used for that purpose (e.g. "CustomerCustomers", "CustomerAddresses", "CustomerPets").

There is one registry setting and two registry setting groups to configure this further hierarchical ordering:
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\VB and VBA Program Settings\DBAddin\Settings]
"specialConfigStoreFolders"="<pathName>:.pubs:.Northwind"
"<pathName>MaxDepth"="1"
"<pathName>Separator"=""
"<pathName>FirstLetterLevel"="True"
If you add the (sub) foldername to "specialConfigStoreFolders" (colon separated list) then this subfolder is regarded as needing special grouping of object names. The separator ("_" or similar) can be given in  "<pathName>Separator", where <pathName> denotes the path name used above in "specialConfigStoreFolders". If this is not given then CamelCase is assumed to be the separating criterion.

The maximum depth of the sub menus can be stated in "<pathName>MaxDepth", which denotes the depth of hierarchies below the uppermost in the <pathName> folder (default value is 10000, so practically infinite depth).

You can add another hierarchy layer by setting "<pathName>FirstLetterLevel" to "True", which adds the first letter as the top level hierarchy.

You can decide for each subfolder whether it's contents should be hierarchically organized by entering the relative path from ConfigStoreFolder for each subfolder in "specialConfigStoreFolders", or you can decide for all subfolders of that folder by just entering the topmost folder in "specialConfigStoreFolders". Beware that the backslash (path separator) in <pathName> needs to be entered quoted (two "\" !) to be recognized when importing the registry key files!

In some instances the Windows API doesn't return the directories/filenames in alphabetical order (e.g. in my SAMBA network), so you'd need to set the following registry setting to "True", which does a sort on the config folder's content before putting it into the tree dropwdown:
"sortConfigStoreFolders"="False"

Inserting configs

If the user finds/loads the relevant configuration, a warning is shown and then the configured cells are entered into the active workbook as defined in the config, relative to the current selection. The reference cell during saving is always the left/uppermost cell (A1), so anything chosen in other cells will be placed relatively right/downward.

Cells in other worksheets are also filled, these are also taking the reference relative to the current selection (when loading) or cell A1 (when saving). If the worksheet doesn't exist it is created.

Currently there are no checks (except for Excels sheet boundaries) as whether any cells are overwritten !

Refreshing the config tree

To save time when starting up DBAddin/Excel, the previously used way of refreshing the config tree (when starting up Excel/DBAddin) was abandoned in favour of a better one: now each user can decide when to refresh the config tree for himself with the button "refresh Configs" right below the menu button "DBConfigs".

This refreshing also restores "theDBSheetAppHandler" object as a side effect, which re-enables DBSheet treatment after sporadic invalidation of this object (needed until the reason for this has been found out).

Installation

Dependencies

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

Installation is done by the installer (you need to have administrative rights, if you want to install from your account without switching to the admin account you can do runas /user:administrator "msiexec.exe /i <Path_Of_DBAddinSetup.msi>" to install or runas /user:administrator "msiexec.exe /x <Path_Of_DBAddinSetup.msi>" to uninstall), during the process of installation, you're asked whether to start addDBAddinToAutomationAddins.xls, which adds the Functions to Excel's Addin List. 

If you prefer not to start Excel/add the functions that point, please add the Automation Addin manually in Excel: This is done by calling Tools/Add-Ins... and then clicking Automation. Here you're presented with all registered Automation Servers that could be connected to Excel (most don't make sense of course). Search for "DBAddin.Functions" and add by clicking "OK".

After installation you'd want to adapt the standard default connection string (ConstConnString) that is globally applied if no function-specific connection string is given. This can be done by modifying and importing DBAddinSettings.reg into your registry.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\VB and VBA Program Settings\DBAddin\Settings]
"ConstConnString"="provider=SQLOLEDB;Server=OEBFASRV02;Trusted_Connection=Yes;Database=InfoDB;Packet Size=32767"
"DBidentifierCCS"="Database="
"DBidentifierODBC"="Database="
"CnnTimeout"="15"
"DefaultDBDateFormatting"="0"
"ConfigStoreFolder"="\\\\Oebfacoat\\dfs\\SOFTWARE\\Datenbank\\ConfigStore"
"LocalHelp"="\\\\Oebfacoat\\dfs\\SOFTWARE\\VB\\DBAddin\\documentation\\HelpFrameset.htm"

"enforceRefresh"="True"
"specialNonNullableChar"="*"
"tblPlaceHolder"="!T!"
"maxRowsToFetch"="1000"
"testHeaderColor"="45"
"prodHeaderColor"="0"
"noErrColor"="-4142"
"internalErrColor"="45"
"dataErrColor"="3"
"reqFieldsColor"="15"
"primColFieldsColor"="15"
"calcColFieldsColor"="41"
"conflictColor"="8"
"closeMsg1"="Sollen Ihre Änderungen in '"
"closeMsg2"="' gespeichert werden?"
"TrueFalseSelection"="WAHR;FALSCH"
"DBSheetDefinitions"="\\\\Oebfacoat\\dfs\\SOFTWARE\\Datenbank\\DBSheets\\DBSheetDefinitions"
"DBConnFileName"="\\\\Oebfacoat\\dfs\\SOFTWARE\\VB\\DBAddin\\DBConns.xml"

The other settings:

The remaining settings belong to DBSheets and are explained there.

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).

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 shortname (dropdown) 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".

Points of Interest

The basic principle behind returning results into an area external to the Database query functions, is the utilisation of the calculation event (as mentioned in and inspired by the excelmvf project, see http://www.codeproject.com/macro/excelmvf.asp for further details), as Excel won't allow ANY side-effects inside a UDF.

There is lots of information to be carried between the function call and the event (and back for status information). This is achieved by utilising a so-called "calcContainer" and a "statusMsgContainer", basically being VBA classes abused as a simple structure that are stored into global collections called "allCalcContainers" and "allStatusContainers". The references of the correct calcContainers and statusMsgContainers are the Workbook-name, the table name and the cell address of the calling functions which is quite a unique description of a function call (this description is called the "callID" in the code).

Below diagram should clarify the process:


The real trick is to find out when resp. where to get rid of the calc containers, considering Excel's intricate way of invoking functions and the calc event handler (the above diagram is simplifying matters a bit as the chain of invocation is by no way linear in the calculations in the dependency tree).

Excel sometimes does additional calculations to take shortcuts and this makes the order of invocation basically unpredictable, so you have to take great care to just work on every function once and then remove the calcContainer.

After every calculation event the working calcContainers are removed, if there are no more calcContainers left, then allCalcContainers is reset to "Nothing", being ready for changes in input data or function layout. Good resources for more details on the calculation order/backgrounds is Decision Model's Excel Pages, especially Calculation Secrets (http://www.decisionmodels.com/calcsecretsc.htm).

The DBListFetch's target areas' extent is stored in hidden named ranges assigned both to the calling function cell (DBFsource<Key>) and the target.

There is a procedure in the Functions module, 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 behaviour usually arises, when redefining/adding dbfunctions that refer to the same target area as the original/other dbfunction. The procedure is called "purge" and may be invoked from the VBA IDE as follows:

Sub purge()
  Set dbfuncs = CreateObject("DBAddin.Functions")
  dbfuncs.purge
End Sub

Known Issues / Limitations



DBSheets

DBSheet is an Excel based solution to modify Database data, i.e. to insert, update and delete rows for a defined set of fields of a given table.

The modifications are done in so-called Database sheets (DBSheets), which are filled by using a specified query. The DBSheet contains the "normal" Table data (direct values) and indirect lookup values for updating foreign key columns. The allowed IDs and the visible lookup values for those foreign key columns are stored in a hidden sheet.

Work in the DBSheet is done with context menus (right mouse) and shortcut keys to the context menu items:

There is also a supporting tool "DBSheet creation" (context menu "edit DBsheet definition", possible only in top/leftmost cell) available for building and editing DBSheets.

In the following sections, the major capabilities of DBSheet are presented, followed by a description of the supporting tool "DBSheet creation".

Background

Prerequisites for understanding this documentation and using DBSheets is a basic proficiency with SQL and database design (good books on this topic are "The Practical SQL Handbook: Using Structured Query Language (3rd Edition)" and its successor "The Practical SQL Handbook: Using SQL Variants (4th Edition)", available free online courses are:

Working with DBSheets

I use the enclosed the test workbook called "Pubs.xls" as an example to guide through the possibilities of DBSheets.

This Workbook uses the pubs database for MSSQL, (available for download/installation from Microsoft or search "pubs download" on Microsoft.com, if this is not installed on your DB server already). For MySQL, Sybase, Oracle and DB2, I transported the pubs database myself, you can download installation scripts here.

Features of DBSheets

DBSheets consist of one sheet containing the table data, the header and the DBSheet definition reference (contained in the comment in the top/leftmost cell), one (hidden) sheet containing the (foreign table) lookups and finally another hidden sheet containing the table data as of last refresh for identifying changed data.

The header row and the primary key column(s), which are located leftmost and shown in gray, may not be modified, except for new rows where there is no primary key yet. The Sheet can however may be unlocked anytime by either using the standard Sheet unlock menu or from the DBAddin toolbar selecting "DBSheet/Unlock DBsheet". Refreshing the DBSHeet locks it back again.

Mandatory columns (not allowing empty (null) values) are shown with a gray background, numerical value, date value and lookup columns are restricted with excels cell validation.

Editing Data:

Data is changed simply by editing existing data in cells.

This marks the row(s) to be changed.

Inserting is done by entering data into empty rows, either allowing the database to introduce a new primary key by leaving this column empty (only possible for singular primary keys) or by setting the primary key yourself. This marks the row(s) to be inserted.

Empty rows can be created within the existing data area by using context menu "insert row" or pressing Ctrl-i in the row where data should be inserted. If multiple rows are selected then the same amount of empty rows is inserted.

Deleting is done using context menu "delete row" or pressing Ctrl-d in the row to be deleted. This marks the row(s) to be deleted.

To make the editions permanent, save the workbook (save button or Ctrl-s). After a warning message, the current DBSheet is stored to the database, producing warnings/errors as the database (or triggers) checks for validity.

When editing, the constraints of the underlying data definition have to be observed, i.e. foreign keys must be entered using the restricted list provided as possible foreign values, numerical data cells do not allow character data, etc...

There are four context menus (five in foreign lookup columns):

You can specify per sheet whether to not have any warnings about refreshing, deleting, inserting and storing data by checking "ALWAYS OK & don't show for this DB sheet !" in the appearing confirmation message box:

The refreshing of data when switching between DBsheets (entering/activating a DBsheet) is usually enforced (setting enforceRefresh = True in the Installation Settings this is always reset when manually refreshing DBSheets and on start-up of the Addin). This however can be disabled by unchecking "Auto DBSheet Refresh" in the DBSheet menu (for details see here). In case "Cancel" is clicked, even though the refresh is enforced for that operation, then the following information is displayed:

If calculated columns were defined, the topmost formula in that column is always preserved when refreshing the DBSheet, being auto-filled down after every refresh. When saving, only those rows are stored where underlying (real) data has been edited, changed data from calculations are not stored. This behaviour can be overruled by setting the DBsheet parameter "enforce save of all rows when calc columns", which saves all rows as edited, regardless whether they have been really edited or not. Beware that this behaviour makes saving very slow for large tables…

The comment in cell A1 contains the reference to the DBSheet's definition file, a unique identifier and the parameter-> range assignments for parameterized DBSheets.

The DBSheet definition file contains following information in XML format:

  1. the query for fetching the main table data to be edited
  2. the connection ID referring to the connection definition in the  global connection definition file. The connection definition contains the connection string, information on how parts of the connection string can be interpreted (database, password), how the collection of available databases can be retrieved and the windows users permitted to create/edit DBSheet definitions.
  3. the foreign key lookups. These consist of a lookupname, being the name of the column and either a select statement or a list of values. The select statement has to return exactly two columns returning the lookup values first and then the IDs to be looked up. (the main table's column value set should be contained in those, so every column value can be looked up).
    Duplicates naturally should be strictly avoided in the return set of a query for a referential foreign key column as they would lead to ambiguities and thus will lead to errors when generating the DBSheet.
  4. The primary column count and the start of the calculated columns (0 for no calculated columns).

The parameter-> range assignments and some end-user definable parts can be edited in the DBSheet parameters dialog, invoked by selecting the menu "DBSHeet/Parameters":

This dialog box allows you to edit the window pane freezing presets, the enforcing of saving all rows (for calculated columns). Also, all the "don't show" settings done during editing, refreshing, saving etc. of DBsheets can be reset again ("don't show" settings with default action "Cancel" are shown as a greyed out tick and can be set by right clicking on the appropriate checkbox). Autoformatting cells down can be enabled to copy down the formats set in the first row of the DBsheet. Autofilters can also be enabled here.

In the left-hand part you can define parameter range names to be used for parameterized DBSheet queries. The content of the named ranges is used to replace the quotation marks used in parameterized DBSheet queries, either with quoting (strings) or without (numerical data or prepared parts, like in clauses etc.). The parameters are replaced in order of appearance.

Conflict resolution when multiple users edit the same row:

Two or more Persons can edit a Table simultaneously, the last who saves data has to resolve any edit conflicts (optimistic row locking). In case of conflict, the first cell of the corresponding row is marked turquoise and for every cell that was edited between opening of the DBsheet (or refresh) and saving by someone else the following message is given: "Data (<Field>) to be stored (<Value>) was also edited by somebody else (<ValueChanged>). Do you want to keep those edits ('No' overwrites them with your changes) ?", which you can answer accordingly.

The same applies for deletes as soon as the first changed Field is detected in the record that is about to be deleted: "Record to be deleted was edited by somebody else (<Field>:<ValueChanged>). Do you want to keep those edits ('No' deletes the record definitely)?"

Looking up foreign table records:

As the lookup information might sometimes not be sufficient to identify a record and a user might want to change the foreign record while still viewing the primary table data, there is another possibility too look up foreign records: In a foreign lookup column, use the context menu "show/hide foreign Record" (Ctrl-b) to display the foreign table with the associated foreign record being highlighted below the primary table pane.

The primary record is put topmost in the original window, the foreign record is selected and shown in the – activated – foreign table pane. Another activation of "show/hide foreign Record" (or pressing Ctrl-b) within the foreign table pane hides the foreign table pane. Data can be edited in both panes!

Supporting Tool "DBSheet Creation"

Usually you start with defining or selecting the connection for your DBSheet. For a new connection you need to specify the connection string, containing the database and the server/instance where the table to be edited is located. The connection timeout can be chosen as well.

Important for running DBSheet in a test environment is the next field allowing to specify another connection ID containing the definition for the associated test environment (either a different database or a different instance/servername).

The entries <DBisUserscheme/> and <dbneedPwd/> are for Oracle databases where DBAddin sometimes has to switch to the scheme and therefore needs a password. After these two database specific fields, the command for retrieving all databases/schemas from the database can be entered (for sql server this is "sp_helpdb" for Oracle it's "select username from sys.all_users". If the result of this command has more than one column (like in sqlserver), you have to give the fieldname where the databases can be retrieved from.

After that, the string that separates database/schema name from the table has to be given (e.g. ".dbo." or ".." or simply "."). Finally the windows user(s) allowed to edit DBSheet connections are given in a comma-separated list.

If all this has been filled in properly, you can store the connection definition and test the connection. In case of successful connecting to the database, the other two tabs "Columns" and "Creation" become available and you can proceed to selecting a table on the tab "Columns". "Load DBSheet def from File" is just a shortcut here to directly load a stored definition without defining/testing a connection!

The connection informations are stored in a global connection definition file usually called "DBConns.xml". This can be placed on a network drive to be centrally available for all DBSheets users. You can also use a text editor to edit your connections, however please take care not to introduce newlines within the xml elements, this disturbs reading into the DBSheet Creation tool.

Next, select the main table for which a DBsheet should be created in the dropdown "Table", which fills the available fields of the table into the dropdown "Column". Once a column has been chosen, the Connection tab becomes unavailable. Only clearing ALL columns from the Columns definitions will allow a change to the connection again.

After that you can start to populate the columns that should be edited by selecting them in the dropdown "Column" and adding them to the DBSheet column list with clicking "add to DBSheet" (or pressing Alt-a). A quick way to add all available columns is to click "add all Fields" (or pressing Alt-f)

If the column is required to be filled (non-null) then an asterisk is put in front of it (shown also in the list of columns below, the asterisk is removed however when generating/customizing queries and lookup queries). The first column is automatically set to be a primary key, any subsequent columns that should serve as primary key can be marked as such by ticking "is primary key?". Primary columns must always come first in a DBSheet, so any primary key column after a "non-primary key" column is prevented by DBsheet Creation.

If the column in question should be a lookup from a foreign table then tick the box "is foreign column?" to enable the foreign table dropdowns:

The first dropdown, "Foreign Database" allows you to select the database of the foreign table carrying the lookup information. After selecting the foreign database, the foreign table can be selected in dropdown "Foreign Table". After selecting the foreign table, the key of the foreign table can be selected in dropdown "Foreign Table Key". This key is used to join the main table with the foreign table, in case it is an outer join (allowing for missing entries in the foreign table), tick "is outer join?".

To finish foreign table lookup, select the Foreign Table Lookup Column serving as a meaningful description for the foreign key (usually some "Name", "Code" or "Description" field).

A calculated column (not being read from the DB but only stored to it) can be specified by ticking "is calculated column?". Calculated columns always must come last in a DBSheet, so any calculated column before a "non-calculated" column is prevented by DBsheet Creation.

You can always edit the columns already stored in the DBSheet-Column list by selecting a line and changing the values in the dropdowns. The changes are reflected in the DBSheet-Column list after clicking the DBSheet-Column list. Click on the button "abort column edit" to start adding again.

You can change the order of columns by clicking on the up/down buttons in the middle of the dialog.

You can copy/paste the definitions between columns by pressing Ctrl-C on a column to be copied and Ctrl-V on the column where the definitions should be pasted. Everything except the column name is pasted.

Removing columns is possible by clicking "remove from DBSheet", you can clear the whole DBsheet columns by clicking "clear all Fields".

In case you want to undo all your changes, simply exit the form and start it again by selecting "DBSheets/Create/Edit Definition". This takes the currently stored definition from the definition file as given in the active DBsheet and displays them again for editing.

When dealing with foreign column lookups or other lookup restrictions (see below), you can edit the definition of the lookup directly by editing the Lookup Query field:

You can put any query into that, it just has to return the lookup value first and the ID to be looked up second. Duplicates should be strictly avoided in the return set of this query as they would lead to ambiguities and will produce error messages when generating the DBSheet.

Customizations of the restriction field should observe a few rules to be used efficiently (thereby not forcing the DBSheet creating person to do unnecessary double work): First, any reference to the foreign table itself has to use the template placeholder !T!, which is then replaced by the actual table enumerator (T2..T<N>, with T1 always being the primary table). Complex select columns (anything that has more than just the table field) must have an alias associated, which has to be named like the foreign table key. If that is not the case, DBAddin wont be able to associate the foreign column in the main table with the lookup id, and thus displays following error message:

The connection between the lookup queries and the main query is as follows:

  1. The first column part of the lookup query select statement is copied into the respective field in the main table query (therefore the above restriction)
  2. The foreign lookup table and all further additional tables needed for the lookup query are joined into the main query in the same way as they are defined in the lookup (inner/outer joins), WHERE clauses are added to those joins with AND.

You can always test the foreign lookup query by clicking on "Test Lookup Query" besides the restriction field. This opens an excel sheet with the results of the lookup query being inserted (max. maxRowsToFetch one after another). This Testsheet can be closed again either by simply closing it, or quicker by clicking on the same button (that now changed its caption to "remove Testsheet") again.

Following diagram should clarify the connection between lookup queries and the main query:

You can also have a lookup column without defining a foreign table relation at all. This is done by opening the lookup query field using the "is foreign ?" box and defining the lookup in that field. Here the same applies as already said above. Beware that the first column is always the lookup value and the second always the id (the value that is actually being stored into the table), so in a "relationless" lookup, both columns bear the actual values. This means that a "relationless" restriction usually will look like "select lookupCol, lookupCol from someTable…":

Also remember that lookups always check for uniqueness, so in case there are duplicate lines to be expected, an additional "distinct" clause will avoid the consequential error messages: "select distinct lookupCol, lookupCol from someTable…" (this approach is not to be used with foreign key lookups, as the exact/correct id should always be found out. Instead try to find a way to make the lookup values reflect their uniqueness, e.g. by concatenating/joining further identifiers, as in "select lookupCol+additionalLookup, lookupID…" )

Even a lookup column without a lookup query is possible by just listing the possible values after the in the restriction separated by "||", e.g.: Yes||No||Maybe. IDs are not required here, just the values are sufficient:

The DBSheet is created in four steps:

  1. First, all restrictions should have been either generated or entered/edited directly.
  2. Then the main query for retrieving the fields to be edited has to be generated. It can also be further customized, if needed.
    However bear in mind that every change in the columns requires either an overwriting of the customizations and subsequent redoing them (cleaner) or constantly keeping the two synchronized. For customizing the restriction part (Where Parameter Clause), a separate text input field can be used that allows the query to be regenerated without any intervening. Simply enter the restriction part (the Where clause without the "Where") and create the Query again.
  3. Then the DBSheet Definition needs to be stored, simply click "save DBsheet def", which allows you to choose a filename (if it hasn't been already saved. The file choice dialog can always be achieved by clicking "save DBSheet def As..."). Afterwards the information currently contained in the DBSheet columns, the DBsheet query and other information is stored in a DBSheet definition file (extension: xml)
  4. Finally, the DBSheet can be created with "assign DBsheet def to active sheet", entering the reference to the DBSheet definition file to the currently opened Excel Worksheet (this only works if a workbook/sheet is active).

You can always test the main table query by clicking on "test DBSheet Query" above the query definition. This opens an excel sheet with the results of the main table query being inserted (maxRowsToFetch rows are fetched, then you're asked to continue with another maxRowsToFetch). This Testsheet can be closed again either by simply closing it, or quicker by clicking on the same button again (that now changed its caption to "remove Testsheet").

DBSheet Menu

There is a menu to invoke special functions useful mostly (sometimes only) for DBSheets:

Unchecking "Auto DBSheet Refresh" disables the automatic, non-overridable refresh of DBSheets. This does not refresh lookups, data and edit (insert/update/delete) marks. So beware of using this feature as it leaves the DBSheet in a rather undefined state. This is mostly useful for copying large amounts of data from other sheets. So after disabling the automatic refresh you can change to the workbook/sheet with the data to be copied, select+copy the range, change back to the target DBSheet and paste.

For copying primary key values to a DBSheet, as the refresh always locks the DBSheet's primary column(s) along with data refresh you also need to first unlock the DBSheet (see below).

With "Unlock DBSheet" you can quickly unlock the current DBSheet (there's no password used by default for locking DBsheets, if that's required then I'd need to include a password setting here). Uses for unlocked DBSheets are occasions where you'd need to overwrite primary key values.

Using "Assign DBSheet", you can assign a DBSheet definition file directly without using the Create/Edit Definition Dialog. If invoked without an active workbook, it creates a new workbook and then inserts the DBSheet reference definition into the single sheet. In case there is already an active workbook, it inserts the DBSheet reference definition in the active sheet. No checking of existing data is done, so watch out !!

With "Parameters", you can set the parameters of the current DBSheet (further details see here

"Create/Edit Definition" opens the supporting Tool "DBSheet Creation" (further details see here

Installation

Additional to Installation, you might also want to customize several of the following settings in DBAddinDefaults.reg:

Testing

DBSheets only has been tested on Excel XP (2002) and MS SQL Server 2000 respectively Oracle 10g.

There is a separate test environment facility, meaning that if there is either a "Test" folder somewhere in the path of the DBSheet Workbook or ".Test." inside the DBSheet Workbooks filename (e.g. "Pubs.Test.xls"), the DBAddin will use a connection with a connection id as the one defined in "testid" 

Known Issues/Limitations

Mapper

Mapper is an object that you can use to save Excel Range data to database table(s). The function that is used to do that is 

saveRangeToDB

saveRangeToDB(DataRange As Excel.Range, tableNamesStr As String, _
primKeysStr As String, primKeyColumnsStr As String, startDataColumn As Integer, _
connID As String, ParamArray optionalArray() As Variant) As Boolean
Dumps table given in DataRange to database table(s) in tableNamesStr in a database specified by connID.
Parameters:
The function returns True if successful, false in case of errors.

Example:
Sub testdumpRangeToDB()
  Set mapper = CreateObject("DBAddin.Mapper")
  mapper.saveRangeToDB Range("employee"), "employee", "emp_id", 1, 2, "MSSQLPUBSTest", True
End Sub
Here the data in range employee are stored to the table employee using primary key emp_id, which is located in column 1. Data is starting in column 2, the connection information is stored in connection id "MSSQLPUBSTest" and any missing employees are inserted with a new emp_id key if it is missing.

Another Example using exceptions (fictional, there is no employeeCar table in the pubs database):
Sub testdumpRangeToDB()
  Set mapper = CreateObject("DBAddin.Mapper")
  mapper.saveRangeToDB Range("employee"), "employee,employeeCar", "emp_id,empPet_id", "1,2", 3, "MSSQLPUBSTest", True, "4,3,12", _
"employee", "job_id", 0, Array("employee", "job_lvl", "VAL", 0), Array("employeeCar", "IssuanceDate", "COL", 6), _
Array("employeeCar", "type_id", "VAL", 0)
End Sub
Here the data in range employee are stored to the tables employee and employeeCar using primary keys emp_id and empCar_id which are located in column 1 and 2. Data is starting in column 3, the connection information is stored in connection id "MSSQLPUBSTest" and any missing employees and employees Pets are inserted with a new emp_id key (resp. empPet_id) if it is missing. Data in columns 4, 3 and 12 are excluded from storage (supplementary calculations...)

A special exception has been implemented for the employees having errors or 0 in their job_id column, here the job_lvl is also set to 0 and their Car's data are set specially: Column 6, which contains the HireDate is used for filling the IssuanceDate of the Car, the car's type_id is also set to 0 (might be a "new employee" scenario).

An Example for the Mapper object/saveRangeToDB usage can also be found in the "DBFuncsTest.xls" Workbook, Sheet "MapperExamples".

History

From now on, changes are tracked in: ChangeLog.txt