Introduction
IMPORTANT: DBSheets has been merged with DBFuncs and has moved here!
DBSheet is an Excel based solution to modify database data, i.e. inserting, updating and deleting rows in a defined (sub)set of fields of a given table.
The modifications are done in so-called Database-Sheets (DB-Sheets), which are filled by using a specified query. The DB-Sheet 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 DB-Sheet 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 DB-Sheet is stored as well
- Selecting
refresh Data (Ctrl-R) 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 the 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 are:
- 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:
- As administrator of the Addin, basic proficiency with Excel-VBA.
Working with DBSheet
I use the enclosed test workbook called Pubs.xls as an example to guide through the possibilities of DBSheet.
This workbook uses the pubs database for Microsoft SQL, (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 it here.
Features of DBSheets
DBSheets consist of one sheet containing the table data, the header and the DBSheet definition (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 the last refresh for identifying changed data.
The header row and the primary key column(s), which are located leftmost and shown in grey, may not be modified, except for new rows where there is no primary key yet.
Mandatory columns (not allowing empty (null) values) are shown with a grey 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 (a "c" is put at the end of the sheet (column 256 for Excel XP) of the modified row(s)).
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 (an "i" is put at the end of the sheet of the inserted row(s)).
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 number of empty rows are 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 (a "d" is put at the end of the sheet of the deleted row(s)).
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+R) 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 there are in the selection.
- Toggle Detail View (shortcut Ctrl+T) for toggling between the list and the detail view (multiple windows with each displaying a separate part of the columns, see below)
- 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 not to have any warnings about refreshing, deleting, inserting and storing data by checking "always OK & don't display for this DB sheet !" in the confirmation message box that appears:
The refreshing of data when switching between DBsheets (entering/activating a DBsheet) can be enforced by setting Public Const enforceRefresh = True, thus displaying the warning message when pressing Cancel:
When selecting "toggle Detail View" (shortcut Ctrl+T), the list view switches to a "detail view", where all data of one row tries to be displayed in one window:
This is achieved by opening multiple windows (views) on the same worksheet, with each view being one page offset to the right. You can switch between these windows with Ctrl+Tab (forth) and Ctrl+Shift+Tab (back). If the DBSheet is the only (visible) workbook, then this will truly cycle through all the detail windows, otherwise the other workbooks will pop up from behind. The last selected row is displayed in light green, which eases navigation in detail windows (when having > 100 columns or very wide columns the single detail windows are rather narrow).
You can switch back to the list view again by selecting "toggle Detail View" again.
If calculated columns were defined, the topmost formula in that column is always preserved when refreshing the DBSheet, being autofilled 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 behaviour parameter enforceSaveAllWhenCalcColumns, which saves all rows as edited, regardless of whether they have been really edited or not. Beware that this behaviour makes saving very slow for large tables…
The header row contains all the definitions in comments, where the comment in cell A1 contains the following information in XML format:
- The query for fetching the main table data to be edited
- The connection ID referring to the central definitions for all corresponding
DBSheets, mainly being the database and the server where data should be fetched from - 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 - The connection timeout, the primary column count and the start of the calculated columns (0 specifying no calculated columns)
- Several other information items used for specifying special
DBSheet behaviour:
- Freezing header rows:
<freezeHeader> - Freezing primary columns:
<freezePrimCols> - Enforcing storing of all rows when calculated columns are to be saved:
<enforceSaveAllWhenCalcColumns> - Suppressing the confirmation message boxes:
<dontShowsave>, <dontShowinsert> <dontShowrefresh>, etc. - Range Names for used for parameterized
DBSheets
- There is one information stored for the whole workbook in a workbook property (can be customized in
Const globalPropertyStore = "Hyperlink base") for suppressing the saving of the underlying workbook when storing data: <dontSaveWB>
The end-user definable parts of this information (not the DBSheet definition itself) can be edited in the DBSheet parameters dialog, invoked by right click the top/leftmost cell and selecting edit DBSheet parameters:
This dialog box allows you to edit the window pane freezing presets, enforcing saving all rows (for calculated columns). Also, all the "don't show" settings done during editing/refreshing/saving of DBsheets can be reset again. Autoformatting cells down can be enabled to copy down the formats set in the first row of the DBsheet.
In the lefthand 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.
There is one information stored for the whole workbook in a workbook property (can be customized in Const globalPropertyStore = "Hyperlink base") for suppressing the saving of the underlying workbook when storing data: <dontSaveWB>
Conflict Resolution When Multiple Users Edit the Same Row
Two or more people can edit a Table simultaneously, the last one 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 to 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, if the context menu is activated in the primary table pane, the other - associated – foreign record is highlighted in the foreign table pane. Data can be edited in both panes, however refreshing takes place when switching between panes !
Supporting Tool "DBSheet Creation"
Following is a the first tab of the Supporting tool "DBsheet Creation". Usually you start with defining or selecting the connection for your DBSheet. For a new connection you enter the desired connection string, containing the database where the table to be edited is located. The connection timeout can be chosen as well.
What is important for running DBSheet in a test environment is the next two fields basically allowing to change the Database name or the servername by attaching "Test."
The next two entries are for Oracle databases where the tool 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!
So next, select the main table. A DBsheet should be created for in the dropdown "Table", which then 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 populating 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 restrictions). 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 Table" allows you to select the foreign table carrying the lookup information for the foreign column. 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 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 must always 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 leaving the selected line (deactivating the selection). When finished with editing, click on the button "abort column edit" to start adding again.
You can change the order of columns by clicking on the arrow-up/arrow-down buttons.
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 there.
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 right click selecting "edit DBSheet definition". This takes the currently stored values from the sheet and displays them again for editing.
When dealing with foreign column lookups or other restrictions, you can edit the definition of the lookup directly by editing the restriction field below the DBSheet Column list:
You can put whatever query into that, all it has to return are the lookup value first and then the ID to be looked up. 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 have to obey 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 as the foreign table key. If that is not the case, DBSheet wont be able to associate the foreign column in the main table with the lookup id, and thus displays the following error message:
The connections between the lookup query and the main query are generated as follows:
- The first column part of the lookup query
select statement is copied into the respective column in the main table (therefore the above restriction) - 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. 1000 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.
The following diagram should clarify the connections between the lookup query and the main query:
You can even have a lookup column without defining a foreign table relation at all. This is done by simply opening the restriction field using the "is foreign column?" 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, like in select lookupCol+additionalLookup, lookupID…).
Even a lookup column without a lookup query is possible by just listing the possible values after the values 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 three steps:
- First, all (or just single selected) restrictions should be (re)generated using the foreign table/lookup field information
- Then the main query for retrieving the fields to be edited has to be generated (and can be further customized, if needed)
You can test the query by clicking on "test" besides the query field. This puts the result of the query into a new temporary sheet (can be closed afterwards) - Finally, the
DBSheet can be created, transferring the information collected by DBSheet Creation to the currently opened Excel Worksheet (if a workbook/sheet is active, then the DBSheet definition is added to that worksheet, otherwise a new workbook/sheet is created)
Also you can initially set specific properties of the DBSheet (see also "Features of DBSheets"), if these are ticked:
- Freeze header?: sets
<freezeHeader> - Freeze primcols?: sets
<freezePrimCols> - Enforcing save of all rows when calc cols?: sets
<enforceSaveAllWhenCalcColumns>
The DBSheet definitions can be saved/restored using the load/save/save as.. buttons on the bottom left side. This stores the information currently contained in the DBSheet columns in a DBSheet definition file (extension: XML).
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 (max. 1000 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.
When a DBSheet is created, all the definitions (headers/lookups, query, etc.) and the initial values from the table are inserted.
Additionally, a special Workbook.open procedure used for initializing the created DBSheet's Workbook is put into the windows clipboard:
Private Sub Workbook_Open()
env = ""
If InStr(1, ThisWorkbook.Name, "Test") > 0 Or _
InStr(1, ThisWorkbook.Path, "Test") > 0 Then env = "Test"
On Error Resume Next
Application.Run "'Your\Path\To\DBSheet\" & env &_
"\DBSheet.xla'!initDBSheet", ThisWorkbook.Name, ThisWorkbook.Path
If Err <> 0 Then _
Application.Run "'DBSheet.xla'!initDBSheet", ThisWorkbook.Name, _
ThisWorkbook.Path
End Sub
This has to be pasted then into the workbook module of the respective DBSheet containing the workbook:
Installation
Installation is done by copying the Excel Addin DBSheet.xla into any folder of your choice.
Within that folder, the global connection definition file DBConns.xml is located. Use a text editor to edit your connection(s) (please take care not to introduce newlines within the leaf nodes, this disturbs reading into the DBSheet Creation tool):
<DBConnections>
<c>
<id>TESTDB</id>
<default/>
<connString>
provider=SQLOLEDB;server=MULTIMEDIAPC;Trusted_Connection=Yes;database=pubs
</connString>
<timeout>15</timeout>
<dbChange>database=</dbChange>
<srvChange>server=</srvChange>
<dbneedPwd></dbneedPwd>
<dbGetAll>sp_helpdb</dbGetAll>
<dbGetAllFieldName>name</dbGetAllFieldName>
<ownerQualifier>.dbo.</ownerQualifier>
<allowedUsersEdit>roli</allowedUsersEdit>
</c>
<c>
<id>ORAPUBS</id>
<connString>
Provider=OraOLEDB.Oracle;Data Source=XE;User ID=pubs;Password=pubs12
</connString>
<timeout>15</timeout>
<dbChange>User ID=</dbChange><DBisUserscheme/>
<dbneedPwd>Password=</dbneedPwd>
<dbGetAll>select username from sys.all_users</dbGetAll>
<dbGetAllFieldName></dbGetAllFieldName>
<ownerQualifier>.</ownerQualifier>
<allowedUsersEdit>roli</allowedUsersEdit>
</c>
</DBConnections>
You might also want to customize several of the following globals in Module DBSheetMain:
#Const DEBUGME = True : For Debug messages the compiler directive can be set to FALSE to ignore the LogDebug statements (thus speeding things up in production…) Const Loglevel = 3: The max log level being written to LogFilePath (0 = ERROR, 1 = WARN, 2 = INFO, 3 = DEBUG). The log file with the above information is written to Environ("USERPROFILE") & "\DBSheet.xla." & env & ".log" (where env is either "Test" or "" and Environ("USERPROFILE")denotes the users home profile folder) Const testHeaderColor = 45: (orange) header color for test environment Const prodHeaderColor = vbBlack: header color for prod environment noErrColor = -4142: tab color if no Errs happened internalErrColor = 45: tab color if internal Errs dataErrColor = 3: tab color if data Errors TrueFalseSelection = "WAHR,FALSCH": This is a language dependent setting for the selection of Boolean (bit) values in Excel. E.g. for English this would be
Const TrueFalseSelection = "TRUE,FALSE" enforceRefresh = True: enforces refreshing DBSheets when activating them, so the user cannot cancel the refresh OK question DBConnFileName = "DBConns.xml": the file name of global connection definition file specialNonNullableChar = "*": this is prepended before columns that may not be null globalPropertyStore = "Hyperlink base": workbook built in property where the workbook global user settings (currently only <saveWB>) are stored tblPlaceHolder = "!T!": special placeholder for being replaced in lookups by the foreign table of that row (T2, T3...) maxRowsToFetch = 1000: when testing queries, don't fetch more than this at once (asks for more..)
Then start DBSheet.xla again and create your first DBSheet!
In case you want to run the Pubs.xls workbook from a different location than DBSheet.xla's path, please change the (manually edited) start path "'" & ThisWorkbook.Path & ... to something that suits better.
Testing
DBSheets only has been tested on Excel XP (2002) and Microsoft SQL Server 2000 respectively as well as Oracle 10g.
There is a separate test environment facility, meaning that if there is "Test" somewhere in the path of the DBSheet Workbook, it will a) call the test addin located in the Test folder below the main folder, b) modify the connection string in a way defined in setEnvironmentConnstring in module utils, which currently only appends \Test to the database server instance denoted by the srvChange parameter in connections. This is of course highly dependent on the database engine you are using, so you'd always have to slightly change the setEnvironmentConnstring procedure.
Known Issues/Limitations
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 – exact – lookup for the foreign key value!
There should only be unambiguous lookup column names (fields containing foreign key values to other tables) in a DBsheet workbook. The reason is that the column name is used for the name of the range containing the valid list in the (hidden) lookup sheet.
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).
Currently 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.
I had an issue with named instances in Microsoft SQL Server and the Microsoft OLEDB driver, when trying to retrieve the schema information, the OLEDB driver closed the connection without obvious reason, thereby producing an error when trying to retrieve the possible databases for foreign tables.
Points of Interest
There is little special about DBSheets (apart from the idea), it's mostly just plain Excel VBA, with one exception: The toggling of detail view (in module DetailListView) uses a WinAPI call to SetWindowLong (Hwnd As Long, nIndex As Long, dwNewLong As Long), which hides/displays the window title bar.
This functionality is wrapped behind the function TitleBar(theWin As Window, bShow As Boolean), which hides/displays the title bar of theWin, depending on bShow.
History
- 28/02/2007 Initial post to Codeproject
- 15/03/2007
- Bugfixes:
- Clearer error messages from database when saving
- 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,...)
- 02/11/2007:
DBAddin Version 1.0 now available at sourceforge
