Database Password for a split database

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?
Who is Participating?
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.

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

MilewskpAuthor Commented:
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

>>- 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.

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.

MilewskpAuthor Commented:
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.
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.

MilewskpAuthor Commented:
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?
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

MilewskpAuthor Commented:
That worked great. Thanks for all you help. Full points and Grade A to you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.