[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Database Password for a split database

Posted on 2006-06-01
Medium Priority
Last Modified: 2008-01-09
I would like to protect both the Front End (FE) and Back End (BE) of a split databse with the same Database password, in such a way that the user only has to enter the password once (when they open the FE). How can I do this?
Question by:Milewskp
LVL 14

Accepted Solution

bluelizard earned 2000 total points
ID: 16807706
as far as i know, normally the BE password you only need to enter at the time when you link the tables into the FE.  afterwards, the user only needs to enter the FE password.


Expert Comment

ID: 16808536
After setting up a form on the front end and linking it to the table on the back end you should go to the startup menu and choose to hide the tables at startup. That way then the data base is opened the user will not be able to see the back end tables. Ensure that your forms cannot be opened in design mode by setting this on the properties of the form. If you ever need a shortcut into the backend yoursel you can gain access to it by holding doen the shift key when clicking you Db Icon. If you are worried by this loop hole then you could as part of your start up, design a form that can only be accessed by you if you set yourself special login rights I.e.

Database Administrator > password = true startup form = My Administrator Form
Database Manager > Password = True > Startup form = Database Managers Form
Database User > Password = True > Startup Form = Users Form


Hope this helps


Author Comment

ID: 16811386
Hi Blue,
Yes, if I assign a password to the BE before I link to the FE, Access asks for the password when I link the tables, but never asks again.
More questions for you if you don't mind:
- If I then change the password of the BE, do I have to delete all of the linked tables and then relink them, or is there an easier way? (I tried using the linked table manager, but it responded with 'Not a Valid Password'.)
- Do you know where the FE stores the BE password? Is it easy for users to access?

Hi Malcolm,
- What form property do you set to ensure than forms can't be opened in design view?
- Can you explain how to set special login rights?
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

LVL 14

Expert Comment

ID: 16811689
>>- If I then change the password of the BE, do I have to delete all of the linked tables and then relink them, or is there an easier way? (I tried using the linked table manager, but it responded with 'Not a Valid Password'.)

i would also suggest you use the linked table manage, but be sure to check the "always prompt for new location" box. otherwise, the manager will only ask you for a new location if it can't find the old one, and in your case it *can* indeed still find the old one, but it uses the old password as well, which fails.  but if you check the box, it should in any case ask you for the "new" location (which is the same as the old one, anyway), but it should prompt you for the new password.  if this fails, rename the BE file and try again (if you want the BE to keep its old name, simple re-re-name it back afterwards and re-link the tables again).

>>- Do you know where the FE stores the BE password? Is it easy for users to access?
for linked tables, i don't know, hence i unfortunately can't tell you whether it can be read (or reverse-engineered) by users.  you might want to post anohter question for this one - i'm sure there are experts that know about it...
if you open a recordset in a table of a non-linked mdb-file using the "openrecordset" command in VB (which i guess you're not), then of course you have to add the password to the command line, and anyone who can see the code can see the password as well, but you can either convert the DB to an mde file or add a password for the VBA code.

LVL 58

Expert Comment

ID: 16825691
You get this one for free (no points for me please). If tblSecret is a linked table from a database with a password, type this in the immediate pane:

    ? CurrentDb.TableDefs("tblSecret").Connect

The full connect string will look like this:

    MS Access;PWD=guesswhat;DATABASE=<full path and name of mdb>

It is copied to the description of the table visible in design view, but without the PWD portion. So that users can not read the password unless you give them access to a VB window, even if they are allowed to open tables in design view.


Author Comment

ID: 16897732
Hi All,
I tried the Linked Table Manager again, this time with the "always prompt for new location" box checked. It's still giving me the  'Not a Valid Password' message (I had to unlink all of the tables and relink them). Am I doing something worng, is there no way to get the Linked Table Manager to prompt me for the new password?

Thanks for the tip.
LVL 14

Expert Comment

ID: 16900024
what i realized before is that when the old and the new database file have the same name (but are in different dirs, of course), access somehow messes is it all up (doesn't seem to recognize that the new file is actually a different one).  what i did to solve this was i changed the new database file's name, then re-linked, then changed the name back to what i really wanted it to be, and re-linked again.  maybe this solves your problem.


Author Comment

ID: 16901778
Hi Blue,
This doesn't seem to work for me. If I change the password of the BE, the only way I can get it linked tables to work with the FE again is to delete them and then relink them.

Even if I change the name of the BE, the Linked Table Manager gives me a 'Not a Valid Password" message when I try to relinkthe tables.. Here are the steps I followed:
- Split Database
- Change file name of BE and set database password of BE to 'pass1'.
- Use Linked Table manager to try to relink tables to BE; "always prompt for new location" checked. Won't work - I get a
'Not a Valid Password" message.
- Change BE file name back to original name.
- Delete all linked tables from FE.
- Relink tabels to BE. Acces asks for and accepts new password. Works fine.

Any ideas?
LVL 14

Expert Comment

ID: 16902166
ok...... if you don't mind using code, you can try these lines; it's what i'm using to relink tables:

Dim db As Database
Dim tempTable As TableDef
Dim tableName As String
Dim pathName As String
Dim pw as String

Set db = CurrentDb()
pathName = "C:\the\new\database.mdb"
pw = "MyPassword"    ' <--- use your password here

tableName = "TableToBeLinked"    ' <--- use your table name here
Set tempTable = db.TableDefs(tableName)
If Len(tempTable.Connect) > 0 Then  ' checks if table is a linked table at all
   tempTable.Connect = ";DATABASE=" & pathName & ";pwd=" & pw
End If


Author Comment

ID: 16942905
That worked great. Thanks for all you help. Full points and Grade A to you.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question