how to detach an mdb from an mdw file?

[Bob]

I'm helping assess some enhancements needed for an Access/SQL Server
application. The Access part has a set of client facing forms and reports,
and pulls data from the SQL Server. What I'm not familiar with is the user
level security in Access. The mdb file looks for the mdw file for user
security info. How can I break this authentication link so the mdb file can
be opened without any security restrictions. I have the admin pwd.

I'm not even sure if the above would solve my real problem. When the
application runs on the client network (the mdw file on a network drive with
user's domain account info), using the admin pwd can gain exclusive access
to the mdb file, so the macros/VBA codes can be changed. However, when I
run them on my local PC (after adding my local login to mdw using
wkrgadm.exe so it would prompt me to login) using the admin pwd, it keeps
saying "You do not have exclusive access to the database at this time..." in
a popup message box when I try to edit any VBA code. Autoexec macro doesn't
show up in the Macro list either. I tried holding Shift key when opening
the mbd file but no help. That's why I think it may have something to do
with the security, but if I'm wrong, I'd appreciate any suggestions.

Thanks
Bob

 

[Joan Wild]

I'm helping assess some enhancements needed for an Access/SQL Server
application. The Access part has a set of client facing forms and
reports, and pulls data from the SQL Server. What I'm not familiar
with is the user level security in Access. The mdb file looks for
the mdw file for user security info.

Not quite. The mdb file doesn't, the Access session does. Every time you
open Access a mdw file is used. There is a system.mdw that ships with
Access and is used by default (it silently logs you in as 'Admin' user).

How can I break this
authentication link so the mdb file can be opened without any
security restrictions. I have the admin pwd.

You need to log in as a member of the Admins group. Give full permissions
to every object to the Users Group. Quit Access; ensure you are joined to
the system.mdw workgroup (use the workgroup administrator to do this). Open
Access, create a new mdb (you should get no login), and import all the
objects from your mdb. This new mdb will be unsecured.

I'm not even sure if the above would solve my real problem. When the
application runs on the client network (the mdw file on a network
drive with user's domain account info), using the admin pwd can gain
exclusive access to the mdb file, so the macros/VBA codes can be
changed. However, when I run them on my local PC (after adding my
local login to mdw using wkrgadm.exe so it would prompt me to login)
using the admin pwd, it keeps saying "You do not have exclusive
access to the database at this time..." in a popup message box when I
try to edit any VBA code. Autoexec macro doesn't show up in the
Macro list either. I tried holding Shift key when opening the mbd
file but no help. That's why I think it may have something to do
with the security, but if I'm wrong, I'd appreciate any suggestions.

The Admin user should not have any permissions to anything in a properly
secured database. When you say 'admin', do you mean the built-in Admin
user, or a user you created for administrating the database?

Just adding that user to your local mdw, wouldn't necessarily get you the
authentication to make changes.

However the message 'you do not have exclusive access...' is a new
requirement starting with version 2000. You cannot modify the design of
anything unless you have the mdb opened exclusively. You should give each
user a copy of the frontend on their local computer; they shouldn't be
running the same frontend on the server.

 

[Bob]

Hi Joan:

Thanks a lot for your detailed reply. I was hoping time would solve the
problem but nope, I was asked about this again.

So I tried you suggestion to create a new MDB file and import the objects.
I was able to import the tables, queries, and forms (that show up in the
Forms category) without problem. Also I exported the VBA modules and class
modules from the original MDB file and imported successfully. However,
there are a few items that show up under Microsoft Access Class Objects in
the VB Editor, with names starting with Form_ or Report_, that I couldn't
import. It gives the messages "The module name 'Form_mytest' is invalid."
I think these are Forms objects, but they don't show up under the Forms
category in Access main window.

The actual deployment of this app is exactly what you said, every user has a
copy of the Access DB file on their local PC. For me, I'm trying to modify
certain things in it so I need exclusive access to it.

I tried the build "admin" account, and also the documented admin account in
the MDW file that accompanies the MDB file, which is "EFANAdmin", the same
result, I can't modify anything because I can't open it exclusively.

One thing mentioned in the document of this app is that all these are used
in a Windows domain environment. That is, the client user is logged into
the domain, and the MDW file is on a server on the same domain, and SQL
Server is on another server on the same domain. I'm wondering if the fact
that there is no way for me to open this thing exclusively is because I'm
not on the same domain? As a matter of fact, I'm not on any domain at all.

Anyway, I have printed out 30+ pages of Microsoft documentation on Access
Security and will read through it this weekend.

Thanks a lot for the help.
Bob

 

[Bob]

Hi Joan:

Thanks a lot for your detailed reply. I was hoping time would solve the
problem but nope, I was asked about this again.

So I tried you suggestion to create a new MDB file and import the objects.
I was able to import the tables, queries, and forms (that show up in the
Forms category) without problem. Also I exported the VBA modules and class
modules from the original MDB file and imported successfully. However,
there are a few items that show up under Microsoft Access Class Objects in
the VB Editor, with names starting with Form_ or Report_, that I couldn't
import. It gives the messages "The module name 'Form_mytest' is invalid."
I think these are Forms objects, but they don't show up under the Forms
category in Access main window.

The actual deployment of this app is exactly what you said, every user has a
copy of the Access DB file on their local PC. For me, I'm trying to modify
certain things in it so I need exclusive access to it.

I tried the build "admin" account, and also the documented admin account in
the MDW file that accompanies the MDB file, which is "EFANAdmin", the same
result, I can't modify anything because I can't open it exclusively.

One thing mentioned in the document of this app is that all these are used
in a Windows domain environment. That is, the client user is logged into
the domain, and the MDW file is on a server on the same domain, and SQL
Server is on another server on the same domain. I'm wondering if the fact
that there is no way for me to open this thing exclusively is because I'm
not on the same domain? As a matter of fact, I'm not on any domain at all.

Anyway, I have printed out 30+ pages of Microsoft documentation on Access
Security and will read through it this weekend.

Thanks a lot for the help.
Bob

 

[Bob]

Hi Joan:

Thanks a lot for your detailed reply. I was hoping time would solve the
problem but nope, I was asked about this again.

So I tried you suggestion to create a new MDB file and import the objects.
I was able to import the tables, queries, and forms (that show up in the
Forms category) without problem. Also I exported the VBA modules and class
modules from the original MDB file and imported successfully. However,
there are a few items that show up under Microsoft Access Class Objects in
the VB Editor, with names starting with Form_ or Report_, that I couldn't
import. It gives the messages "The module name 'Form_mytest' is invalid."
I think these are Forms objects, but they don't show up under the Forms
category in Access main window.

The actual deployment of this app is exactly what you said, every user has a
copy of the Access DB file on their local PC. For me, I'm trying to modify
certain things in it so I need exclusive access to it.

I tried the build "admin" account, and also the documented admin account in
the MDW file that accompanies the MDB file, which is "EFANAdmin", the same
result, I can't modify anything because I can't open it exclusively.

One thing mentioned in the document of this app is that all these are used
in a Windows domain environment. That is, the client user is logged into
the domain, and the MDW file is on a server on the same domain, and SQL
Server is on another server on the same domain. I'm wondering if the fact
that there is no way for me to open this thing exclusively is because I'm
not on the same domain? As a matter of fact, I'm not on any domain at all.

Anyway, I have printed out 30+ pages of Microsoft documentation on Access
Security and will read through it this weekend.

Thanks a lot for the help.
Bob

 

[Bob]

Hi Joan:

Thanks a lot for your detailed reply. I was hoping time would solve the
problem but nope, I was asked about this again.

So I tried you suggestion to create a new MDB file and import the objects.
I was able to import the tables, queries, and forms (that show up in the
Forms category) without problem. Also I exported the VBA modules and class
modules from the original MDB file and imported successfully. However,
there are a few items that show up under Microsoft Access Class Objects in
the VB Editor, with names starting with Form_ or Report_, that I couldn't
import. It gives the messages "The module name 'Form_mytest' is invalid."
I think these are Forms objects, but they don't show up under the Forms
category in Access main window.

The actual deployment of this app is exactly what you said, every user has a
copy of the Access DB file on their local PC. For me, I'm trying to modify
certain things in it so I need exclusive access to it.

I tried the build "admin" account, and also the documented admin account in
the MDW file that accompanies the MDB file, which is "EFANAdmin", the same
result, I can't modify anything because I can't open it exclusively.

One thing mentioned in the document of this app is that all these are used
in a Windows domain environment. That is, the client user is logged into
the domain, and the MDW file is on a server on the same domain, and SQL
Server is on another server on the same domain. I'm wondering if the fact
that there is no way for me to open this thing exclusively is because I'm
not on the same domain? As a matter of fact, I'm not on any domain at all.

Anyway, I have printed out 30+ pages of Microsoft documentation on Access
Security and will read through it this weekend.

Thanks a lot for the help.
Bob

 

[Bob]

Hi Joan:

Thanks a lot for your detailed reply. I was hoping time would solve the
problem but nope, I was asked about this again.

So I tried you suggestion to create a new MDB file and import the objects.
I was able to import the tables, queries, and forms (that show up in the
Forms category) without problem. Also I exported the VBA modules and class
modules from the original MDB file and imported successfully. However,
there are a few items that show up under Microsoft Access Class Objects in
the VB Editor, with names starting with Form_ or Report_, that I couldn't
import. It gives the messages "The module name 'Form_mytest' is invalid."
I think these are Forms objects, but they don't show up under the Forms
category in Access main window.

The actual deployment of this app is exactly what you said, every user has a
copy of the Access DB file on their local PC. For me, I'm trying to modify
certain things in it so I need exclusive access to it.

I tried the build "admin" account, and also the documented admin account in
the MDW file that accompanies the MDB file, which is "EFANAdmin", the same
result, I can't modify anything because I can't open it exclusively.

One thing mentioned in the document of this app is that all these are used
in a Windows domain environment. That is, the client user is logged into
the domain, and the MDW file is on a server on the same domain, and SQL
Server is on another server on the same domain. I'm wondering if the fact
that there is no way for me to open this thing exclusively is because I'm
not on the same domain? As a matter of fact, I'm not on any domain at all.

Anyway, I have printed out 30+ pages of Microsoft documentation on Access
Security and will read through it this weekend.

Thanks a lot for the help.
Bob

 

[Bob]

Hi Joan:

Thanks a lot for your detailed reply. I was hoping time would solve the
problem but nope, I was asked about this again.

So I tried you suggestion to create a new MDB file and import the objects.
I was able to import the tables, queries, and forms (that show up in the
Forms category) without problem. Also I exported the VBA modules and class
modules from the original MDB file and imported successfully. However,
there are a few items that show up under Microsoft Access Class Objects in
the VB Editor, with names starting with Form_ or Report_, that I couldn't
import. It gives the messages "The module name 'Form_mytest' is invalid."
I think these are Forms objects, but they don't show up under the Forms
category in Access main window.

The actual deployment of this app is exactly what you said, every user has a
copy of the Access DB file on their local PC. For me, I'm trying to modify
certain things in it so I need exclusive access to it.

I tried the build "admin" account, and also the documented admin account in
the MDW file that accompanies the MDB file, which is "EFANAdmin", the same
result, I can't modify anything because I can't open it exclusively.

One thing mentioned in the document of this app is that all these are used
in a Windows domain environment. That is, the client user is logged into
the domain, and the MDW file is on a server on the same domain, and SQL
Server is on another server on the same domain. I'm wondering if the fact
that there is no way for me to open this thing exclusively is because I'm
not on the same domain? As a matter of fact, I'm not on any domain at all.

Anyway, I have printed out 30+ pages of Microsoft documentation on Access
Security and will read through it this weekend.

Thanks a lot for the help.
Bob

 

[Joan Wild]

So I tried you suggestion to create a new MDB file and import the
objects. I was able to import the tables, queries, and forms (that
show up in the Forms category) without problem. Also I exported the
VBA modules and class modules from the original MDB file and imported
successfully. However, there are a few items that show up under
Microsoft Access Class Objects in the VB Editor, with names starting
with Form_ or Report_, that I couldn't import. It gives the messages
"The module name 'Form_mytest' is invalid." I think these are Forms
objects, but they don't show up under the Forms category in Access
main window.

They may be hidden (tools, options, show hidden objects).

The actual deployment of this app is exactly what you said, every
user has a copy of the Access DB file on their local PC. For me, I'm
trying to modify certain things in it so I need exclusive access to
it.

I tried the build "admin" account, and also the documented admin
account in the MDW file that accompanies the MDB file, which is
"EFANAdmin", the same result, I can't modify anything because I can't
open it exclusively.

I thought you removed the security. The new mdb you created should be
unsecure and require no login. Open Access, and then use File, Open -
select your unsecure mdb and choose 'Open Exclusive' in the dialog box (the
dropdown beside the open button)

One thing mentioned in the document of this app is that all these are
used in a Windows domain environment. That is, the client user is
logged into the domain, and the MDW file is on a server on the same
domain, and SQL Server is on another server on the same domain. I'm
wondering if the fact that there is no way for me to open this thing
exclusively is because I'm not on the same domain? As a matter of
fact, I'm not on any domain at all.

No. You said you were working on the frontend that was on your local
computer.