Excel in Front, part 2

Project Update: I'm happy to say that so far the Excel Front End project has been as smooth as glass.  No only were the client's expectations exceeded in every regard, but my personal experience writing the application has been immensely satisfying.

If you recall from last post: I was hired to design and build a work-order tracking system for a manufacturing facility, using Excel as the front-end to an Access Database.  When I set out on this project, the main challenge was that the client's shared workbook approach was causing a lot of trouble, and undermining their confidence in the data.  They asked for my help, as an Excel consultant, to sort out the file sharing issues and build in some business logic and data validation rules.  

I proposed two solutions: keep the shared workbook but apply sharing permissions effectively and write the required macros to ensure data integrity, or the more involved, expensive option: move the data to Access and create an intelligent front-end in Excel so users can interact with it.  They opted for the Access option, understanding the scalability advantages, and the cost savings they will achieve when the time comes to build out some of the other data management applications they need.

I developed a vision for how the system should work: no shared workbooks, no linked files, no open connections, and especially: no Access, as in, no developing in Access (because, well, it's crap.  Sorry, but I hate it.)  I have linked Excel to Access before, so I knew how to do it, but I didn't know if this architecture would perform acceptably with this particular set of business logic and data until I actually built it.   

Typical client-server apps use a persistent database connection.  This is fine when using an Enterprise RDBMS such as SQL Server, but in a small-scale deployment using an .mdb file, I just knew that trying to simultaneously connect 20 or so users to a shared Access db would be a recipe for failure.  So I designed the system to interact with the database in instant-batch mode: need some data? Connect to the db, fetch the data into memory, disconnect from the file, then display the formatted results.  This way, no single user has an open connection to the access file for more than a fraction of a second at a time, virtually eliminating any chance of network traffic bottlenecks, record-locking stalemates, and (hopefully) database corruption.

The following are some key design points I employed:

• Created a de-normalized view of the data, by way of an Access Query, which joins the related data tables to give a fully-qualified view of all Work Orders in the system.  This Master Query becomes the main Result Set that the Excel side queries from.
• Rather than code separate reports for all the different needs of the client, coded a Report Engine that can be configured as needed, enabling all current and future reports to be designed without programmer intervention.
• Matched the field names in the Master Query to the column headings used in Excel.  This allows the user to build their own filters and searches by selecting from known field-names.  
• All user-definable settings are stored in the database, keyed to the user's login.  This way, when the Excel front end file is updated, it can reload all the user's settings and they can continue to work without any setup time.
• Used generic database SELECT and EXECUTE routines, so all db-related code passes through a single gateway.  If we later to decide to change our Database server, we won't have to search through hundreds of routines to change all the affected code.
• Captured cell-edits using the Worksheet_Change event.  Updates are validated, and if allowed, instantly written to the database.  Cell formatting and locking are applied as the data is displayed, so the users know (and are only allowed to edit) the editable parts of each record.

Amazingly, this project came together without a hitch.  The incredible flexibility and programmability of Excel enabled me to build every requirement the client asked for, and since I didn't get bogged down searching for bug workarounds and advice, I had time left in the budget to add some really sweet features to the application.  Next Tuesday we deploy the app to the users, and they are really looking forward to the improved efficiency and productivity advantages they will be getting.  And the client has asked for help with a second project for another division in the company, which is obviously very welcome: follow-on work is a sure sign you're doing a good job.

Unfortunately I can't share many more details about this application because I have signed a non-disclosure agreement, which binds me to keep any client-specific or business-specific information secret.  But here is some code which I have used in this and other projects: I keep this in a code module called "DBConnectivity". It is offered without warranty, but it might come in handy for anyone attempting a project that connects Excel to an Access database file.  This code references the Microsoft DAO 3.6 Object Library, so you will need to create a reference to that library in your project.

Public DB as Database

Public Function OpenDBConn() As Database
     
  'Opens the database connection using global db object
     
   Dim strDBName As String
   
   On Error Resume Next
   Err.Clear
   
   Set OpenDBConn = Nothing
   
  'get DBName from cell on main worksheet
   strDBName = Range("DBPath")
   If Len(Dir$(strDBName, vbNormal)) = 0 Then
    gglobalerror = "The database could not be opened. " & _
          "Please select the database file on the " & _
          "Main Tab and try again."
      Exit Function
   End If

   Set OpenDBConn = DBEngine.OpenDatabase(strDBName)
   If Err.Number <> 0 Then
      Set OpenDBConn = Nothing
   End If

End Function


Public Function SQLSelect(ByVal strSQL As String, _
       ByRef sList() As String) As Integer

'Execute the passed SQL (strSQL) and return the 
'retrieved results in the passed array (sList)
'this method is coded for the Microsoft DAO 3.6 
'library.  modify this and SQLExecute
'routines if migrating to another data provider, 
'such as SQL Server.

  Dim rsList As Recordset
  Dim iColCnt As Integer, iRowCnt As Integer, 
  DIM idxRow As Integer, idxCol As Integer

  gGlobalError = ""
  On Error GoTo Err_SQLSelect

 'try db connection.
  SET DB = OpenDBConn
  If DB is Nothing then Goto Cleanup

  Set rsList = DB.OpenRecordset(strSQL, _
                                dbOpenSnapshot, _
                                dbReadOnly)

 'check for no data
  If rsList.EOF Then
     SQLSelect = 0
     GoTo Cleanup
  End If

 'Size the array
  iColCnt = rsList.Fields.Count
  rsList.MoveLast: rsList.MoveFirst
  iRowCnt = rsList.RecordCount
  ReDim sList(iColCnt, iRowCnt) As String

 'store recordset rows in array
  Do Until rsList.EOF
    idxRow = idxRow + 1
   'Load values for the row
    For idxCol = 1 To iColCnt
      If IsNull(rsList.Fields(idxCol - 1)) Then
        sList(idxCol, idxRow) = ""
      Else
        sList(idxCol, idxRow) = rsList.Fields(idxCol - 1)
      End If
    Next
    rsList.MoveNext
  Loop

SQLSelectExit:
  SQLSelect = UBound(sList, 2)

Cleanup:
  On Error Resume Next
  rsList.Close
  Set rsList = Nothing
  DB.Close

  Exit Function

Err_SQLSelect:
 'On Error Resume Next
  gGlobalError = "Select from the database failed!  " & _
    " & Err.Description & vbCrLf & " _
    SQL: " & strSQL
  Debug.Print gGlobalError
  SQLSelect = -1

  Resume Cleanup

End Function


Public Function SQLExecute(ByRef strSQL As String) _
 As Integer

 'Execute the passed SQL statement contained in the 
 'string strSQL
  Dim strErr As String

  SQLExecute = -1
  gGlobalError = ""
  On Error GoTo Err_SQLExecute

 'try db connection.
  SET DB = OpenDBConn
  If DB is Nothing then Goto Cleanup

  DB.Execute strSQL, dbFailOnError
  SQLExecute = 1

Cleanup:
  On Error Resume Next
  DB.Close

  Exit Function

Err_SQLExecute:
  strErr = Err.Description
  gGlobalError = "SQLExecute failed!" & _
       vbCrLf & "SQL: " & strSQL & " _
       Err:" & strErr
  Debug.Print gGlobalError
  SQLExecute = -1

End Function