Check your References

When you are building advanced Excel applications, you occasionally need to call upon logic or features provided outside of Excel. This is done by adding a REFERENCE to an external application or library, by way of the VBA Editor's Tools menu.  Just about every application on your computer shows up here, enabling your code to leverage highly specific functionality such as enhanced user interface elements, or internet data services.

In a previous post I explained how to get Outlook to send email from Excel. The code to do so requires a reference to the Microsoft Outlook 11 Object Library. A recent application I created does this, and also uses several other libraries, including an HTML Browser control to preview PDF files, an Access database, and has a set of Custom UserForms - all requiring project references.  Each one of these represents an actual file - usually a DLL - installed and registered on the host system.

This makes Excel incredibly powerful.  But using this mechanism can complicate the deployment of your software, because you can't always control what is or isn't installed and registered on the end-user's computer. This can also be a problem if you are a stand-alone developer, but need to move your work between home and office machines.  If a referenced DLL or program is not available, you end up with a Broken Reference.

Another coder, Andrew Roberts, blogged about this very problem, and provided a technique to programatically re-add broken references, (for Outlook, in his example.)  It's a good fix, but has limitations.  For one, it depends on a specific Excel security setting to work, which is  probably not something you're going to want to ask 30 novice operators to change on 30 different computers.  And if your project code is password-protected, this approach may not work at all.


Protected Project and Protected References

In my project, I am using the Redemption library for Outlook.  This toolset allows VBA to manipulate Outlook objects quietly, in the background, instead of requiring the user to approve a series of popup warnings shown as part of Outlook's security protocol.

Not all users need this extra level of functionality, so I wrote some code to "ask for Redemption" - users who do not install this extra software should not be show-stopped by a broken reference.  The program should instead default to the standard Outlook experience.

I ran into a major snag: the VBA code that allows you to view and verify project references doesn't work when your VBA project is password-protected - unless the project is open in the VBA editor!

My code needs to be password-protected because of the nature of my programming work - I program for profit.  I don't want every employee at my client's location to freely view and copy the code I have written - this is my sweat and blood, and I try to maintain at least an illusion of copyright protection on it.  I also don't want operators to change or hack it.

Still, some code I share freely: here's a routine you can call from your workbook's open event to programatically inspect the project reference collection.

Sub reftest()
    
   'which references are visible and/or broken?
    Dim chkRef As Variant
    
    Debug.Print vbCrLf & vbCrLf & " - Reference Check -"
    
    For Each chkRef In ThisWorkbook.VBProject.References
      Debug.Print chkRef.Description & "  " & _
      Choose(chkRef.isBroken + 1, "", " - broken"
    Next

End Sub

In the VBA development environment, it all works wonderfully. All the references I added showed up.  But if I close Excel, and then re-open the model, (which calls this routine from it's Workbook Open event,) the references I added to the project become invisible to the code.  All you see are the 4 standard references that all projects have:


VBA   Visual Basic For Applications
Excel   Microsoft Excel 11.0 Object Library
stdole  OLE Automation
Office  Microsoft Office 11.0 Object Library

My code can't tell if the Redemption reference is broken or not, and so can't handle the situation gracefully.

I believe Excel hides the extra References by design - these are part of your code's functionality and overall design strategy, which is the I.P. that your trying to protect by putting a password on it.  However, since it's the code "asking", not a person via the IDE, I think it should be possible to enumerate the full collection of program references.  This would allow the kind of self-diagnostic logic I'm looking for.

I'm not sure what the work-around is for this at present - I'll probably end up using some sort of in-function error trap.  I'll report on my solution in a future post.  I'm open, as always, to suggestions via this blog's comment facility.

One of the really good things about Microsoft Windows and the Office programs is the ability to interoperate and exchange data between applications.  If you want to ensure portability and longevity of your code, just don't get carried away adding 3rd party libraries. 

SQuirreL away that data

The most useful language any programmer should know is T-SQL - structured query language.  When you have a solid understanding of relational database architecture and the tools to manipulate it, extracting handy information from your business data becomes almost effortless.

Relational DB systems abound: Access, SQL Server, Oracle, MySQL and PostgreSQL are a few examples.  You can even use SQL to intelligently compile data right out of your Excel worksheets.

As macro developers, we often rely on canned reports produced by corporate systems to feed us data.  This is usually in the form of .csv text files, web scrapings, or stacks of steaming, reeking workbooks that take hours to recalculate.  Sometimes, especially if data volumes are large, moving everything into Access and pulling it back into Excel using SQL Select statements can be the best way to carve out the specific information required.

In a recent project, a client was mining several years worth of member profile data to compile statistics and spot geographic trends.  The prototype system, using a small subset of data and a few pivot tables, appeared to get the job done.  But the workbook was already topping 20MB in size - a real pain to shuttle around.  Then the full data set accumulated to over 65,000 records - more than the number of rows in an Excel 03 (client's spec) worksheet... time to step up to MDB.

I massaged the data in pieces in Excel, imported it into an Access database, and was stunned to find the .mdb file to be only 3.2 MB in size!  That's sweet compression.  Smaller than my prototype workbook was, zipped, but holding 20 times the data.

Next, do away with pivot tables.  Nothing personal. I actually think pt's are a pretty good idea, but they can be unwieldy, and many end users fear them.  Programming them takes you into the outer wastelands of VBA - sketchy knowledge for most of us, (but examples are out there,) and getting exactly the result you need might not be possible - this has been my experience with most of Excel's special power features.

I think SQL is considerably more powerful, and a lot less complicated.  With a simple* piece of code I can build a mini pivot table that doesn't pivot, because it's already pivoted to what the user wants to see.  Here's an example:

SELECT TOP 10 ZipCode, AVG(Revenue) 
FROM MemberView GROUP BY ZipCode ORDER BY 2 DESC

This gives us a nice little Answer, suitable for publication in this years Annual Report - a breakdown of the top 10 zip codes by average revenue.

The key to this is setting up a View, which is another more complex SELECT query that joins data from different tables.  This View can also pre-compute some answers, convert ugly binary 1's and 0's to pleasant things like "Yes" and "No", and limit the data set as needed.  It sits in the DB and looks just like another table.  This brings all the data we need to a single point of light.

I wanted to let users pick the field to zip-rank.  For this I linked a drop-down control to a list of cells containing the fields in my View, and the report macro simply subs the user choice into the Select statement.  With this dead-simple, pre-pivoted table maker, users can't mess up.  Instead, they grind out report after report, all day long, grinning like madmen.

Doing some of the heavy lifting in the database itself means the macros and formulas can be much simpler, and the JET database engine can crunch the numbers a lot faster than Excel anyway.  I love that my Business Reporting System workbook is only 100Kb.  Not having data to lug around means macro updates and bug fixes don't impact the client, which helps keep me off-site.  When the data set scales up even further, I'm well positioned to migrate to an enterprise system like SQL server, with minimal impact on the Excel code.

In a Previous Post I showed you some VBA code to support Access DB connectivity and  pulling data using SQL Select statements.  This should be enough to get you started.  I'd love to hear about any nifty tricks or nasty pitfalls you encounter.

Shrapnel

I've begun to migrate with some of my clients to the 3 year old Excel.  I'm well past the whole Ribbon controversy - yet I still get a sense that the new ship is still a bit leaky.  Office has thrown a few curve balls at me, so here are some cheap and effective tidbits of advice I can pass along:

Don't... 

...create pivot tables in Excel 2007 and then save the file as a 2003 compatible .xls - it all goes to hell.


...try to draw a flow chart in a Word 2007 document using AutoShapes.  Visio is better if you have it.


...run untested Excel VBA code that references the MS Word object model without setting up error handler's first.


...insert a PDF object into a document that may end up being printed to PDF.


...dispair.  No matter how low the economy goes, businesses will always have spreadsheets.


Cheap and Effective

I've been working on a B2B application - a variation on the standard "request for quote" problem.  In this case, a request is assembled from data on the spreadsheet, dropped into an email, and sent to a slew of individuals somewhere who may not even have Excel (poor sods.) They hit reply, fill in some sections of the copied message, and send.  Another macro connects to Outlook, scans a particular in-box, and pulls the details from any messages found as they come back.

This is, like my advice above, cheap and effective.  It's a way to do email forms with no workgroup software or exchange programming.  It doesn't involve sending or sharing files, and is not limited to the corporate LAN.  Data can even be collected electronically from people who aren't sitting at their desk, working, like I am always.

Hey, isn't this what websites are for?  Um, yeah, but this approach bypasses the need for website programming - no offence to web programmers (since I used to be one,) but power macro artistes deal in turn-around times of hours, not weeks, and the data needs to get back to Excel in the end anyway, right?  
 
Excel talks quite nicely to outlook.  Here's some code that sends an email message - it's really dead easy as long as you set up the appropriate references.  Perhaps the next post can explore the response capture side of things.

Public Function SendAMessage(strSubject As String, _
         strBody As String, _
         strAddress As String, _
         Optional strAttachment As String = "") _
                As Boolean

 'strAttachment, if used, will be the full 
 'file path of a file
 'requires a reference to the Microsoft 
 'Outlook 12.0 Object Library
 'see Tools Menu - References
  
  Dim email As Outlook.MailItem
  
  On Error GoTo Fail
  SendAMessage = False

  Set email = Outlook.CreateItem(olMailItem)
     
 'make sure we've been passed sufficient info
  If Len(strAddress) = 0 Or Len(strSubject) = 0 _
  Or Len(strBody) = 0 Then
  
    'trigger an error
     Err.Raise 999, , "Missing Information"
     
  Else

    'assemble the message
     With email
        .Subject = strSubject
        .To = strAddress
        .Body = strBody
        
        'attachment if required
         If Len(strAttachment) > 0 Then
           .Attachments.Add strAttachment
         End If
        
        .Send
        
     End With
     
    'it worked
     SendAMessage = True
  
  End If

Cleanup:
  On Error Resume Next
  Set email = Nothing
  Exit Function

Fail:
 'it didn't work
  gGlobalError = Err.Description
  Debug.Print "SendAMessage Failed. " &_
               Err.Description
  Resume Cleanup
  
End Function

Back to Business

This post is so long overdue I'm almost ashamed. If I'd had the entire summer off, that would be one excuse, but the real reason for the delay was that I've been too busy writing code to write anything else.  Okay, I will admit to enjoying a reduced workload in July - 20 hours instead of my usual target of 120!  In any other month this would be a catastrophe, but I try to keep July work-free, as a personal reward for the year's efforts.  That's one of the principal advantages of being self-employed, after all.

As mentioned in a previous post, I landed a return engagement with the company for whom I created an Excel front end to an Access database.  Another department within the company saw how effective the first application was, and hired me to build something similar, to handle their Purchase Order processing workflow.  This has kept me busy for all of August.

The business logic was a lot more complex for this project, but I was able to re-use quite a bit of the architecture from the previous application: User Account Management, database connectivity code, list filtering and sorting... There's nothing like the efficiency and confidence you get from writing code around a proven framework: instead of messing with the fiddly bits, I was able to focus on adding business functionality and making the software friendly and usable.  Client's don't generally get excited to learn that the TCP/IP bottleneck has been optimized, but they are always thrilled to hear about the one-click report engine you've designed!  This project is currently in testing, and so far, the reception has been 100% positive.  I aims to please.

As a challenge to myself, I decided to do this development entirely in Excel 2007.  I usually do my work in 2003, since I find that version of Excel more responsive and pleasant to use.  If the client will be running 2007, the "xls" file from Excel 03 usually just works.  But this time I opted to skip 2003 altogether, to see what would happen.  To my surprise, I found I was able to make the transition with very little pain and suffering.  

It took me a few days to really get the hang of using the Ribbon - I still find myself hunting around for some Excel features, but the learning curve was not as steep as I had predicted.  The benefit is that the file is running "native" in Excel 2007 - no "compatibility mode."  I'm not sure if this is a real advantage (other than looking cleaner on the Excel title bar), but at least I'll know that, if some strange bug or behaviour pops up, it's not an artifact of the file conversion process.  More importantly, I am now more efficient using and interacting with Excel 2007, and feel comfortable porting my personal spreadsheets and Client work to this platform.

Looking forward to the Fall, it appears I will continue to be very busy for the foreseeable future.  Since starting out as an independent consultant 4 years ago, I have been slowly building my roster of clients.  Many projects are one-off, where I go in, figure out what needs doing, write the app, and that's more-or-less the end of the engagement.  But several of my clients are power Excel users who are constantly looking for new short-cuts, utilities, or techniques to apply to their daily business activities.  

As I add more and more of these types of clients to my roster, I'm given an increasing load of "casual" hours - short fixes, mini projects, and extension of existing work.  This ad-hoc stuff is taking up enough of my time that it effectively fills in the gaps between bigger projects.  It has taken a few years, but now I have the business humming along perfectly - working enough to pay the bills, but not so much that I'm overwhelmed.  If that starts happening, I'll just start raising my hourly rate.  Everything is going according to plan.

Applying the Application Object

VBA coders who want do do more than automate the basic Select, Copy and Paste functions of Excel eventually discover the object model, and the world of possibilities it brings.  One object that is sometimes overlooked is Application.  Here are a few examples of how we can use the Excel Application object in our VBA code.

One of my personal favourites is Application.StatusBar.  The status bar is the strip of real estate at the bottom of the Excel window where you see system messages and so forth.  Programmers can write to this area, which can be an intuitive and logical way to keep your users informed about what your macro is doing.  As explained in a previous post on Speed Optimization, if your process is long and involves a loop, periodic updates to the status bar can tell the user how far along the process has advanced.  I usually use a percent complete message, as exemplified in the following code:


For idx = 1 to intNumRows
   {... code does something here....}
   Application.StatusBar = "Running... " & _
   CInt((idx/intNumRows)*100) & "% complete"
Next


Given this feedback, the user can predict how long the macro will take to run, which actually makes it appear to run faster.  More importantly, users can see that SOMETHING is happening.  Its also nice to finish up with a parting word...


Application.Statusbar = "Calculation Complete."


Another really important use of the Application object is the WorkSheetFunction method.  This allows you to execute any function that would normally only work when placed directly in a worksheet cell.  For example, say you had a VBA process that needs to take an average of a bunch of cells.  You could loop through them and add each cell's value to a running total, then divide by the number of items, or you could use the built-in AVERAGE function, as in:


varResult = Application.WorksheetFunction.Average(rngRange)

I ofetn use this approach when trying to find the position of a value in a list, because the MATCH worksheet function has no equivalent in VBA.


intPos = Application.WorksheetFunction.Match(varVal, rngRange, 0)

What if you have written a custom function and you need to know what cell that function was entered into? This is important, for example, if the function must refer to other values on the same row or in the same column.  For this, we use Application.Caller, which returns a RANGE reference to the cell where the function was "called" from.


Function MyFunction(N as integer) as Integer
 'use whatever is in the Nth column of the row in which
 'this function is placed as an argument for another function
  intRow = Application.Caller.Row
  MyFunction = MyOtherFunction(Cells(intRow, N))
End function

Another very powerful method is Application.Run.  This allows you to specify the name of another macro as a text string, and execute it, along with up to 30 arguments.  Why do we need this?  Let's say we are working on a model that encodes a large set of business rules.  We might have dozens of macros that do specific things depending on what may be contained in the data.  Using the Run method, we can assemble the name of our macro in code based on changing data conditions, and then run the code.  I have used this in situations where macro names reside in a database, allowing me to change the behaviour of the application by modifying data.  This facility comes close to enabling Self-Modifying Code, which any old-school programmer will tell you is the holy grail of power in any language that allows it.

There are many many more powerful features available to the VBA programmer by way of the Application object.  Just open your Code Editor and type "Application.", and observe the miriad of methods and properties that the IDE exposes for you.  With a bit of experimentation I guarantee you will discover a way to do something you never thought possible in VBA.  Random code exploration is the gateway to advancing the art.

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

Excel in Front, part 1

This post is long overdue - I've been flat-out, coding a very interesting application for a manufacturing client, using Excel as the front end on a shared Access database.  The goal is to give a workgroup the ability to perform simultaneous edits to their work-in-progress tracking data, without any of the hassles, bugs, hideous file growth, sharing violations, lock-outs, etc. that you get with the built-in workbook Sharing mechanism.  There's a host of business logic involved, and they want the ability to design their own reports at will.

Again, I find myself reinventing the wheel.  "Why not develop your application in Access?" one might ask. My reasoning: Customer didn't have it, doesn't want it, can't support it, and more importantly, developing in Access is painful.  I prefer Excel.  The advantages weigh in: custom business logic is more easily coded in Excel.  It is.  And we needed in-grid editing, flexible data presentation, filtering, sorting, graphing... and whatever else we can dream up for versions 2 through N.  That's all in my Excel quiver.

"Oh, right. Excel lets you Link to a data source and populate tables..." yeah, I thought about it, but the users don't want to fart around with Data Connections, and my experience tells me it's a long road coding for those APIs. (if you know better, please tell me.)  And I wanted this to be an always-disconnected setup, where the workbook simply grabs data or sends updates to the db in quick bursts - no live links to network files that might be unavailable, renamed or changed.

The db was designed using an SQL Script to create the tables, populate the lookup data, and set up primary keys, defaults, and so on.  I ran the script to create the database in SQL Server, and then imported the tables into my .mdb file using Access.

I did not use the Access database design tools, though you certainly can.  I prefer to use a build script whenever designing a relational database; you drop and recreate the db often as you rework your design - that's Agile.  I also find that Access is a bit too user-friendly, in other words, not powerful enough.  I guess I can type SQL faster than I can ponder through menus.

In Excel, I set up a file-select control, and some text boxes for username and password.  The users on the network will each have a local copy of the application workbook - It's not linked, welded, cross-referenced or otherwise umbilical'd to any other file.  Can't you smell the freshness?  No pop-ups... ever.  As updates come out, they will be emailed to the users.  They will enter their credentials, file-pick the .mdb file, and work. That's Data Separation.

Here, again, I'm not tying into any built-in user management.  In a secure enterprise situation you probably have to set up database user accounts linked to network id's, but my client is looking for (and I strive to offer) the simplest solution wherever possible (SSWP), so we won't be contacting network support for domain credentials and such - the .mdb file is on a password-secured network drive.  Our application will maintain it's own user list... VBA code does the rest.

The project is still underway - next post I'll explain how I set up my object classes, control cell-editing, filter and sort, and conclusions: will it be another home run, or a teetering, reeking, house-of-cards? Stay tuned.