Search code examples
ms-accessdeploymentvbams-access-2007ms-access-2010

Options to Deploy MS Access Database Application for Data Collection

I am building an Access Database application in MS Access 2007 which is essentially a tool for data collection which I will need to distribute to various sites to be filled out. After the sites fill out all the necessary records, they will return the tool and I will need to merge all the data from the various sites into a single database for analysis. I have 2 tables and 3 forms with a bunch of custom VBA code behind for data validation, cleaning and flow.

I have a Summary form that shows all the records currently entered. Users can then Add a record using a button on the Summary form which launches a data entry form (let's call it Data Entry Form #1). From Data Entry Form #1, there is another form that can be launched (Data Entry Form #2) for entering child records about the record being filled out on Data Entry Form #1. There is referential integrity enforced at the table level.

The flow from the Summary Form to Data Entry Form #1 and Data Entry Form #2 is important for the integrity of the data. I have dictated this flow explicitly in VBA and will instruct users to always begin at the Summary Form.

After that lengthy background, my question.....

What are my different options, and the relative advantages/disadvantages for the options for deploying this application to my various sites. My basic requirements are:

  • Ideally users would never see the tables in which the data is being stored in.
  • The Summary form would launch when opened and they could not open any other form directly.
  • Closing the Summary form would close the application.
  • Since there is a bunch of VBA code dictating important functionality, if they did not accept the Security Warning, they would not be able to access any of the forms or use the tool
  • I can easily script the extraction/export of the data from the two tables for each tool

I am vaguely aware of the following options: - Distribute the full ACCDB file to the sites - Create and distribute an AACDE file to the sites - Use the Access Developer Extensions to "package" the application - create and EXE file?

I have also read that if users do not have Access 2007 or later, that they can download the MS Access Runtime Services and be able to use my application without having to buy/install a full version of MS Access. Can someone confirm this? Does this apply to all of the above (ACCDB, ACCDE, EXE) Is there any functionality that would not be available to them from a strictly data entry role?

Thanks!

Solution

  • You should be able to do most of this with options set within access, plus some code;

    1. Create an accde;
    2. Using that accde, in the options, untick display navigation pane (or something like that); There should be an option to disable the shift key as well.
    3. Set startform to the summary form

    4. Closing the summary form closes the application: In design view of the summary form (in the accdb, before you do the rest of this), create a form_unload event; In this event put

      DoCmd.Quit

    More of an issue might be whether or not all the sites have the necessary components of ms office to run access 2007, or if you need to provide an access 2007 runtime as well, but I'm not going there. If you need to do this, you'd best ask another question or go hunting for an existing answer.

    Hope this helps