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

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
> 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!

billparsAuthor Commented:

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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

billparsAuthor Commented:

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,
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just to add another voice: I've done exactly what you mention (the ACCDB/ACCDE with a secured MDB backend), and it worked fine.
billparsAuthor Commented:

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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billparsAuthor Commented:

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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.