Go Premium for a chance to win a PS4. Enter to Win


Create links in ACCDB frontend to tables in secured MDB backend?

Posted on 2014-11-07
Medium Priority
Last Modified: 2014-11-10
I have an MDB with tables secured by an MDW.  Management decrees that it cannot migrate to SQL Server any time soon.  In the meantime, I must build an Access frontend to provide a user interface.

I could build the frontend as an MDB, and then compile it to MDE.  I would rather build it as an ACCDB, and then compile it to ACCDE, in anticipation of swapping out the MDB backend, for a SQL Server backend, down the road.

To do that, however, I would need to create links in an ACCDB/ACCDE frontend to secured tables in an MDB backend.

I am able to open an ACCDB through a shortcut whose "/wrkgrp" switch points to the MDW that secures the MDB.  This forces a logon, through the MDW, to open the ACCDB.

If the user has Open/Read permission for the MDB, and Read Design permission for its tables, then they can link to them from the ACCDB.  They also can read, update, insert, and/or delete MDB data through ACCDB links, depending on how I set table permissions in the MDB.

Conversely, if I open the ACCDB without a shortcut through the MDW, then no logon occurs, and I cannot access MDB data through ACCDB links.

(Compiling the ACCDB results in an ACCDE that behaves identically in all of these respects.)

This seems to achieve my goal, but I cannot find documentation to support this approach.  Am I starting down a path with known dangers, or is this common and acceptable practice?
Question by:billpars
LVL 52

Expert Comment

by:Gustav Brock
ID: 40430123
> is this common and acceptable practice?

It may not be common, but if you have tested it and it works reliably, I cannot see any reason to regard it as not acceptable. I would even say thanks for the tip!


Author Comment

ID: 40430490

Thanks for the reply.

That you have never heard of this approach is not reassuring.

A cursory proof of concept with Access 2010 appears to support it, but I am wary of going where no man has gone before.

My confidence would increase if a developer:

Had personally implemented this architecture, and could vouch for its reliability


Could point to documentation that supports it

Will Access 2013 support it?  Do I lack the insight to spot an obvious trap that lies ahead?  Am I overlooking any unanticipated "gotchas"?

Perhaps other posters will weigh in.
LVL 58
ID: 40431922
Yes,  it's a valid approach and one I've used in the past although slightly differently.   I had a "development". MDW,  which allowed full access.   I'd then hand the db out without this MDW.

The default MDW would be used, which gave no table permissions.  All data access was done via query and "run with owner permissions".  This allows them to access the data in the app,  but since they were not the owner of the tables,  they could do nothing with them outside of the app.

This approach was documented in the security FAQ many years ago.  Still have a copy that I can post tomorrow.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

ID: 40431949

Thanks for the timely review and response.

Just to be clear, beyond the twist you described from your own experience (i.e. distributing an ACCDB frontend and secured MDB backend without an MDW), you have knowledge of the specific architecture I describe (i.e. distributing an ACCDB frontend and secured MDB backend with an MDW) in the real world?

I would love to see your copy of the security FAQ that document this approach.  Please post it, if possible.  Are you aware of any other sources of documentation on this (e.g. Microsoft)?

Can you think of any reasons why the approach might fail in Access versions beyond 2010?

Thanks again,
LVL 58
ID: 40432503

  The ACCDB doesn't really add anything.  JET/ACE work group security is still in place with a ACCDB and "on".   When Microsoft says it "stripped out" security, all they did was remove the ACL (access control list) from the DB.  There is still a work group file being used (SYSTEM1.MDW) and it's the "default" one, the same as when you had a MDB.  That's why your setup works.  

The customized MDW you have now has the same default accounts and groups SYSTEM1.MDW does, so everything in the ACCDB works, but users are blocked in your MDB because you've changed the permissions on those objects.

Your not going to find direct documentation on this type of setup because it doesn't exist as work group security was officially "removed"  for ACE.  There's also the fact that work group security has not changed at all since day 1.  It still works exactly as it did then, even with ACE DB's

LVL 85
ID: 40432964
Just to add another voice: I've done exactly what you mention (the ACCDB/ACCDE with a secured MDB backend), and it worked fine.

Author Comment

ID: 40433002

Thanks for indulging me.

I understand the ACCDB really does not buy me anything -- other than saving me the trouble of converting frontend source code, from MDB to ACCDB, when the backend migrates to SQL Server.

I also understand that nothing has changed, vis-a-vis the custom MDW securing the custom MDB, even though Access 2010 (instead of Access 2003) now manipulates them.

I am just not used to thinking of *unsecured* frontends, so I need to confirm the following:

1. Because the ACCDB includes table links to a secured MDB, the ACCDB will not open unless a user (with Open/Read permission for the MDB, and Read Design permission for its tables) is logged into the custom MDW.

2. Using my shortcut, the user is logging into the custom MDW, and *simultaneously* opening the ACCDB (i.e. they cannot do the one without doing the other).

3. When the user closes the ACCDB session, they automatically log out of the custom MDW (i.e. exactly as if the ACCDB were an MDB).

Do I correctly grasp all three of these?  If so, then I am good to go -- assuming this behavior is identical in Access 2013 (or its next iteration).

Thanks again,

EDIT: Scott, I just now saw your reply.  Thanks!  I appreciate the insight.
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40433215

 Yes, that is pretty spot on except for a couple of points:
 For #1, it depends on what you mean by "open".

 From a technical standpoint, the ACCDB itself does open with or without the custom MDW and a user will have access to forms, reports, etc.   But unless they are using the custom MDW, they would not be able to access tables in the BE MDB.  Thus your app functionally might not "open" because your trying to read the BE tables.

 For #2, as noted above, the ACCDB would open even with a standard work group file, Whether or not they can do anything without having access to the BE tables is another story.

 For #3....don't think of it as a log in/out situation.  It's more a matter simply of which MDW is used when attempting to open the DB in question.  The actual log in to JET happens after that.

  By that, I mean you can set a default MDW in the registry (there are also user profiles, but no one has used them since A95 and I'm not even sure they would work anymore), which if you don't specify the command line switch is the one that gets used.   That registry entry can be switched.

 So I could set that to the custom MDW and drop the use of the command line switch.   That would work for everything *unless* you messed with the default accounts and groups in that custom MDW.  If you did, then access to all the ACCDB's would break.   That's what you do when you secure a DB.

Many people have done this in the past ("joined" a work group) that they customized and then break access to some DB's not realizing that if they don't use the command line switch, the work group they joined to is used for everything by default.

But overall, yes, your understanding the situation correctly and it's going to work like you expect.


Author Closing Comment

ID: 40433358

Thanks for the clarification.

I was mistaken to presume the ACCDB would not open by simply double clicking it.  I assumed the custom permissions (Open/Read for the MDB, and Read Design for its tables) were required for the Navigation Pane even to *display* links to secured tables in the backend, so thanks for setting me straight.

It also was inarticulate for me to think of logging into (and out of) an MDW, vs. simply using it to open a particular MBD/MDE or ACCDB/ACCDE.

Thanks again for a great job of patiently explaining complex ideas.


PS: I will assume you have no reason to believe the Microsoft requirement for opening database files through MDWs will disappear in any future version of Access -- unless you say otherwise.
LVL 58
ID: 40433434
<<PS: I will assume you have no reason to believe the Microsoft requirement for opening database files through MDWs will disappear in any future version of Access -- unless you say otherwise. >>

 Absolutely none.  Any changes on the desktop side are dead.  I don't believe JET or ACE will change one iota from this point forward.  The last changes made to ACE were done in 2007, and those were not all that significant (attachment data type and MVF's).  Everything new is being done on the web side.    

 Then consider that in regards to work group security and DB rights, it hasn't changed at all since version 1.0.

 I can't see any reason why they would change it now.


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Ransomware - Defeated! Client opened the wrong email and was attacked by Ransomware. I was able to use file recovery utilities to find shadow copies of the encrypted files and make a complete recovery.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

824 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