Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 51

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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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

Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

Question has a verified solution.

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

This article is written by John Gates, CISSP. Gates, the SNUG President-Elect, currently holds the position of Manager of Information Systems at Lake Park High School in Roselle, Illinois.
If you're a modern-day technology professional, you may be wondering if certifications are really necessary. They are. Here's why.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

704 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