Beyond Excel: VBA and Database Manipulation


April 28, 2010  4:15 PM

Building a Library of Routines for Updating – #3

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
You are here (click to enlarge)

You are here (click to enlarge)

This post provides “insert” functionality which adds records to tables.  The basic syntax of an SQL Insert statement is: 

 

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
To build this statement, we have two routines.  One creates a string of column/field names and the other creates a string of values.  The column/field names come from the Fields Defnition table.  The values come from the end user’s entries in the “Data” range.
Function Build_SQL_Insert_Fields(sFields As String, sTable As String) As String
'   Description:Format field/column names for an SQL "Insert" statement
'               Use Build_SQL_Insert_Values to add associated values
'   Parameters: sFields:Range name containing field definitions
'                       Field:  Database Field/Column names
'                       Key:    Which fields are part of the unique key
'                       Table:  Database table/file name to update
'               sTable: Database table/file name to update
'   Example:    sSQL = _
'                   "Insert Into " & sTable & " (" & _
'                    Build_SQL_Insert_Fields("Fields", sTable) & ") " & _
'                   "Values (" & _
'                    Build_SQL_Insert_Values("Fields",sTable,"Data",1,True) & ") "
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_Insert_Fields = ""    'Assume Something went wrong
    Dim lRow As Long
    Dim sSQL As String
   
    If sFields <= "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
            'Skip fields that are part of Primary Key or not part of update table 
            If .Cells(lRow, FieldColumn("Table", sFields)) = _
                sTable And .Cells(lRow, FieldColumn("Key", sFields)) <> "A" Then _
                    sSQL = sSQL & IIf(sSQL > "", ", ", "") & _
                       .Cells(lRow, FieldColumn("Field", sFields))
        Next lRow
    End With
   
    Build_SQL_Insert_Fields = sSQL
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Insert_Fields - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

 

Function Build_SQL_Insert_Values(sFields As String, sTable As String, _
                                 sDataRange As String, lRecord As Long, _
                                 bRows As Boolean) As String
'   Description:Format field/column values for an SQL "Insert" statement
'               Use Build_SQL_Insert_Fields to add associated Field/Column names
'   Parameters: sFields:Range name containing field definitions
'                       Field:  Database Field/Column names
'                       Key:    Which fields are part of the unique key
'                       Table:  Database table/file name to update
'               sTable: Database table/file name to update
'               sDataRange: Range name that holds the values/data
'               lRecord:Record in sDataRange being updated (Row# if bRows=True)
'               bRows:  True means each record is in a row
'   Example:    sSQL = _
'                   "Insert Into " & sTable & " (" & _
'                    Build_SQL_Insert_Fields("Fields", sTable) & ") " & _
'                   "Values (" & _
'                    Build_SQL_Insert_Values("Fields",sTable,"Data",1,True) & ")"
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_Insert_Values = ""    'Assume Something went wrong
   
    Dim lRow As Long
    Dim sSQL As String
    Dim sCRTB As String
    Dim sValue As String    	'Current field Value
    Dim sScreenField As String  	'Field's Screen Name
    sCRTB = vbCr & vbTab & vbTab
   
    If sFields <= "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
            'Skip fields not part of the table or part of an Auto ID
            If .Cells(lRow, FieldColumn("Table", sFields)) = sTable And _
                .Cells(lRow, FieldColumn("Key", sFields)) <> "A" Then
                sScreenField = .Cells(lRow, FieldColumn("Heading", sFields))
                If bRows Then
                    sValue = Range(sDataRange).Cells(lRecord + 1, _
                             FieldColumn(sScreenField, sDataRange))
                Else
                    sValue = Range(sDataRange).Cells( _
                             FieldRow(sScreenField, sDataRange), lRecord + 1)
                End If
                sSQL = sSQL & IIf(sSQL > "", ", " & sCRTB, "") & _
                    SQL_Add_Update_Functions(sValue, lRow, sFields)
            End If
        Next lRow
    End With
   
    Build_SQL_Insert_Values = sSQL
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Insert_Values - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 

April 20, 2010  1:16 PM

Building a Library of Routines for Updating – #2

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
You are here (Click to enlarge)

You are here (Click to enlarge)

Last post provided SQL_Add_Update_Funtions.  This entry provides the next level up to the routines that relies on SQL_Add_Update_Functions.  The basic syntax of an SQL Update statement is:

UPDATE table_name
SET    column1=value, column2=value2,...
WHERE  some_column1=some_value1
  AND  some_column2=some_value2 ...

To build this statement, we have two routines.  One creates the “column=value” pairs of the “SET” clause , and the other creates the “WHERE” clause.    The column/field names come from the Fields Definition Table and the values come from the end user’s entries in the “Data” range.

There’s not any magic in either of these routines and I believe the in-code documentation expains things well enough, so without any further pontification, here are the next set of routines to add.  These should go into “modTableUpdate”.

Function Build_SQL_Update_Values(sFields As String, sTable As String, _
                                 sDataRange As String, lRecord As Long, _
                                 bRows As Boolean) As String
'   Description:Format field/column names & values for an SQL "Update" statement
'   Parameters: sFields:Range name containing field definitions
'                       Field:  Database Field/Column names
'                       Key:    Which fields are part of the unique key
'                       Table:  Database table/file name to update
'               sTable: Database table/file name to update
'               sDataRange: Range name that holds the values/data
'               lRecord: Record in sDataRange being updated (Row# if bRows=True)
'               bRows:  True means each record is in a row
'   Example:   
'	sSQL = _
'            "Update  " & sTable & " " & _
'            "Set    " & _
'             Build_SQL_Update_Values("Fields","Products","Data",1,True) & " " & _
'            "Where   " & _
'             Build_SQL_UpdDlt_Where_Clause("Fields","Products","Data",1,True)
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_Update_Values = "" 'Assume Something went wrong
   
    Dim lRow As Long
    Dim sSQL As String
    Dim sCRTB As String
    Dim sValue As String        'Current Value of the field being processed
    Dim sScreenField As String  'Screen Name of Current Field being processed
    Dim sTableField As String   'Table Name of Current Field being processed
    sCRTB = vbCr & vbTab & vbTab
    If sFields <= "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
            'Include only fields from stable and not part of the Primary Key
            If .Cells(lRow, FieldColumn("Table", sFields)) = sTable And Not _
                .Cells(lRow, FieldColumn("Key", sFields)) >= "A" Then
                sTableField = .Cells(lRow, FieldColumn("Field", sFields))
                sScreenField = .Cells(lRow, FieldColumn("Heading", sFields))
                If bRows Then
                    sValue = Range(sDataRange).Cells(lRecord + 1, _
                             FieldColumn(sScreenField, sDataRange))
                Else
                    sValue = Range(sDataRange).Cells( _
                             FieldRow(sScreenField, sDataRange), _
                             lRecord + 1)
                End If
                sSQL = sSQL & IIf(sSQL > "", ", " & sCRTB, "") & _
                       sTableField & " = " & _
                       SQL_Add_Update_Functions(sValue, lRow, sFields)
            End If
        Next lRow
    End With
   
    Build_SQL_Update_Values = sSQL
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Update_Values - Error#" & Err.Number & vbCrLf & _
         Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 
Function Build_SQL_UpdDlt_Where_Clause(sFields As String, sTable As String, _
				  sDataRange As String, lRecord As Long, _
				  bRows As Boolean) As String
   
'   Description:Create SQL Where clause for SQL "Update" or "Insert" statement
'   Parameters: sFields:Range name containing field definitions
'                       Field:  Database Field/Column names
'                       Key:    Which fields are part of the unique key
'                       Table:  Database table/file name to update
'               sTable: Database table/file name to update
'               sDataRange: Range name that holds the values/data
'               lRecord: Record in sDataRange being updated (Row# if bRows=True)
'               bRows:  True means each record is in a row
'   Example:   
'	sSQL = _
'            "Update  " & sTable & " " & _
'            "Set    " & _
'             Build_SQL_Update_Values("Fields","Products","Data",1,True) & " " & _
'            "Where   " & _
'             Build_SQL_UpdDlt_Where_Clause("Fields","Products","Data",1,True)
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_UpdDlt_Where_Clause = ""    'Assume Something went wrong
    Dim lRow As Long
    Dim sSQL As String
    Dim sCRTB As String
    Dim sValue As String        'Current Value of the field being processed
    Dim sScreenField As String  'Screen Name of Current Field being processed
    Dim sTableField As String   'Table Name of Current Field being processed
   
    If sFields <= "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
            'Include only fields from stable and not part of the Primary Key
            If .Cells(lRow, FieldColumn("Table", sFields)) = _
                sTable And .Cells(lRow, FieldColumn("Key", sFields)) >= "A" Then
                sTableField = .Cells(lRow, FieldColumn("Field", sFields))
                sScreenField = .Cells(lRow, FieldColumn("Heading", sFields))
                If bRows Then
                    sValue = Range(sDataRange).Cells(lRecord + 1, _
                        FieldColumn(sScreenField, sDataRange))
                Else
                    sValue = Range(sDataRange).Cells( _
                             FieldRow(sScreenField, sDataRange), _
                             lRecord + 1)
                End If
                sSQL = sSQL & IIf(sSQL <= "", "", vbCr & "  And   ") & _
                       .Cells(lRow, FieldColumn("Field", sFields)) & " = " & _
                       SQL_Add_Update_Functions(sValue, lRow, sFields)
            End If
        Next lRow
    End With
   
    Build_SQL_UpdDlt_Where_Clause = sSQL
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_UpdDlt_Where_Clause - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function


April 15, 2010  8:10 AM

Building a Library of Routines for Updating – #1

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
You are here (Click to enlarge)

You are here (Click to enlarge)

As mentioned before, we will be re-using the routines created for the reporting side. And just like we did for the reporting side of things, we need to build a library of routines before we can actually start using them to assemble applications.  This will take several posts because I use several small functions to perform specific tasks in the process (as opposed to a larger monolithic routine).  I prefer coding this way because I can test each routine more thoroughly if the code is kept simple and the functionality kept focused. 

You can get an overall view of the routines by clicking the diagram at right.

The first routine is SQL_Add_Update_Functions. This routine converts Excel formatted information into SQL friendly formats. For example, if the field in the database is character based, we need to surround the value entered in Excel with single quotes. Another example common to legacy systems is conversion of dates from Excel’s human friendly formats to system required formats, such as Julian dates (or more correctly “ordinal dates“). And on that note, to support SQL_Add_Update_Functions I have included the date format conversion routine Date2Julian and its sister Julian2Date.

By-the-way, I recommend that you create a separate module “modTableUpdate” and add SQL_Add_Update_Functions to it, along with the other routines we will add that will not require modification but are unique to spreadsheets that update databases. By having these routines in their own module, you can better manage them and include them as needed. I also recommend Date2Julian and Julian2Date be placed in “modGeneral” since they have broader applications.

  

Function SQL_Add_Update_Functions(sValue As String, lRow As Long, _
                                  sFields As String) As String
'   Description:Format XL data into SQL friendly formats
'   Parameters: sValue      An individual value to be posted to the database
'               lRow        Record within sFields describing field to hold sValue
'                           NOTE: This is equal to 'Field' number + 1
'               sFields     Field Definition range name
'   Example:    Debug.Print SQL_Add_Update_Functions("02/10/10", 2, "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    SQL_Add_Update_Functions = ""    'Assume Something went wrong
    sValue = Replace(sValue, "'", "`")
    With Range(sFields)
        Select Case UCase(.Cells(lRow, FieldColumn("Upd.Func.", sFields)))
            Case Is = "DATE2JULIAN" 
                If IsDate(sValue) Then
                    sValue = Date2Julian(sValue)
                Else
                    sValue = "NULL"
                End If
            Case Is = "DATE"
                If IsDate(sValue) Then
                    sValue = "'" & sValue & "'"
                Else
                    sValue = "NULL"
                End If
            Case Is = "HHMMSS"
                If IsNumeric(sValue) Then
                    sValue = sValue - Int(sValue)
                    sValue = "'" & Format(sValue, "hh:mm:ss") & "'"
                Else
                    sValue = "NULL"
                End If
            Case Is = "HHMM"
                If IsNumeric(sValue) Then
                    sValue = sValue - Int(sValue)
                    sValue = "'" & Format(sValue, "hh:mm") & "'"
                Else
                    sValue = "NULL"
                End If
            Case Is = "TRIM"
                sValue = "'" & Trim(sValue) & "'"
            Case Is = "VAL"
                If IsNumeric(sValue) Then
                    sValue = Val(sValue)
                Else
                    sValue = "NULL"
                End If
            Case Else
                If .Cells(lRow, FieldColumn("Format", sFields)) > "" Then
                    sValue = "'" & _
                        Format(sValue, _
                              .Cells(lRow, FieldColumn("Format", sFields))) & "'"
                Else
                    sValue = "'" & sValue & "'"
                End If
        End Select
    End With
   
    SQL_Add_Update_Functions = sValue
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "SQL_Add_Update_Functions - Error#" & Err.Number & vbCrLf & _
            Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 
Function Date2Julian(sDate As String) As String
   
'   Date2Julian:    Converts MM/DD/YYYY to YYYYDDD
'   Parameters:     sDate - the date to convert
'   Example:        sJulian = Date2Julian(format(now(), "mm/dd/yyyy"))
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Date2Julian = "2000001"
    Dim dDate As Date   
    dDate = DateValue(sDate)
    Date2Julian = Year(dDate) & _
                  Format(dDate - DateValue("01/01/" & Year(dDate)) + 1, "000")
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Date2Julian - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 
Function Julian2Date(sJulian As String) As Date
'   Julian2Date:    Converts YYYYDDD to MM/DD/YYYY
'   Parameters:     sJulian - the Julian date to convert
'   Example:        dPosted = Julian2Date("2009002")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Julian2Date = "01/01/01"
    Julian2Date = DateValue("01/01/" & _
                  Left(sJulian, 4)) + Val(Right(Trim(sJulian), 3)) - 1
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Julian2Date - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function


April 5, 2010  11:24 AM

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


March 23, 2010  5:09 PM

Updating Databases from Excel – First Looks

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Here is an example of an update spreadsheet.  We will use it in our tutorial. It updates the target stocking level (“Target Lvl”) for Products in the Northwind database.  It could just as easily, with different configuration, update an item master in SQL Server, iSeries DB2, Oracle, or just about any other relational database.
Update Example

Update Example

Normally, update spreadsheets have three buttons, “New”, “Load”, and “Post”.  I’ve trimmed this example to just two buttons for now.  We’ll add the “New” button later.
 
When the user first opens the spreadsheet, it is blank except for the buttons.  The user can click “Load” and select which products to change, or they can load all products as I have done in this example.  As followers of this blog might guess, the “Load” button acts just like our reports except for one formatting difference – the yellow cells. 
 
Yellow cells are the only cells that can be changed.  We use equivalent functionality to Excel’s “Format cell” > “Protection” > “Locked” option, along with the ActiveSheet.Protect Contents:=True to block users from changing anything outside the yellow areas.  We also use these cell qualities to guide keyboard navigation (cursor keys) to the next open cell.
 
Another change you might have noticed is the “ACD” column.  “ACD” stands for “Add, Change, Delete”.  An “X” indicates that a record already exists in the database.  When the user changes the “Target Lvl” column, the appropriate “ACD” cell changes to “C”, indicating the record changed.  When the user clicks the “Post” button, our spreadsheet finds rows with an “ACD” set to “C” and executes an SQL Update statement for us.  Fortunately, we can use the Fields Table to help take the drudgery out of constructing SQL statements.  Here is what the expanded “Fields Table” looks like.
 
Fields Table

Fields Table

 You can click the graphic to display a larger version of it.  The next post will explain the new fields.
 


March 17, 2010  8:19 PM

Updating Databases from Excel – The Basics

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post I encouraged readers to approach updating databases with care and professionalism. Some may wonder why then, use Excel at all if updates require the level of care in traditional programming. The answer is that Excel is a standard means for exchanging data.

As you know, the world is moving to software as a service, cloud computing, and disparate data systems. In other words, much of the data you need to run your business is, or soon will be, on the web. This data is usually in need of cleansing, transformation, denormalization, and integration with your host systems. The good news is just about every website that offers any kind of export, supports Excel, CSV, or XML formats – all of which Excel handles nicely. And by using the functionality available in Excel, you can shorten the chain of objects required to cleans and move data from website to your own server to just one object – no temporary files, no server side programs, no triggers, scheduled events, system administrator involvement – just one Excel spreadsheet.

A real world example is just now being realized in the trucking industry. The government has mandated a new means for scoring drivers and carriers. The new scoring system is called CSA-2010 and is intended to identify which drivers are likely to be involved in an accident. The new scoring system will (in my opinion) determine which drivers continue to work and which carriers continue to do business. The data for CSA-2010 comes from tens of thousands of police officers in the field from various jurisdictions who, when they pull over a trucker for a violation or at a scale, record an inspection result and upload it through a series of systems that are ultimately collected at the federal level. Carriers need this information integrated with their driver performance systems so they can take action to remediate at risk drivers and so they can improve their chances for earning business in an already cut-throat market. The problem is, with so many sources, the data often isn’t clean.

No problem. The data can be exported through XML to Excel where it is extremely easy to find inconsistencies, fix, cross check with other systems (using the data extract methods in the prior posts), and, finally, uploaded onto servers where it becomes part of the overall driver performance system.

The basic process is to read through an Excel range, pluck out values, match them to column/field names, and execute a simple SQL Insert (or Update, or even Delete) statement like:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (Cell(lRow, 1), Cell(lRow, 2), Cell(lRow, 3),...)

Now constructing such Insert (or Update) statements can be tedious so as we have in the past, we will be using the “Fields” table and code to automate most of that for us.


March 11, 2010  8:43 PM

Updating Databases from Excel

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

It seems almost every web portal that lists data includes an export to Excel, CSV, or XML, or combinations thereof.  What we are going to start exploring will enable you to capture that data, massage it, save it, and merge it with existing systems – using nothing more than Excel and ODBC.

We’re moving into controversial waters here.  It has been traditionally accepted that nobody should update a database except through a predefined program – for good reasons.  Updating servers from ODBC and Excel is considered by many auditors to be a violation – for good reasons.  DBA’s cringe at the notion of users updating server databases – for good reasons – because “fools rush in where angels fear to tread” (English poet Alexander Pope’s An essay on criticism, 1709).

In the last few weeks I have seen a few requests in the Question/Answer section of this site by people seeking ways to update server databases, especially the AS/400’s (iSeries) database, from Excel.  “It’s possible,” I explain, “but the answer is far too complex for a Question/Answer forum.”  Indeed it is, because if you want to avoid disaster, you MUST do the kinds of things traditional programmers have always done – check the data for sanity and silly mistakes before posting it to the database.

The techniques we will discuss will be along those lines.  We will see not only how to update the database, but also how to make sure the user put data in the proper columns, and that dates are valid, numeric fields contain numbers, text fields do not exceed field widths, codes and IDs match those in code tables and/or master files. 

We will be building on what we have done so far – so if you are new to this blog, please go back to the beginning and join us after you have built your library of routines given in prior posts.  We’ll be here waiting.

Function or Subroutine Module, Class, or Form Located in Post
Worksheet_BeforeDoubleClick Sheet1 Drill Down Part 2
Get_Data Sheet1 Drill Down Part 2
Prompt Sheet1 Drill Down Part 2
Build_SQL_Group_By modGeneral Group By and Aggregate SQL Functions
Add_XLFormula modGeneral Adding Excel Formulas – Part II
Parse_XLFormula modGeneral Adding Excel Formulas – Part II
Format_Results modGeneral Setting Column Widths or Hiding Them
Sort_Data modGeneral Adding Sorting
Freeze_Pane modGeneral Preserving Column Headers and Key Values
Build_SQL_Select_Fields modGeneral Adding Tables and Columns to the Fields Table
FieldColumn modGeneral Adding Tables and Columns to the Fields Table
Create_Named_Range modGeneral Creating the Fields Table
Fix_Name modGeneral Creating the Fields Table
Build_SQL_ID modGeneral It’s Time to Play
All Code and Properties for… frmPrompt Asking for It
All Code and Properties for… frmDatePicker Looking for a Date?
All Code and Properties for … frmSelect_Multiple Searching for Codes
SQLLoad modGeneral Say Goodbye to QueryTables
SQLRead modGeneral Using ADO to Read a Database
Pivot_Template modGeneral Wrapping things up
Setup_PivotChart modGeneral Building a Library of Routines – Setup_PivotChart
Setup_Pivot modGeneral Building a Library of Routines – Setup_Pivot
Settings modGeneral Building a Library of Routines – Settings
NameExists modGeneral Building a Library of Routines – ?Exists
ShapeExists modGeneral Building a Library of Routines – ?Exists
WorkSheetExists modGeneral Building a Library of Routines – ?Exists
PivotTableExists modGeneral Building a Library of Routines – ?Exists
ChartExists modGeneral Building a Library of Routines – ?Exists
Create_Easy_Button modGeneral Code for “easy” Button


March 4, 2010  6:32 PM

Drill Down – Part 2

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Before we get to coding, let’s look at the final product.  When we click our ‘easy’ button, our spreadsheet prompts us for dates, customers, and products.  If we just take the defaults, everything will be loaded from the NorthWinds database – like this:

Drill Down Before

Drill Down Before

If we look in the ‘Orders’ column we see that some lines have more than one order supporting them.  If we double click anywhere in the second line (say cell A6 for example) our finished spreadsheet will display those two orders so we can know the detail behind our summary.  It will look like this:

Drill Down After

Drill Down After

Now, as mentioned in the previous post, we need to restructure our code a bit.  We need to eliminate the Macro1() in our module, and create three routines in the sheet’s class (for how to get to the sheet’s class review prior post).  So here is the first bit of code – constants that will make modifying this template for use on other databases easier:

Const sConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                 "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"
Const sData = "Data"
                 

These constants go at the very top of our sheet’s class.   And, as before, you will need to modify the path to your copy of the Northwind database (highlighted in red).

The next bit of code is the sheet’s double click event handler.  When you double click on worksheet ‘Data’, this even automatically fires.  All we need to do is wire it up to do what we want.  In this case, we want to load the detail associated with the Customer ID and Product Code on the line that is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
'   Worksheet_BeforeDoubleClick:  Excel's double click event handler
'   Parameters: Target  Cell that was double clicked
'               Cancel
'   Example:    <Automatically called when user double clicks worksheet>
'     Date   Init Modification
'   02/23/10 CWH  Initial Programming
    On Error GoTo ErrHandler            '
   
    If NameExists(sData) Then
       
        Dim lColDta As Long
            lColDta = Range(sData).Column
        Dim lColCus As Long
            lColCus = lColDta + FieldColumn("Customer ID", sData) - 1
        Dim lColPrd As Long
            lColPrd = lColDta + FieldColumn("Product Code", sData) - 1
       
        Get_Data "Fields_Detail", _
                 Trim(Cells(Target.Row, lColCus)), _
                 Trim(Cells(Target.Row, lColPrd)), _
                 frmPrompt.pFrom, frmPrompt.pTo
    End If
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Worksheet_BeforeDoubleClick - Error#" & Err.Number & vbCrLf _
        & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Sub

 

As you can see, there’s not much to it.  But it requires a new routine ‘Get_Data’.  ‘Get_Data’ is half of our Macro1() – the part that doesn’t ask for any information but instead builds the SQL and submits it.  It looks like this:

Private Function Get_Data(sFields As String, _
                  sID1 As String, sID2 As String, _
                  dFrom As Date, dTo As Date) As Boolean
'   Get_Data:   Processes request from Prompt or Worksheet_BeforeDoubleClick
'   Parameters: sFields Name of the Field Definition table
'               sID1    Customer ID(s)
'               sID2    Product code(s)
'               dFrom   Ordered date range start
'               dTo     Ordered date range stop
'   Example:    If .pOK Then Get_Data "Fields", "8", "'NWC-14', 'NWCA-21'", _
'                                      Int(now())-30, Int(Now())
'     Date   Init Modification
'   02/23/10 CWH  Initial Programming
    On Error GoTo ErrHandler            '
    Get_Data = Failure                  'Assume the Worst
    Dim sSQL As String
           
    sSQL = "SELECT  " & Build_SQL_Select_Fields(sFields) & vbCr & _
           "FROM    Customers C, Orders O, " & vbCr & _
           "        `Order Details` D, Products P " & vbCr & _
           "WHERE   O.`Customer ID` = C.ID " & vbCr & _
           "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
           "  AND   D.`Product ID`  = P.ID " & vbCr & _
           "  AND   O.`Order Date` Between #" & _
                    Format(dFrom, "mm/dd/yyyy") & "# And #" & _
                    Format(dTo, "mm/dd/yyyy") & "# " & vbCr & _
            Build_SQL_ID("O.`Customer ID`", Trim(sID1), False) & vbCr & _
            Build_SQL_ID("P.`Product Code`", Trim(sID2), True) & vbCr & _
            "GROUP BY " & Build_SQL_Group_By(sFields, "*")
    
    SQLLoad sSQL, sConnect, "A4", "Data", "Data"
    If NameExists(sData) Then
        If Range(sData).Rows.Count > 1 Then
            Add_XLFormula sData, sFields
            Freeze_Pane sData, sFields
            Sort_Data sData, sFields
            Format_Results sData, sFields
            If sFields = "Fields_Detail" Then Pivot_Template
        End If
    End If
   
    Get_Data = Success                  'Successful finish
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Get_Data - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

That should look very familiar to you.  The reason we split this from the part of Macro1() that asks for input is because we want only one routine to load data for us – whether the input comes from frmPrompt or from Worksheet_BeforeDoubleClick.

So now all we need to do is add back the part that sets up and displays frmPrompt.  It looks like this:

Private Sub Prompt()
'   Prompt:     Ask user for report parameters
'   Parameters: None
'   Example:    Prompt
'     Date   Init Modification
'   02/23/10 CWH  Initial Programming
    On Error GoTo ErrHandler            '
    With frmPrompt
               
        .pDateLbl = "Ordered Dates"
        .pFromVisible = True
        .pFrom = "01/01/2006"
        .pToVisible = True
        .pTo = Format(Now(), "mm/dd/yyyy")
       
        .pID1Visible = True
        .pID1Lbl = "Customer ID(s)"
        .pTitle1 = "Select Customers"
        .pConnect1 = sConnect
        .pLblCode1 = "Code"
        .pSQLCode1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    ID like '%?%' " & vbCr & _
                     "Order By ID"
        .pLblDesc1 = "Company"
        .pSQLDesc1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    Company like '%?%' " & vbCr & _
                     "Order By Company"
       
        .pID2Visible = True
        .pID2Lbl = "Product Code(s)"
        .pConnect2 = sConnect
        .pLblCode2 = "Code"
        .pSQLCode2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Code` like '%?%' " & vbCr & _
                     "Order By `Product Code`"
        .pLblDesc2 = "Name"
        .pSQLDesc2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Name` like '%?%' " & vbCr & _
                     "Order By `Product Name`"
                    
       .Show                                         'Display the Prompt
        Do While .Visible                            'Wait on user
            DoEvents
        Loop
        If .pOK Then Get_Data "Fields", _
                              Trim(UCase(.pID1)), Trim(UCase(.pID2)), _
                              .pFrom, .pTo
    End With
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Prompt - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Sub

When you’re finished make sure to get rid of Macro1(), and then right click on the ‘easy’ button and assign it to macro ‘Sheet1.Prompt’. 

There’s one more advantage to structuring our code this way – almost all of the code that needs to be modified to use this template on just about any other database – AS/400, SQL Server, Oracle, MySQL, etc – on just about any table or set of tables – is contained in this one sheet.  This makes adapting this very quick and easy.


February 24, 2010  6:40 PM

Drill Down – Part 1

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post I showed how we can present summarized data when what our users want is supported by detail that would fill up tens of thousands of rows.  When we summarized the data, in a sense, we duplicated the functionality of a simple “pivot table.” 

Pivot Tables have one feature we didn’t duplicate.  It’s a feature that always astonishes new Pivot Tables users.  That feature is the automatic “drill down” that happens when a value is double clicked, revealing the underlying detail.  Users love this and it’s not tough to add it to the summarized sheet we created last post.  The secret is in Excel’s Worksheet_BeforeDoubleClick event handler.  Using this event handler, we can respond to the user double clicking on a data row and using information in that data row, pull up the detail supporting it. 

Now because we are wading into deeper VBA waters, it’s appropriate to introduce some more polished coding techniques.  We will be dumping Macro1() in Module1.  Instead we are going to break it up into three routines.

  1. Worksheet_BeforeDoubleClick – This will handle the drill down request
  2. Prompt – Presents the user with our prompt form when they press the “easy” button.
  3. Get_Data – Gets and presents the data based on the parameters from Prompt or Worksheet_BeforeDoubleClick

Worksheet_BeforeDoubleClick cannot reside module1.  It must be placed in the worksheet class.

Code in worksheet classes has some special properties we need to be aware of.  Code in a module is shared across ALL worksheets.  Routines in a worksheet are “local” to that worksheet.   That means, variables declared in, and code written in a worksheet class are unique to that worksheet.  Thus, we can have the same name for a routine in each worksheet with each routine meeting the specific needs of its own worksheet. 

The Worksheet_BeforeDoubleClick is a fine example of this.  Every worksheet has a Worksheet_BeforeDoubleClick routine.  By default, they are empty – so by default, they do nothing.  The Worksheet_BeforeDoubleClick in our Sheet1 will be different.  We will add code so it will “drill down” into the data.  But we ONLY want this behavior in Sheet1.  We don’t want our “drill down” routine to respond to double clicks in our “Fields” table, and we certainly don’t want it to override our Pivot Table’s drill down functionality.  Fortunately for us, a worksheet’s local event handlers ONLY respond to it and no other worksheet.

We also want to take advantage of this “local” nature of routines placed in the worksheet class.  As mentioned earlier, we are going to break up the Macro1() into three separate routines.  These routines will be specific to processing this particular dataset.  However, in some future project, you may want to have several worksheets in a workbook with each worksheet analyzing one set of data that, with the other sets, provides a suite of analytical tools.  By making the routines local, each worksheet can have its own version of Prompt and Get_Data with each version modified as needed to support the unique requirements of its own worksheet.

VBE Drill Down Project

VBE Drill Down Project

To add code to Worksheet_BeforeDoubleClick we must:

  1. Select Sheet1 from Project Explorer
  2. Select Worksheet from the Code Panel’s left drop down
  3. Select BeforeDoubleClick from the Code Panel’s right drop down

Excel will then provide a subroutine shell for us to add our specific code.   And that will be the subject of our next post.


February 19, 2010  5:38 PM

Group By and Aggregate SQL Functions

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

At this point, we have everything we need for about 95% of user reporting and analytical requests.  On rare occasions we need to summarize the data prior to putting it into our Excel spreadsheets.  This happens when the raw detail for an expected time frame exceeds Excel’s normal 65,536 row limit and is more detailed than required for proper analysis.  An example might be a request to review orders for an entire year by customer and product.  In moderately sized companies, such requests could easily exceed 65,536 records.

We can work around this limitation by aggregating records so that several database records are represented in Excel as one row.  We do this with SQL’s aggregate functions which include:

COUNT(*), SUM(field), AVG(field), MAX(field), MIN(field), FIRST(field), LAST(field)

If you use these functions you must add a GROUP BY clause.  I touched on this last month in the post Adding SQL Formulas to the Table.  The basic rule for the GROUP BY clause is:

All fields in the SELECT clause that are NOT one of the SQL Aggregate Functions, MUST be included in the GROUP BY clause. 

GROUP BY clauses are tedious and often a source of errors for the SQL novice.  If you omit or mis-transcribe any SELECT field, your SQL will fail.  That’s why SQL novices typically fail on their first attempts with GROUP BY clauses.  Since the GROUP BY clause is nearly a replica of the SELECT clause, creating them is tedious.  So of course we need to automate that to make our work easier, increase our productivity, and improve the quality of our products.  Here is a routine that does just that (with a Fields Table to use test your results).

Fields Table for GROUP BY example

Fields Table for GROUP BY example

 

Function Build_SQL_Group_By(sFields As String, sTable As String) As String
'   Build_SQL_Group_By: Build SQL "Group By" field list
'   NOTE: SQL Aggregate Functions include:
'         Avg(),Count(),First(),Last(),Max(),Min(),Sum()
'   Parameters:
'       sFields:    Field definition range name.  It must contain:
'
'           Field:     Field/Column names
'           Alias:     File/Table Alias (ex: L for the table LOADS)
'           Table:     Field's Table name
'           SQL Func.: SQL function/expression
'       sTable:     Table being grouped.  "*" indicates all fields from
'                   all tables will be grouped
'   Example:
'       sSQL = sSQL & "Group By " & _
'              Build_SQL_Group_By("Fields", "*")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Build_SQL_Group_By = "" 'Assume Something went wrong
    Dim lRow As Long
    Dim sSQL As String
    Dim sSQLFunc As String  'Value in the SQL Func.
    Dim sFld As String      'Field with Alias
   
    Dim lColTbl As Long
        lColTbl = FieldColumn("Table", sFields)
    Dim lColFld As Long
        lColFld = FieldColumn("Field", sFields)
    Dim lColSQL As Long
        lColSQL = FieldColumn("SQL Func.", sFields)
    Dim lColAls As Long
        lColAls = FieldColumn("Alias", sFields)
   
    If Trim(sFields) = "" Or Trim(sTable) = "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
           'Include fields for indicated table
            If (sTable = .Cells(lRow, lColFld) Or sTable = "*") Then
                sSQLFunc = Trim(UCase(.Cells(lRow, lColSQL)))
               'Include fields that are not an aggregate function
                If sSQLFunc = "" Or _
                   InStr(1, "AVG,COU,FIR,LAS,MAX,MIN,SUM", _
                         Left(sSQLFunc, 3)) = 0 Then
                   'Combine Field with Alias
                    sFld = IIf(Trim(.Cells(lRow, lColAls)) > "", _
                               Trim(.Cells(lRow, lColAls)) & ".", "") & _
                           Replace(.Cells(lRow, lColFld), """", "'")
                   'If SQL Function has a "?", replace with Field
                    If InStr(1, sSQLFunc, "?") > 0 Then _
                        sSQLFunc = _
                            Replace(sSQLFunc, "?", sFld)
                   'If SQL Function, use it instead of Field
                    If sSQLFunc > "" Then sFld = sSQLFunc
                    sSQL = sSQL & IIf(sSQL > "", ", ", "") & sFld
                End If
            End If
        Next lRow
    End With
   
    Build_SQL_Group_By = sSQL
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Group_By - Error#" & Err.Number & vbCrLf & _
	Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 

Below is the change to our Macro1() highlighted in red.  Pivot_Template has also been removed because this example has no “State” column.

If .pOK Then                                 'OK button used to exit
           
    sSQL = "SELECT  " & Build_SQL_Select_Fields("Fields") & vbCr & _
           "FROM    Customers C, Orders O, " & vbCr & _
           "        `Order Details` D, Products P " & vbCr & _
           "WHERE   O.`Customer ID` = C.ID " & vbCr & _
           "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
           "  AND   D.`Product ID`  = P.ID " & vbCr & _
           "  AND   O.`Order Date` Between #" & _
                    Format(.pFrom, "mm/dd/yyyy") & "# And #" & _
                    Format(.pTo, "mm/dd/yyyy") & "# " & vbCr & _
            Build_SQL_ID("O.`Customer ID`", Trim(.pID1), False) & vbCr & _
            Build_SQL_ID("P.`Product Code`", Trim(.pID2), True) & vbCr & _
            "GROUP BY " & Build_SQL_Group_By("Fields", "*")
    SQLLoad sSQL, sConnect, "A4", "Data", "Data"
   
    If NameExists("Data") Then
        If Range("Data").Rows.Count > 1 Then
            Add_XLFormula "Data", "Fields"
            Freeze_Pane "Data", "Fields"
            Sort_Data "Data", "Fields"
            Format_Results "Data", "Fields"
        End If
    End If
         
End If
 


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: