​MS Access - Best Economical Data Management Option

One of the largest misconceptions about Microsoft Access is that it’s expensive. Many people operate under the assumption that if they build a data management solution in Access, that every user will need a full version of Access installed on their machines. This is a wrong assumption.

​Microsoft provides a runtime version of Microsoft Access. It does not cost anything to download and install Access runtime, on user machines. If your organization builds a multi-user Access application, you would only need to pay for the full version of Access for administrator/developer machines.

Using Microsoft Access Runtime, on user machines, can save organizations a lot of money. In licensing fees, your organization can save an average of $100 per runtime install. Depending on how many machines have runtime installed, $100/machine, can add up to a lot of money.

Runtime has other advantages as well. Where runtime is installed, end users will be more restricted than administrators/developers who have the full version of Access. Following are some of the restrictions on users of Access runtime:

• Users will not have any access to source code. This protects your database source code is one very important reason to consider Access runtime.
• Users will not have direct access to any of the data tables. They will only have access to data through forms, queries and reports created by the programmer. This makes it easier to control what users see and what they are allowed to edit, and how they can manipulate data.

These two advantages make runtime a good choice, even if your organization does not have a high multi-user database count.

Creating a Runtime Version of Your Access Application
In order to create a runtime version of your Access application, you’ll want to take the following points into consideration.

1. Firstly, in order to effectively use runtime, it’s important that your application be a split database application. Split database applications store the actual data in a centralized backend database file. By storing data in its own file, it is then possible to have multiple frontend application files linked to the same data source. It is possible to store your data in its own Access table database, but most often split databases include a backend SQL database for data storage
2. Frontend Microsoft Access file that includes all the data entry forms, reports and queries used by end users.
3. The frontend file is linked to the data in the backend SQL Database. You can read more about setting up a split database in this LinkedIn article. Yes – Microsoft Access works in a Multi-User Environment
4. In addition to using a split database setup, you should also consider taking advantage of Access’ “Trusted Location” settings.Trusted locations are assigned to an Access file, to ensure that it can only be opened from specific file drives. This helps protect your runtime file from tampering. It makes it more difficult from someone to copy the file and change it back to a file that can be opened with a full version of Access.
5. You can read more about Trusted Locations at this link.
6. Once you have a split database, and assigned trusted location(s) to the file, you are in a better position to actually create a runtime file for end-users. Creating runtime files is actually a three-step process.The first step is making sure you retain a copy of your database in an .accdb format. This ensures that you have a “developer” copy available, in case you need to make future revisions.
7. Before actually creating your runtime file, you will want to save/publish your frontend file in an .accde compiled format. This action will protect all of your VBA source code. To save your file in an .accde format follow the instructions in this link.
8. Now that you’ve compiled your database, so that the VBA code is protected, you are in a position to convert that file to a runtime version. To do this you simply change the extension from ACCDE to ACCDR.
9. Now you are ready to roll out the .accdr file to a network folder, where all machines, with the runtime version of access, can open and use the database.

More Articles About Microsoft Access​​

• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.