Multiple Users Accessing Single Workbook - Best Practices? RRS feed

  • Question

  • Hello all,

    I am putting together a collaboration tool that works in the following manner.

    Users have the "client" workbook which has the VBA enabled application I have written which consists of a single user form and data fields that the user enters pertinent data into.  When they submit the new data set the application opens a remote "database" XLSX workbook, fills the workbook with the new data and assigns it an ID number.  Some other code on the client side takes that information and writes a custom KML balloon for use in a 3D earth viewer.

    Normally when I write a VBA application using the OLE it is only reading from the other document (or sending data to an Outlook mail message).  I have never designed an application that is intended to both read/write and be distributed to multiple users that could, in theory, try to execute commands against the "database" workbook at the same time.

    I've added code that shuts a user's application down if they try to run it while the "database" is already in use by another user, but this seems like something that would be frustrating if someone working in the database leaves it open and leaves the room.  So I've set the "database" workbook to "Shared" status.  This seems to work fine in testing.

    Does anyone have any experience designing applications that perform similar operations in a multi-user environment?  What advice could you lend to help keep my application running well?



    • Edited by Grasor Monday, April 4, 2016 9:48 PM
    Monday, April 4, 2016 9:47 PM