Press Alt-Control-D to invoke DbDialog, a convenient yet sophisticated dialog for managing a Microsoft Access database. The database file, DbDialog.mdb, is created in the Window-Eyes user profile folder. Also located there is a configuration file, DbDialog.cfg, which contains default settings for how fields of each table in the database are to be treated. The file DbDialog.ini is created at runtime, inheriting settings from DbDialog.cfg, as well as containing other script settings such as the hotkey to invoke the main dialog (configurable via Hotkey Manager). Since this file is in ini format, it can be manually edited with a text editor. Once a table is initially configured, a considerable amount of flexibility is available within the dialog for affecting the display, ordering, and filtering of records.
The main dialog contains a listbox, buttons, and status line. The listbox shows a selection of fields of each record. Below it is a row of buttons to perform various actions. Below that is a status line that shows meta data about the current record in the listbox -- typically the unique ID number of the record, the date it was added, and the date it was last modified.
The default table is for managing contact information. Also provided for illustration is a table about products and one about to do items. Documentation here sometimes explains features using these tables as concrete examples.
There are many action buttons, available either by tabbing or mnemonic, Alt-Letter hotkeys. They are summarized as follows:
Add = add a record
Browse = bring focus to the list of records and say the current item
Copy = add a record by starting with a copy of the current record
Export = transfer a copy of the current record to another table
Filter = set a filter to limit what records are displayed. You can optionally type a comparison operator before a value to be matched, e.g., the equals sign (=) for a complete match (ignoring case). If no comparison operator is specified and the data type of the field is Text, then a match will occur if the value is contained anywhere inside the field data.
Go To = go to another table in the database
Help = display this documentation
Index = pick fields that determine the sort order of records
Keywords = search for a record by a word or phrase it contains
List = save data in the listbox to a text file, which is then opened automatically in the Window-Eyes script editor (Notepad by default but configurable via Script Manager)
Modify = modify the current record
Next = search for the next match of keywords
Output = say non-blank field names and values of the current record, and copy that text to the clipboard
Prior = search for the prior match of keywords
Remove = remove the current record
Select = pick what fields to display in the listbox
Tag = tag the current record
Untag = untag the current record
View = view the current record
Extra Tools = present a set of additional buttons for less frequent or more advanced operations
Yield = say the number of records in the current filter and in the whole table
Zoom = go to another table with related records , e.g., Product records with the same Contact ID
Close = end the dialog (data is saved with each operation, not at the end)
Some commands invoke an input form for viewing or changing multiple fields of data. Each row of the form contains a field label to the left and a field value to the right. Tab moves from field to field. The Contact form has many fields, but each label has a unique Alt-Letter hotkey so you can jump directly to it. The last field, Extra Info (Alt-X), lets you enter multiple lines of miscellaneous information not captured by other fields.
A few buttons in the input form operate on the field with focus. In order not to conflict with Alt-Letter combinations for accessing fields directly, these buttons use Alt-Symbol hotkeys. The =Lookup button (Alt-=) is for looking up the ID of a record to relate to the current one, e.g., for filling in the Contact ID field in a Product record. A minimal version of the same DbDialog interface lets you navigate among Contact records and choose one. The Contact ID is then placed in the corresponding field in the Product form and focused is moved to the next field, Contact Look, which summarizes the Contact record in words (since the ID is just a number). The /Date button (Alt-/) enters the current date in the field with focus. The :Time button (Alt-Shift-;) enters the current time. If a longer form of the date or time is entered than you prefer, you can delete unnecessary parts (e.g., the day of the week or milliseconds).
The Extra Tools button in the main dialog presents another set of buttons with additional choices. Several of these use the same term as the main dialog but apply to a larger entity than the current record: Add, Copy, Export, Go To, Modify, Output, Remove, Tag, and Untag. They are explained as follows.
Add New Table. A wizard helps you define a new table. You are presented with a multiline edit box. Each line defines the name of a field and its data type (using terminology of Microsoft Access). The Text data type also specifies the maximum number of characters in parentheses. Other data types are YesNo for a true or false value, Counter for an auto-incrementing number, DateTime for a date or time value, Long for an integer value, Double for a floating point number, or Currency for a money value. Twenty Text fields are presented by default. Edit their names or types, or delete some entries, and then submit the form. The wizard automatically adds the following fields: Tagged Text(1), Added DateTime, Modified DateTime, ID Counter, and Extra_Info Memo. Once the table is created, you can open it with the Go To command and fine tune the fields used for display and ordering with the Select and Index commands.
Backup Database to Another Folder. You are prompted for a folder to which the files DbDialog.mdb and DbDialog.cfg are then copied.
Copy Structure to New Table. You are prompted for the name of a new table that will have the same structure as the current one.
Drop Table from Database. You pick a table to delete completely from the database.
Export All Records in Filter. You pick a table to which copies of all records in the current filter are copied.
Go to Different Database. You are prompted for another Microsoft Access .mdb file to open. It is assumed that a configuration file exists in the same folder, having the same name except a .cfg extension.
Help on Structured Query Language. A text file explaining SQL syntax for Microsoft Access is opened in the Window-Eyes script editor. You can use this syntax with the Query command, e.g., Alter Table for changing the size of a field.
Import All Records from Another Table. You pick a table from which copies of all records are transferred into the current one.
Load database in Microsoft Access. The current database is opened with the application associated with .mdb files, generally Microsoft Access if installed. This lets you perform restructuring, querying, or reporting options not available within the DbDialog program, itself.
Modify All Records in Filter. An input form lets you specify new field values that will be applied to all records in the current filter.
Output All Records to Text File. The non-blank field names and values of all records in the current filter are saved to a text file, which is then opened automatically in the Window-Eyes script editor.
Produce Custom Report. You pick a report defined for the current table. Two examples are provided for the Contact table: Address and PhoneEmail. Their definitions are sections of the configuration file DbDialog.cfg. A report definition contains code that is evaluated for each record. a Line of output is generated only if its code evaluates to a non-blank value (use the word Blank to intentionally leave a blank line). As the examples shows, multiple lines are possible for each record. Also, code may be designated for evaluation at the top of the report, after each record, and at the bottom. Use VBScript syntax, except that field names may be denoted by a $ prefix. Functions of Homer Shared Object may also be used such as Part, IIF, and IsBlank. Extra Homer constants may also be used such as xQuote, xApostrophe, and xMute.
Query with SQL Command. You are prompted for an SQL (Structured Query Language) command to submit to the current database. The result of the operation is shown in a message box.
Set Configuration Options in Text Editor. The configuration file for the current database, typically DbDialog.cfg, is opened in the Window-Eyes script editor. You can review or change settings that cannot be done through the user interface of DbDialog.
Remove All Records in Filter. All records in the current filter are deleted.
Tag All Records in Filter. The Tagged field of All records in the current filter is set to an asterisk character (
Untag All Records in Filter. The Tagged field of all records in the current filter is set to a space character.
When a new version of DbDialog is installed, it does not replace the database file DbDialog.mdb, so you will not lose data. You should periodically back up that file, however, as with any important document, e.g., via the Backup command on the Extra Tools menu. The configuration file DbDialog.cfg is replaced with a script update, but the user settings in DbDialog.ini are not. Whenever DbDialog is invoked, it merges settings in DbDialog.cfg into DbDialog.ini, but only those that do not already exist there.
Thanks go to Christo de Klerk for extensive testing and feedback on DbDialog. This global script requires GW Toolkit and Homer Shared Object.
Added check for Homer Shared Object 6.6, which fixes output in the DialogMemo function.