Access Database Open File Hyperlink Without Opening App Again

A common question raised by many Admission users creating a multi-user database is how best to deliver your solution so that it is secure, fast, and upgradeable.  Access database deployment within your organization, or to your cease customers, should be well thought out in advance to avoid functioning problems, reliability problems, or potentially significant security or data loss issues.  This article discusses fundamental best practices to deploy your Access database.

Splitting Your Database

This item is a must to brand upgrades to your end users easier.  A split database in MS Admission but means you volition have i file that contains your tables merely.  This is sometimes chosen a "back end" or "data file."  For simplicity, we'll refer to it as your Access Data File.  The 2d file is what is usually chosen your "front end end" database.  There is really no data stored in the file, it but contains your forms, queries, reports, macros, VB modules, and links to your Access Data File.  The advantages are:

Shop your data file anywhere

With your data separated from the user interface (forms/queries/reports) and business concern logic (VB/macros), you can go along the data anywhere that your users are able to proceeds admission.  About commonly, on a shared network bulldoze or even a shared folder on your own automobile (every bit long as your car is on when the file is needed by other users!).  You can back up your Access Data File whenever you need to, and relocate the file if needed.  You will need to make sure the front-stop database file(due south) always know where the Admission Information File resides.  If information technology moves, you can manually restore the links to the new location, do so programmatically, or apply a tool like UI Builder for Admission to help prompt the user to find the new location.

Upgrade Your Front-End Databases without Information Loss

Storing your forms/reports/macros/VB in the same file with your tables also presents a trouble when yous need to make updates.  Especially if you program to deliver your database to users at a unlike location where you lot don't have straight admission to the database file.  Sending users a new version won't work, every bit the data would be overwritten, or yous would have to ask the users to import their existing information tables.  With a split Access database configuration, y'all simply send them a new version of your front-end file and their data remains intact.

Avoid Multi-User Collisions

If you shop all your objects in ane file, including your tables, Admission will usually have difficulty when multiple users endeavour to open the aforementioned database file.  While Access does have record-level locking, you may yet receive errors that another user is currently in the database and you cannot make changes.  To avert this, you provide each stop user with their own front end-end database file, each linked to the aforementioned Access Data File.

Splitting your database is quite easy with the built-in Access tool. Exist sure to make a copy of your database earlier you begin only to be safe. In Access 2000/2003, select "Tools>>Database Tools..." from the bill of fare, then "Database Splitter..." In Access 2007/2010, select the Database Tools ribbon tab, then "Move Data" and then "Access."  The sorcerer will take you through the procedure of selecting the tables to move, and what the your information file volition be named.  It may seem picayune, simply we recommend calculation "_datafile" to the end of the proper noun then you are sure you know this is the data file, not a front end-terminate your users can delete.

Front-End Preparation

When preparing your front-end database file for commitment to users, you volition want to consider what you want to allow your users to be able to practise with your front-end file.  If yous want to allow them to make design changes, open up tables directly, edit or view VB source code, and so you can deliver the file "as is."  More commonly, you will await users to have much more limited capability to just view the forms and reports as you lot accept prepare them, without existence able to view or alter the class/study/lawmaking design.  The easiest way to practise so is to compile Access into an MDE or ACCDE file format.  A standard Access database file is in a "semi-compiled" state which ways Admission does some level of compilation of whatever VB code y'all have created on the fly.  Compiling your Access database just ways that a new file is created with the extension ending in "e" instead of "b," and the objects are fully compiled such that a user cannot open up a form or study'south design view, nor can they inspect your VB lawmaking.  This protects your intellectual property, prevents many types of tampering, and likewise makes your database load (but slightly) faster.  A few other points to assistance with front-end preparation:

Navigation and Forms

Make sure your database includes clear navigation, and nosotros recommend you as well utilize our 10 Tips for a More Dynamic Access Form to add polish and brand sure your users are more productive.

Remove Unecessary Menus and Options

In near cases, you'll want to hide the Admission database window (2000/2003) or Access Navigation Pane (2007/2010) and provide your ain Access menus.  To hide the database window in 2000 and 2003, select "Tools>>Startup..." from the menu, and uncheck the "Display Database Window" choice.  In Access 2007 and 2010, select the Role icon, then the Access Options button.  In the dialog, select "Electric current Database" and uncheck the "Brandish Navigation Pane" choice.  Lastly, all versions allow you to disable Total Menus (the ribbon in Access 2007/2010) and right-click Shortcut Menus if you want to further limit user'south options.

Security Measures

Compiling your front-terminate into an ACCDE or MDE format helps protect some of your intellectual property, but if yous are planning to store sensitive information in your Access project, we recommend you implement the necessary security measures to exist certain your information is protected to the extent needed.  In some cases, that may merely be hiding the navigation pane to minimize the possibility that a user can observe your tables without Admission know-how.  In more extreme cases, it may mean password protecting and/or encrypting the front-terminate and data file.

Access Workgroup Security

Considering the Access 2007 file format does not support Workgroup Security, nosotros recommend staying away from this approach since it will limit your ability to move to the 2007 format when it becomes advantageous or even required past future versions of Access.

Tabular array Security

Ultimately, a determined user will likely be able to get to the data if they take enough Access know-how.  A linked tabular array in your front-end is technically a "trusted connection" where if the user tin open the database window or navigation pane, they volition be able to view information in the linked tables even if the data file is password protected.  If you lot hide the database window/navigation pane, disable full menus, and too disable the "Access Special Keys" option, yous have made a big step to preventing a user from existence able to get to the tables.

Navigation Security

Even later you secure your database tables, you lot may decide you want to limit what information some users view in your forms and reports, and what forms and reports they tin can even launch.  The Enterprise Edition of UI Builder offers the ability to create user-level menus in Access using the user's windows login or a custom bill of fare prompt, or you can create your own custom menus and routines to log users in and nowadays dissimilar options to them.  At a form level, you may desire to consider whether you will obscure certain information based on the user, such as National ID or Tax ID.

Information File Locations and Performance

Your Access Information file (dorsum terminate) should exist located on a network folder where all users can open files in the directory.  You can likewise use this mechanism to limit who can access your database.  For instance, you may fix up your file server folder permissions to just allow the x users yous desire to exist able to open up your database.  If you are delivering a solution to your customer, you lot may non have control over where the information file resides, merely you can provide guidance on setting upward network folder security, and making sure the folder is included in nightly backups.

When deciding where to store the Information File, it is also important to consider the file server age, hardware, and network latency.  The last thing you want is a client or user lament that your awarding is painfully slow.  In some cases, it may just be a boring server.  Nosotros also recommend creating a persistent connection to a table in the Information File for each front-finish when it is open.  Simply put, Access will attempt to lock and unlock the Data File each time y'all shut a form and open another.  By creating a persistent connection, Access keeps the Information File open for the entire time the front-end is open.  To exercise this, you tin can either launch a hidden form at startup that uses a table with a few records in the Data File, or open up a recordset to a table in the Information File, and go along it open until the front-end is closed.  If y'all are using UI Architect, this is handled for you when UI Builder starts up.  UI Architect creates a connection to the event log table (tblEventLog) that remains open until UI Builder closes.

If your solution will be used by a single finish user, and yous store their Data File on their machine, proceed in mind that it will be easiest to take a consistent directory to shop the Data File on your auto (for evolution) and the end user'due south (for production).  If you both use the same path on your local PCs, you won't demand to remap the linked tables when your end user installs your front end end database file.

Front-End Installation

The front-end database files should be installed on each individual'due south PC.  It will load faster, and you minimize the risk that 2 users open the same forepart-finish on a shared network binder (causing the Access multi-user error messages nosotros covered previously).  Importantly, your end users will not demand the full version of Access unless yous are assuasive them to make design changes to your forms/reports/code.  You can salvage a bang-up deal in MS Admission licenses by leveraging the costless Access Runtime edition.  When you install the forepart-end, you tin apply the free Admission 2007 developer extensions from Microsoft to create an installation file, or us a free installer like Avant-garde Installer (what we apply and love).  Make sure your chief front-end class or an piece of cake-to-admission form has the version clearly labeled.  That way if you need to troubleshoot, you or the user tin speedily tell what version they are running.

It's also important to note that the Plan Files directory in Windows Vista and Windows vii is no longer considered a "read/write" directory for virtually purposes.  Consequently, y'all'll want to install the front-stop file in one of the new and accepted places to installed read/write programs, such as:

• The user's Documents binder
• C:\Users\<<USERNAME>>\AppData\Roaming\<<YOURFOLDER>>\

Upgrading Your Front-End

When the time comes to upgrade your front end-stop file with new changes, all you need to do is release an update to the end users with the front-end file simply.  They simply need to run your provided installation file, or replace their current copy of the ACCDE/MDE front end file yous initially provided.

Changing Your Data File

If you have directly access to the Data File, making changes to the tables is usually easy.  Your biggest task will to be to make certain all users have exited their forepart-end databases so that yous take full read/write access to the tabular array design.

If you are delivering a packaged solution to customers, changing their Data File tin be a flake more involved.  A few approaches to consider:

Future Use Fields

We use these quite a bit in our products. They aren't fool proof, merely they tin can aid you minimize table changes when y'all want to add together a few new features that require more fields in an existing tables.  Essentially, you create a ready of fields in each table named "Future-Employ-1" "Future-Utilize-two" and so on.  You may desire to create 4-five that are Text(255), several more that are Double numeric fields, and a few Date fields.  When you determine y'all need a new text field to store something you lot didn't know your customers/users wanted to rail, you've got a field available to practise so.  The grade will have a caption similar "Client Segment" while in the table (which your users won't come across) you are making use of "Future-Use-ane."  Avert calculation as well many future use fields, every bit it can (minimally) increase the space each row in the table will consume on deejay.  But if you take 200,000 records, an extra 2kb per tape can indeed make a difference!

Upgrade Lawmaking

Another option is to accept a flag ready in the forepart-end file to run upgrade code when you deliver a new version of your forepart-finish file to users. When your database opens, if the flag equals true, your awarding attempts to open up up the Data File (using DAO or ADO) and make the changes to the table(s).  Afterwards it is washed, it sets the flag to false.  This does require solid experience with ADO or DAO, and we won't mail service any lawmaking here.  Merely information technology is entirely feasible to exercise.

Register for our eBook - 'Using Microsoft Access For Greater Efficiency' where yous can become a comprehensive view of how Microsoft Access can assist yous exist more than productive, when to choose Access as a solution, best practices, and where to get help online.

flahertybresteers.blogspot.com

Source: https://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm

Share this post