Introduction

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

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:

• "chainCells", which concatenates the values in the given range together by using "," as separator, thus making the creation of the select field clause easier.
• "concatCells" simply concatenating cells (making the "&" operator obsolete)
• "DBString", building a quoted string from an open ended parameter list given in the argument. This can also be used to easily build wildcards into the String.
• "DBinClause", building an SQL in clause from an open ended parameter list given in the argument.
• "DBDate", building a quoted Date string (format YYYYMMDD) from the date value given in the argument.
• "Plookup", doing a pattern weighted lookup of values to retrieve the "best matching" lookup value. This is needed when working with incomplete input data.

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.  

• just any range, resulting data being copied beginning with the left-uppermost cell
• a self-defined named range (of any size) as TargetRange, which resizes the named range to the output size. This named range can be defined (and set as function parameter) either before or after results have been queried.

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

• either a one dimensional row-like range or a
• a self-defined named range (of any size extent, columns have to include all calculated/filled down cells) , which resizes the named range to the output size. This named range can be defined (and set as function parameter) either before or after results have been queried. Watch out when giving just one cell as the named range, this won't work as it's not possible in VBA to retrieve another assigned name of a cell and a hidden name us used to store the last extent of the formula range. The workaround is to assign at least two cells (columns or rows) to that name.

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:

• 0: DBListFetch just overwrites any existing data below the current TargetRange.
• 1: inserts cells of just the width of the TargetRange below the current TargetRange, thus preserving any existing data. However any data right to the target range is not shifted down along the inserted data. Beware in combination with a FormulaRange that the cells below the FormulaRange are not shifted along in the current version !!
• 2: inserts whole rows below the current TargetRange, thus preserving any existing data. Data right to the target range is now shifted down along the inserted data. This option is working safely for cells below the FormulaRange.

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:

<Standard Connection String>;<special ODBC connection string>

provider=SQLOLEDB;Server=MULTIMEDIAPC;Trusted_Connection=Yes;Database=pubs;ODBC;DRIVER=SQL Server;SERVER=MULTIMEDIAPC;DATABASE=OEBFA;Trusted_Connection=Yes

DBRowFetch

DBRowFetch (Query, ConnectionString(optional), 
 headerInfo(optional/ contained in paramArray), TargetRange(paramArray))

DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs " &_
 "where job_id = 1",,A1,A8:A9,C8:D8)

DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs",_
 ,TRUE,B8:E8, B9:E20)

DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs",_
 ,TRUE,A5:A8, B5:I8)

DBCellFetch

DBCellFetch (Query, ConnectionString (optional), 
 headerInfo(optional), colSep(optional), rowSep(optional), lastColSep(optional), lastRowSep(optional))

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

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)

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)

DBRowFetch(concatCells(E1:E4),,A1,A8:A9,C8:D8)

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

concatCellsSep

concatCellsSep(separator, ParameterList)

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)

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)

• When E1 contains the excel native date 18/04/2005, this results in : '20050418' (ANSI: date '2005-04-18', ODBC: {d '2005-04-18'}).
• When E1 contains the excel native date/time value 10/01/2004 08:05, this results in: '20040110 08:05:00' (ANSI: timestamp '2004-01-10 08:05:00', ODBC: {ts '2004-01-10 08:05:00'})
• When E1 contains the excel native time value 08:05:05, this results in '08:05:05' (ANSI: time '08:05:05', ODBC: {t '08:05:05'})

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)

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

• the refreshing and
• the storing

Skipping Data Refresh when opening Workbook

Prevent Storing of retrieved Data in the Workbook

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:

• MSQuery
• or - in case it is installed on your machine - SQL-Excel, a cool visual query builder for Excel (and maybe in the future other Office applications). For details on SQL-Excel see www.sqlexcel.net 

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

• starts MSQuery (see associated documentation on using MSQuery to define queries), building the query either in the Query-Assistant or in MSQuery itself, the option "Back to Office" lets you insert the query into the active worksheet.
• or, in case it has been installed, starts SQL-Excel. Build your query in the visual query builder of SQL-Excel and then press "Get SQL" to return to Excel.

• a DBListFetch function
• a DBRowFetch function
• a DBCellFetch function
• a DBMakeControl function
• in case DBAddin is used within Word: as a DATABASE field function

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

• Function Target: The cell where the DB function (either DBRowFetch or DBListFetch) is being placed. Available only for DBRowFetch and DBListFetch.

• Data Target: For DB functions, the cell(s) where retrieved database data is going to be placed. For DB bound controls this corresponds to the "LinkedCell" property, which is the target cell where the chosen control value is put. Available for all DB items except DBCellFetch (here the data is passed as the function's value).

• Query Target: The cell or range where the query is placed in case it is bigger than 255 characters. Available for all DB functions. If this is explicitly set then the query is always placed there, regardless of it's size! Also the query is placed there if it contains quotation marks ("). In case the Query Target is a multi cell range all query text in those cells is concatenated together. If the visual query construction tool returns multi-line queries (as SQL-Excel does) then the multiple lines are spread over the available cells of that range (a warning message is given when insufficient cells are given, the query is placed then in the top cell).

• ConnDef Target: The cell where the connection definition is placed in case "use custom database setting" is chosen (used to override the standard connection definition in the registry key [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\DBAddin\Settings\ConstConnString]) and the connection definition string is bigger than 255 characters. Available only for DBRowFetch and DBListFetch.

• Range Calc: The range where formulas that should be filled in along with data are going to be placed, Available only for DBListFetch.

Other possible choices are:

• additional Data choice: The way additional Data should be treated in DBListFetch (see explanation there). Available only for DBListFetch.
• "include Header Info?": should Field Headers be included in DBListFetch (see explanation there). Available for DBListFetch DBDropDown and DBListbox .
• "automatic Column Fit?": should columns be autofitted for DBListFetch (see explanation there). Available only for DBListFetch.

• "automatic Format fill?": should 1st row formats be autofilled down for DBListFetch (see explanation there). Available only for DBListFetch..
• "show row numbers?": should row numbers be displayed in 1st column for DBListFetch? (see explanation there). Available only for DBListFetch..
• custom database setting: if a different database from the global standard connection definition (see DBListFetch function) is used, then this is activated. Available for all DB items. Also the ODBC provider "MSDASQL.1" is inserted automatically in front of the rest of the connection string.

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

Creating configs

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"

"sortConfigStoreFolders"="False"

Inserting configs

Refreshing the config tree

Installation

Dependencies

• TABCTL32.OCX (distributed and installed with DBAddin)
• MSCOMCTL.OCX (should be available on all Windows XP/Vista installations)
• Office, Word and Excel Object Libraries (well, as it is an Office Addin, this should be expected on the target system)
• ADO 2.5 or higher (distributed in Windows XP/Vista)

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:

• DBidentifierCCS: used to identify the database within the standard default connection string
• DBidentifierODBC: used to identify the database within the connection definition returned by MS-Query
• CnnTimeout: the default timeout for connecting
• DefaultDBDateFormatting: default formatting choice for DBDate
• ConfigStoreFolder: all config files (xcl/wrd) under this folder are shown in a hierarchical manner in "load config"
• LocalHelp: the path to the local help files downloadable here. To include it into the standard installation, put the contained documentation folder into the DBAddin Application folder (e.g. C:\Program Files\RK\DBAddin)

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

• DBMakeControl
• Listbox control selection shifts uncontrolled upwards when using mouse. This only occurs when the DBMakeControl is either dependent on calculation results that are themselves dependent on the control's target cell (kind of "circular dependency") or if there is a dependent DBListfetch in the same sheet with extendDataArea <> 0. In this case Excel somehow always sets other cells to "dirty" status, thus enforcing a recalculation of the DBMakeControl. The workaround is to either use up/down keys after the initial click for precise selecting or place the DBMakeControl function in a different sheet than the DBListFetch's target area.
  
• Listbox control selection is not visibly retained during refresh (context menu resfresh data or fullcalc (Ctrl-Alt-F9)), i.e. the data target is kept, but the entry's highlighting is gone. This is the case when the control is placed on the same sheet as the DBMakeControl function. Workaround: put DBMakeControl in a different sheet than the control itself.
  
• All DB getting functions (DBListfetch, DBRowFetch, etc....)
• A fundamental restriction for these function is that they should only exist alone in a cell with no other DB getters. This is needed because linking the functions with their cell targets is done via a hidden name in the function cell (created on first invocation)
  
• DBListFetch:
• formulaRange and extendArea = 1 or 2: Don't place content in cells directly below the formula Range as this will be deleted when doing recalculations. One cell below is OK.
• GUID Columns are not displayed when working with the standard data fetching method used by DBListFetch (using an opened recordset for adding a - temporary - querytable). A workaround has been built that circumvents this problem by adding the querytable the way that excel does (using the connection string and query directly when adding the querytable). This however implicitly opens another connection, so is more resource intensive. For details see Connection String Special Settings
• Query composition: Composing Queries (as these sometimes tends to be quite long) can become challenging, especially when handling parameters coming fromcells. There is a simple way to avoid lots of trouble by placing 
• When invoking an Excel Workbook from the commandline (from a cmd script or the task scheduler) Excel may register (call the connect method of the Add-in) the Add-in later than invoking the calculation which leads to an uninitialized host application object and therefore a non-functional dbfunctions (they all rely on the caller object of the Excel application to retrieve their calling cell's adress). I'm still investigating into this.
• The Workbook containing the DB functions may not have any VBA Code with Compile Errors, or it will return an "Application defined Error". This relates to Excel not passing the Application.Caller object correctly to UDFs when having compile errors in VBA-Code.

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:

• New records are added by either adding data in an empty row below the displayed data or by selecting "insert Row (Ctrl-i)" in the cell context menu and adding the data into the inserted empty line.
• Existing records are updated by simply changing cells.
• Existing records are deleted by selecting "delete Row (Ctrl-d)" in the cell context menu.
• When the Excel-Workbook is saved (Ctrl-s) then the current DBSheet is stored as well.
• Selecting "refresh Data (Ctrl-e)" in the cell context menu will refresh data (undoing any changes made so far)

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

• http://www.sql-und-xml.de/sql-tutorial/ (german)
• http://www.w3schools.com/sql/default.asp (english)
• http://www.sql-tutorial.net/ (english)
• http://sqlcourse.com (english)
• http://sqlcourse2.com (english)

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

• refresh DBSheet (shortcut Ctrl-e) for reading in the current DBSheet from the database thus discarding any changes
• delete row (shortcut Ctrl-d) for deleting the row(s) of the currently selected cell(s)
• insert row (shortcut Ctrl-i) for inserting rows above the currently selected cell(s). As many rows are inserted as the selection has.
• show/hide foreign Record (Ctrl-b) for displaying the associated foreign table in a second window below with the corresponding record already selected.

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.

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:

• TrueFalseSelection: This is a language dependent setting for the selection of Boolean (bit) values in Excel. E.g. for English this would be "TRUE,FALSE"
• enforceRefresh: enforces refreshing DBSheets when activating them, so the user cannot cancel the refresh OK question. This cannot be set outside Excel/DBAddin, as it is always reset to True on startup
• specialNonNullableChar: this is prepended before columns that may not be null
• tblPlaceHolder: special placeholder for being replaced in lookups by the foreign table of that row (T2, T3...)
• maxRowsToFetch: when testing queries, don't fetch more than this at once (asks for more..)
• testHeaderColor: (orange) header color for test environment
• prodHeaderColor: (black) header color for prod environment
• noErrColor: (automatic) tab color if no errs happened
• internalErrColor: (orange) tab color if internal Errs
• dataErrColor: (red) tab color if data Errors
• reqFieldsColor: color for required fields in header row
• primColFieldsColor: color for primary columns
• calcColFieldsColor: color for calc columns
• conflictColor: color for displaying conflicting fields
• closeMsg1: first part of closing message for readonly DBSheets, after display of workbook name
• closeMsg2: second part of closing message for readonly DBSheets, after display of workbook name)
• DBConnFileName: the file name of global connection definition file
• DBSheetDefinitions: the root folder of all DBSheet definitions. All definitions below this folder only need a relative path specification in cell "A1".

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

• The DBSheets are locked to prevent accidental modification to the header row and the primary column(s). Sometimes it is necessary to do edits to the primary columns (see below for the foreign/primary key issue) or do other edits to the dbsheet's worksheet, which are possible only for unlocked sheets. In that case you can unlock the dbsheet by using the "unlockDbsheet" menu, which temporarily unlocks the DBSheet. It's not possible to unlock a Dbsheet permanently (after closing/reopening it).
• The list of lookup values for foreign key values is limited to 32767 entries due to excel limiting the data validation to 32K. This however doesn't mean you can't enter a value there, it is just not shown in the dropdown list so you're left on your own guessing the correct lookup for the foreign key value!
• There should only be unambiguous lookup field names (fields containing foreign key values to other tables) in a DBsheet workbook. The reason is that the field name is used for the name of the validation list of the constrained foreign key column. In cases where there is no unambiguity, make sure that the name refers to the same lookup values or you will get strange behaviour.
• Due to ansi-padding resulting in padded fields for fixed length character fields (for Microsoft SQL server), you are sometimes asked incorrectly that another user has changed the field when directly updating the fix-length character field. This is due to Microsoft SQL returning space padded fields when querying via a select cmd in ADO and thus being compared to an unpadded value in the last refresh data sheet (funny, if you're querying with table as cmd, the fields are returned unpadded).
• The hide/show foreign lookup works correctly only for single primary column foreign tables. Any Table with more than one primary column yields a wrong foreign record selection.
• There is a very stupid issue inside the OLEDB driver for MS SQL Server when creating DBSheets: When trying to retrieve the schema information (which uses sp_helpdb), the OLEDB driver somehow needs (public role) access to almost all user databases (It doesn't need it for the demo Northwind database for example) plus the "model" database. The ODBC driver doesn't require that and I actually don't see any reason for it (sp_helpdb in isql/osql/Query Analyzer works perfectly without the explicit access). So if you really want to use the OLEDB MSSQLServer driver, give all users that need to create/edit DBSheets public access to all user databases and the model database. I've written a script to accomplish this for any given SQL Server Instance:
  

  DECLARE @logname varchar(255),
   @dbname varchar(255),
   @exec varchar(255),
   @DBCursor CURSOR,
   @MyCursor CURSOR,
   @sql_db varchar(255),
   @sql1 varchar(255),
   @sql2 varchar(255)
  
   
   SET @MyCursor = CURSOR LOCAL SCROLL FOR
   SELECT name FROM master.dbo.syslogins WHERE name LIKE 'OEBFACOAT\%'
   OPEN @MyCursor
   FETCH NEXT FROM @MyCursor INTO @logname
   WHILE @@FETCH_STATUS = 0
   BEGIN
   SET @DBCursor = CURSOR LOCAL SCROLL FOR
   SELECT name FROM master.dbo.sysdatabases WHERE name not in('master','NorthWind','msdb','tempdb')
   OPEN @DBCursor
   FETCH NEXT FROM @DBCursor INTO @dbname
   WHILE @@FETCH_STATUS = 0
   BEGIN
  	-- need dynamic sql to switch database context here...
   SET @sql_db='USE '+@dbname
   SET @sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name='''+@logname+''')')
   SET @sql2=(' BEGIN EXEC sp_grantdbaccess '''+@logname+''' END')
   EXEC (@sql_db + @sql1 + @sql2)
   FETCH NEXT FROM @DBCursor INTO @dbname 
   END
   FETCH NEXT FROM @MyCursor INTO @logname
   END

• Sometimes a Worksheet loses it's ability to insert validation lists in various columns, currently the only fix is to reinsert the DBsheet configuration definition (Menu "Create DBSheet") into a new worksheet and delete the corrupt worksheet.
• For read-only workbooks the saving event hook for dbsheet storing prevents the posibility to save the Workbook under a different name (as this is usually annoying and unnecessary). If you really want to save a read-only workbook under a new name, add a non dbsheet worksheet to the workbook and save the workbook while that sheet is active. Sorry, but that's the trade-off.
• In case a primary column field is also a foreign lookup, a situation can arise where such a field is empty because the foreign lookup doesn't exist anymore. This can occur if referential integrity is not enforced on the foreign key. In those situations it's impossible with standard methods to update or even delete the record in question, because the dbsheet relies on the values provided in the primary column for finding the record for update/delete.
  You can overcome the problem by putting into the dbsheet query's foreign key column a construct that replaces the null values (that were not found) by the "real" underlying id.
  In MSSQL Server this would be ISNULL(<foreign_lookup_for_key_column>,<real_id_column>), in Oracle NVL(<foreign_lookup_for_key_column>,<real_id_column>).
  You also have to "unlock" the primary columns by unlocking the worksheet. 
  Then, a Delete or an Update of the record is possible.  If you do an update then you have to ignore the foreign lookup errors that arise for the non-existent foreign key column, the record will be updated correctly.
• Sometimes, when dealing with floating point values, Excel produces "rounding" errors when inserting these values via the QueryTables Object. This leads to unnecessary/false conflict resolution messages, because the data stored in the "original data backup sheet" is different (rounding errors) from the one currently in the database, which is under normal circumstances a hint for data that has changed during the editing of the user. To work around this annoying behaviour, you have to change the DBSheet Query to convert the columns in question to a fixed point type, e.g. decimal (in MSSQL Server):The part of the select clause would then look like ...., cast(T1.Rate as decimal(10,6)) Rate, ...
• When setting the "autoformat cells" option in the DBSheet parameters dialog, any drawing objects (textfields, shapes, etc.) are copied down together with the formats unless you set the object positioning to "independent of cell position and -size" (sorry for the german version of Excel, but you should get the point):
   
• When defining DBSheets, any change to database table names (typically done when developing databases) invalidate any previously defined DBSheets. This applies also to changes in case, e.g. changing lower to upper case and vice versa.
• Due to the fact that Excel loses clipboard information when performing data changes in a worksheet (which is done when inserting the database information), the clipboard get's cleared when changing to a DBSHeet on refresh. I've implemented two work-arounds to that problem: The first involves - temporarily - unlocking the dbsheet by using the "unlockDbsheet" menu (as needed above for editing primary columns). This makes copy/paste fully functional, however at the risk of working with stale DB-data. The second is a clipboard cache I've implemented in the refreshDBSheet method of the DBSheetHandler. The major drawback of this workaround is that it works cleanly only for contiguous ranges (single array selection) and not for fragmented selections. This behaviour is similar when copying fragmented selections to programs external to excel, here the left-out rows/columns are pasted as well.

Mapper

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

• DataRange As Excel.Range = The Excel Range to be saved to the database
• tableNamesStr As String = the table(s) where the range is to be saved to. If more than one table is given then use comma as a separator.
• primKeysStr As String = The primary key(s) used for identifying the rows in above table(s). If more than one primary key is given then use comma as a separator. The count of primary keys has to be the same as the tables.
• primKeyColumnsStr As String = The columns where the primary key value(s) are found in the Excel Range. If more than one primary key is given then use comma as a separator. The count of primary key columns has to be the same as the tables/primary keys.
• startDataColumn As Integer = The starting column for the actual data (excluding the primary column(s)).
• connID As String = The connection ID specified in DBConns.xml (see DBConnFileName in Installation)
• if given in the optionalArray:
• [insertIfMissing] As Boolean = if True then insert row into table if primary key is missing there. Default = False (only update) 
• [excludeColumns] As String = The column numbers of those columns that should be excluded from the storing (helper columns,etc.). If more than one column is to be excluded then use comma as a separator.
• [specialExceptionTable] As String = The table in whose specialExceptionField the update values should be checked whether they are of specialExceptionValue or result in an error. If that is the case then the rest of the row is not being stored, and the values given in specialExceptionDefaults are stored into the fields and tables given in specialExceptionDefaults.
• [specialExceptionField] As String = The field that is checked whether it's value is a specialExceptionValue (typically an undesired value, like empty or zero) or whether it is an error.
• [specialExceptionValue] As Variant = The special value that is checked (typically an undesired value, like empty or zero)
• [specialExceptionDefaults] As Variant() = Array of Array(""Tablename"",""Fieldname"",""COL/VAL"",value): The table/field/Column/Value combinations that are used for determining the special default values to be stored in the special fields of special tables in case of an above found exception. In case the third part of a specialExceptionDefaultArray is "COL" then the default values are taken from the column given in the fourth part (value). In case the third part of a specialExceptionDefaultArray is "VAL" then the default values are taken directly from the fourth part.

Sub testdumpRangeToDB()
  Set mapper = CreateObject("DBAddin.Mapper")
  mapper.saveRangeToDB Range("employee"), "employee", "emp_id", 1, 2, "MSSQLPUBSTest", True
End Sub

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

History

• 2006: DBFuncs First versions using CopyFromRecordSet, Version 1.5 is the last one utilizing Range.CopyFromRecordSet for DBListFetch, as I found this function to be highly unreliable. From 2.0 on, I use QueryTables instead, which copies (at least until now) all data correctly into the sheet.
• 31/01/2007: DBFuncs Version 2.0: Codeproject Article posted
• 03/02/2007: DBFuncs Version 2.1: Separate autosized naming of FormulaRange is now possible
• 27/02/2007: DBFuncs Version 2.2: Bugfix: Check for non-range parameters in DBRowFetch's parameter list, Bugfix: chainCells & concatCells now working with general parameters, Bugfix: remaining names from Querytables are deleted now, Bugfix: Autosizing names works now with single cells, too.
• 28/02/2007: Initial post of DBSheets.xla to Codeproject.
• 12/03/2007: DBFuncs Version 2.3: 
  • Bugfixes: 
    • Single cell naming the TargetRange now correctly resizes to full extent.
    • Autoformatting down works now also for formula cells
    • worked around the validation list bug (calculation couldn't be set to manual in the event procedure).
    • Format filling now works correctly also with headers. 
    • removed a very subtile error with multiple resizing TargetRanges beneath one another lead to DBlistFetch losing track of the upper TargetRange. 
    • Formatting didn't work correctly down to end of data areas.
  • Enhancements: 
    • a named TargetRange extends to the FormulaRange if the FormulaRange is adjacent to the TargetRange. 
    • Added DBinClause Function for creating bracketed in clauses from parameter arrays (ranges, etc.).
• 15/03/2007 DBSheets Version 1.1: Bugfix: clearer error messages from Database when saving. Bugfix: loading Datasheet definitions now uses the correct connection, Enhancement: Added parameterized queries (having ? in the where clause), added "edit DBSheet parameters" context menu to top/leftmost cell menus, which allows creating range names whose values are used to fill the parameterized queries. Also allows the end-user to edit his presets for "don't show" and other useful settings (autoformat, freeze headers/primary columns,...)
• 25/03/2007: DBFuncs Version 2.4:
  • Bugfixes:
    • Filtering now works correctly again. 
    • verketteZellen didn't work with non-ranges.
  • Enhancements:
    • Added function help for all public functions.
• 09/04/2007: DBFuncs Version 2.5:
  • Bugfixes:
    • Auto-Formatting works now for general formats (not only numerical)
    • error values (#NV!...) possible in formula range of DBListFetch.
      
  • Enhancements:
  •  Added Plookup function for weighted pattern lookup
• 02/11/2007: DBAddin Version 1.0: Major redesign as COM/Automation Addin
• Enhancements:
• Complete Refactoring of DBSheet Codebase
• Merged DBFuncs with DBSheets
• MS Word Support (database table fields)
• DBCellFetch function for returning queries as single value results
• DB Control now realized as Functions as well (no need for additional VBA startup coding)
• FormulaRange can be separately named
• Easy function deployment with Save/Load Config feature (can be generally used for formulas/cell contents)
• Better Help (similar to built-in functions now)
• Event Log used to log errors.
• Progress feedback via Statusbar
• "Jump" feature for all DB functions except DBCellFetch to quickly select target data area from function cell and select function cell from target data are
• MSI-Installer based installation