Working With Linked Tables In Access That Have A Password Applied To It

Working With Linked Tables In Access That Have A Password Applied To It

Working with linked tables in Access probably means you have engaged in the process of splitting your database between the tables which is deemed the back-end (BE) with the remaining objects making up the front-end (FE) database.

In some cases however, your BE database may have been protected with a basic encrypted password preventing general users any access directly or indirectly to it. If this password is modified, your linked tables will fail to open the tables via the FE database.

linked tables in access

To check what the current password is (and this is not a very secure system), you could load (unhide) a system table in the FE database and take a peak! Oops, this with reveal all!

Working With Linked Tables In Access That Have A Password Applied To It

Firstly, you need to unhide one of the system tables in Access and find the table called ‘MSysObjects’ which is a read only table and therefore cannot be edited (don’t even bother to try).

Now locate a linked ‘table’ record (which has a ‘Type’ value of 6) and the first field (column) called ‘Connect’ which will reveal a password if applied. It will look something like:

MS Access;PWD=benb;

As already mentioned, this cannot be edited but a standard encrypted password is not really a secure way to stop unauthorised users finding it – that’s for sure!

So what are your options and how do we manage protecting your database further?

Well, this simple answer is ‘NONE’ when applying the basic encrypted password for your linked tables in Access as this will never really secure (mask) the FE or BE and can easily be exposed as shown. If this is your strategy then know its limitations and live with it.

With earlier versions of MS Access (pre 2007), you had the option to invoke a better (workgroup) security password tool which was a separate system that sat alongside your databases but required some planning and setting up. The added downside was the handling with the later versions using the newer ACCDB file format where this tool had been dropped altogether in favour of the web based or SharePoint services which again are an external process to this application.

The only real solution that a developer would need to consider leans towards using Access VBA where various coded objects could manage, control and mask sensitive data like passwords and provide a mechanism to take over the linked table tool.

That was one reason why I designed a simple VBA tool called building your very own Access database security (Login User Form Utility) which can be built upon and extended to your exact requirements as I have left this open and supported with a manual guide too. It doesn’t include the ability to manage the Linked Table Manager utility but with some additional VBA code it could!

There you have it! Build your own or know it’s (but not your) limitations.

A very happy and prosperous new year to all!

This entry was posted in MS Access, Tables, Utilities and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.