class: center, middle # DB Addin --- # What is it all about DBAddin is a ExcelDNA based Add-in for Database interoperability. DBaddin provides 1. DB Functions, which are an alternative to the Excel built-in MSQuery and PowerQuery (integrated statically into worksheets having severe limitations in terms of querying and constructing parameterized queries). 2. Methods for working with database data (DB Modifications: DBMapper, DBAction and DBSequences). 3. DBMappers also allow for a row oriented way of data entry/modification. This is called DBSheets. ??? Notes .. --- # DB functions There are four ways to query data with DBAddin: 1. A list-oriented way using `DBListFetch`: Here the results are entered into a QueryTable starting from the TargetRange cell. 2. A record-oriented way using `DBRowFetch`: The results of the query are entered into several ranges given in the Parameter list `TargetArray` in order of the ranges' appearance. 3. Setting the Query of a ListObject (new since Excel 2007) or a PivotTable to a defined query using `DBSetQuery`: This requires an existing object (e.g. a ListObject created from a DB query/connection or a pivot table) and sets the target's query object to the desired one. 4. Setting the Query of a Powerquery Object to a defined query using `DBSetPowerQuery`: This requires an existing Powerquery name and sets its containing query as above. ??? Notes .. --- All DB functions 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`/`concatCellsText` simply concatenating cells (making the "&" operator obsolete) with Value property / Text property of the cells * `concatCellsSep`/`concatCellsSepText` concatenating cells with given separator with Value property / Text property of the cells * `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 (standard format YYYYMMDD, but other formats can be chosen) from the date value given in the argument. ??? Notes .. --- # DB Modifications DBModifications can be used to * save Excel Range data to database table(s): DBMapper * modify DB Data using Data Manipulation SQL (update, delete, etc.): DBAction * and creating sequences of these activities: DBSequence. The target data referred to by DBMapper and DBAction (data is the DML SQL statement(s)) is specified by special Range names, any other definitions (environment, target database, etc.) is stored in a custom property of the workbook having the same name as the target range. ??? Notes .. --- # DBSheets DBSheets are an extension to DBMappers with CUD (Create/Update/Delete) flags to modify Database data directly in an Excel sheet, i.e. to insert, update and delete rows for a defined set of fields of a given table. The modifications are done in DBMappers, which are filled by using a specified query. These DBMappers are also accompanied by indirect lookup values for updating foreign key columns together with resolution formulas to retrieve the direct Table data values (IDs). These allowed IDs and the visible lookup values for those foreign key columns are stored in a hidden lookup sheet. ??? Notes .. --- # DBSheet in Action  --- # Ribbon Menu The DB-Addin settings are stored on a distributed basis, being central and user specific and can be defined within the Ribbon Menu. The various database relevant settings for DB Functions and DB Modifiers are environment dependent and can be chosen with an environment selector in DB-Addin's ribbon menu. There are several tools, like adding query configurations with a configurable tree menu, an ad-hoc SQL-Query window and a error purging/problem detection button. Also a logging window and a conversion tool for functions of the legacy VB6 predecessor of DB-Addin is there. ??? Notes ..