Best Practice in Backend Design with Excel RRS feed

  • Question

  • Situation:  

    • I have a frontend Excel Workbook that calls an Excel Addin.  
    • The frontend contains the user selections and the backend addin contains all the reference data tables required to populate form controls etc, as well as the VBA that does most of the work.
    • The reference data is being updated more frequently than expected and I'm thinking about pulling the reference tables out of the Addin workbook to facilitate updating the data either through web queries or otherwise.


    • Are there best practices on what application to use when structuring backend datatables?  I've considered using another Excel Workbook but was wondering whether text files are more the norm (or any other application for that matter).  Lastly, was also thinking about connecting the backend data tables to a Sharepoint list to simplify how new data is pushed/pulled to/from the end user--not sure if this would affect your response.
    Monday, May 19, 2014 4:20 PM

All replies

  • On a network, I wouldn't use anything other than a good database. Access if you have nothing else, or start with the free SQL Express. Read write to databases is easy with OLEDB and your data is now completely separate from your application which is good practice.

    Personally I wouldn't use SharePoint, usually too slow in my experience (unless hosted on your local network).

    Rod Gill
    Author of the one and only Project VBA Book

    Tuesday, May 20, 2014 8:38 AM
  • I developed an addin similar to the one you describe.  The addin had to work when the PC was not connected to the network (i.e. airplane).  I wanted all the data to be reliably backed up.  I had the addin operate in 2 modes.  In development mode it would connect to an Oracle DB.  IT backed up Oracle.  In production mode it connected to an Access DB.  When I did a production release I copied the Oracle DB to a fresh Access DB.  For the most part Oracle and Access work the same.  Access is fast.  I tried SQL Express and it works OK.  I also tried SQLite and it works OK but complex queries are limited.  I used Inno to do the release.
    Tuesday, May 20, 2014 12:25 PM