Beyond Excel: VBA and Database Manipulation

Apr 5 2010   11:24AM GMT

Additions to the Fields Table for Update Spreadsheets

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

 As promised in the last post, here are descriptions of each column in the fields table.

Fields Table

Fields Table (click to enlarge)

Heading Description
Table Database table/File name. Every entry in this table must have a table name or the word “*None”. “*None” indicates a literal or an Excel Function will placed in this column instead of a value from the database table.
Alias An abbreviation you want to use for the table/file name. This is highly recommended.
Examples of abbreviations might be “E” for an employee master, or “C” for customers. if “*None” is in the Table column, you must not enter an abbreviation.
Field Field/column name. If “*None” is in the table column this must be a literal value like “A”. If this column is to contain an Excel function, this column should contain “”.
Key ”K” designates the field/column is part of the table/file primary key. The Update and Delete routines need to know what the key values are in order to update your database properly.
WARNING! Failure to add all keys can result in: Delete operations removing more; or Update operations changing more records than intended. Do not allow changes to primary key values as this will cause Updates and Deletes to fail. Take care to designate all values that make this record unique.
Heading Spreadsheet column heading to display.
NOTE: These column headings MUST be compatible with your database’s conventions.
S.Ord A number to designate which fields/columns are sorted first, second, third, etc.
S.Seq ”A” designates ascending sort order, “D” designates descending
Freeze ”Y” designates all columns to the left of this column will always display no matter how far the user scrolls to the right.
NOTE: Only 1 field should have this value set.
SQL Func. An SQL function to apply to the field on reading the database
Hide ”H” hides the column entirely. “C” (conditional) grays out repeating values.
Width Enter a number to restrict the column’s width when displayed
Format Any Excel format string to apply to the results
XL Func. An Excel formula to use instead of a table field/column. Result columns can be designated in the Excel formula by enclosing either the “Field/Column” name or “Heading” in brackets {}. “Field/Column” names should be used when possible because “Headings” can change.
NOTE: Table must be set to “*None”; Alias must be empty; and Field must be double quotes
P Designates that only the program can change this column (the user is locked out).
C Designates the user can change this column for new and existing records. 
NOTE: No key fields should use this setting.
A Designates the user can change this column for new records only
Required ”Y” designates blank entries are not allowed
V.Type Validation Type

# Only allow numbers
$ Same as #
1-### Restricts text entries to this many characters
ACD Use for “ACD” columns only (restricts entries to “A”, “C”, “D”, and “X”).
DATE Restricts entries to valid dates
DB Requires entry to be a value in a database table.  Should the entry not be found, frmSelect can be used to help the user find the proper code.  If you use this, you must add programming code to routine “DB_Lookup” to validate the entry (or modify the template to suit). 
TIME Restricts entries to valid time values.
XLC Requires entry to be a value in an Excel “Code” table. “Code” tables consist ONLY of “Code” and “Description” columns.  Should the entry not be found, frmSelect will help the user find the proper code.
XLT Requires entry to be a value in an Excel “Type” table. “Type” tables consist ONLY of “Type”, “Code” and “Description” columns, where only codes of a certain type are valid.  The “type” must also be a column in the entry area.  Should the entry not be found, frmSelect will help the user find the proper code.
YORN Restricts entries to “Y” or “N”
V.Tbl Validation Table: When V.Type is:

DB It is recommended that you put the name of the database file/table containing values to validate against here – however – you can use any string that “DB_Lookup” can test against in a “Select Case” statement to direct execution to your code.
XLC Put the name of the Excel “Code” table range here.  Use XLC when the number of codes is relatively small (under 100?) and extremely static (doesn’t change).  An example might be a listing of status codes such as “A” for Active, “T” for Terminated, “S” for Suspended.  If the list of values is long OR dynamic (changes often), don’t use an Excel range to store values.  Use a database file/table instead along with DB to lookup values.  This will make maintenance of your spreadsheet much simpler.
XLT Put the name of the Excel “Type” table range here along with the column name of the “Type” enclosed in curly brackets.  The “Type” column should be to the left (entered prior) of the “Code” column. The same advice in XLC about the number of codes and dynamic nature of your data applies to this. 
EXAMPLE: Products {Category}.   In this example, only “Products” in the same “Category” as the current record are valid and can be selected.  The “Category” column must be entered and validated before the “Products” column.
Upd.Func. A programmed function to apply to the value before updating. Preprogrammed functions include:

DATE2JULIAN Converts normal dates to YYYYDDD (Julian) format
DATE Prepares the value for storage as a DATE data type in the database
HHMMSS Prepares the value for storage as a TIME data type in the database
HHMM Prepares the value for storage as a TIME data type without seconds
TRIM Prepares the value for storage as character data and removes blanks.
Always use this for character based fields so quotes surround the value in SQL statements
Format String Enter any valid Excel format string to convert numbers (such as social security numbers) to text with formatting (dashes) before storing as character data
Notes Any notes you may want to associate with the entry. The program does nothing with notes.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: