Access 2007 - Workgroup Problem

[DJH]

I have an Access 97 MDB and MDW, the frontend is on the local PC and the
backend and workgroup files are on the network. I am now installing Access
2007 on some PCs. I found out how to run the workgroup administrator in
Access 2007 so that I can join the named mdw on the network. The problem is
that when another user logs into that PC, he is not joined to the workgroup,
and is not prompted for a user and password when opening this database. How
can I set the workgroup join ahead of time for all users who log into the PC?

 

[jmarkron]

I have an Access 97 MDB and MDW, the frontend is on the local PC and the
backend and workgroup files are on the network. I am now installing Access
2007 on some PCs. I found out how to run the workgroup administrator in
Access 2007 so that I can join the named mdw on the network. The problem is
that when another user logs into that PC, he is not joined to the workgroup,
and is not prompted for a user and password when opening this database. How
can I set the workgroup join ahead of time for all users who log into the PC?

The simple solution is to have your users begin the application by using a
SHORTCUT with the following:

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\_DirOffRoot_\
YourDBName_cd.accdb" /WrkGrp "M:\_ServerDir_\YourDBNameSystem.mdw" /user
"AutoDemo" /Pwd "demo"

The /user is recommended but the /Pwd is only if you wish them (or a AUTO-
process) to login without entering a password. Maintain your system.mdw in
your WorkGroupAdministrator as appropriate. This is the simple solution.

The next step is the RIGHT way to do it. You still implement the step above.
However, any of your databases should have an AUTOEXEC start-up logic that
checks for non-authorized ADMIN entries and immediately performs 'DoCmd.Quit'
to kick them out. This can be done in simple approaches or as I have done, a
user validation table and logic that manages the attributes of users,
permissions, forms and macros.

Good luck...... Ron

 

[DJH]

I know I could do the shortcut, but that doesn't prevent new users to the PC
who have not joined the appropriate workgroup there from opening the database
directly without getting prompted to login and get the correct permissions
for their username.

From the shortcut command given, I see that you referenced a .accdb file.
We need to keep the network backend data mdb and workgroup mdw in Access 97
format during this transition from 97 to 2007 on our workstations. For those
PCs with Access 2003 or 2007, I have a 2000 version MDB frontend on their c:
drive.

I was wondering where the workgroup join information is stored for each user
on the PC, and if there was anyway to set it so that any user who logs into a
specific PC is automatically joined to the workgroup. The DoCmd.RunCommand
acCMdWorkgroupAdministrator works fine in Access 2007 for the user who is
logged in when the command is executed, but it doesn't carry on to other
users who may log into that PC.

 

[jmarkron via AccessMonster.com]

Hi DJH,

You have several issues:
1) Your application/database MUST have some logic that traps non-authorized
users (and I suggest 'ADMIN' as a non-authorized user) and kicks them out.
This allows anyone on any PC to run MSAccess in their world but stops them
from entering your application/database world... regardless of what xxxSYSTEM.
mdw they are attached to.

2) In the SHORTCUT, you need to define the a) MSAccess version to
run, b) the database to open and c) the xxxxSYSTEM.mdw file to run under.
This forces the environment you want to operate in, including the log-in
dialogue window. You can copy any current SYSTEM.mdw to a specific folder
location, change the name, address it in your SHORTCUT statement, and
maintain it using your DoCmd.RunCommand >acCMdWorkgroupAdministrator. There
is no join function (which only sets the default MSAccess user's system-
registry entry) because it is controlled in the SHORTCUT statement as an
override to the system-registry.

3) The trap for non-authorized users MUST be in your AUTOEXEC macro and will
be a pretty elaborate process calling either VBA logic to identify the user
(=CurrentUser()) or some series of table-lookup records where you can store
legal users for authorization.

I have about 300 hours of developing this (and more user control) logic since
MSAccess2, (through 95, 97, 2000, XP, 2003 and 2007) and I use it extensively
for my clients. It is well documented and contained for an experienced
developer to work with. I hesitate to distribute it because it is difficult
for inexperienced developers to support. If interested in more one-on-one
discussion, we might open another channel of communication to accomplish that.

........Ron

I know I could do the shortcut, but that doesn't prevent new users to the PC
who have not joined the appropriate workgroup there from opening the database
directly without getting prompted to login and get the correct permissions
for their username.

From the shortcut command given, I see that you referenced a .accdb file.
We need to keep the network backend data mdb and workgroup mdw in Access 97
format during this transition from 97 to 2007 on our workstations. For those
PCs with Access 2003 or 2007, I have a 2000 version MDB frontend on their c:
drive.

I was wondering where the workgroup join information is stored for each user
on the PC, and if there was anyway to set it so that any user who logs into a
specific PC is automatically joined to the workgroup. The DoCmd.RunCommand
acCMdWorkgroupAdministrator works fine in Access 2007 for the user who is
logged in when the command is executed, but it doesn't carry on to other
users who may log into that PC.
[quoted text clipped - 23 lines]

Good luck...... Ron

 

[Keith Wilby]

The next step is the RIGHT way to do it.

I'm afraid it is certainly not the right way. What you describe can be
easily bypassed. If there is a "right" way then that way is the built-in
user-level security. Whilst not perfect it will keep most nosy-parkers out.
There's a link to the MS FAQ on my web site.

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

I know I could do the shortcut, but that doesn't prevent new users to the
PC
who have not joined the appropriate workgroup there from opening the
database
directly without getting prompted to login and get the correct permissions
for their username.

Your shortcut method is correct but if your users can open your database
file without your shortcut then you haven't secured it properly. See my
other posting in this thread for more info. Note that user-level security
is not supported in the accdb file format.

Keith.
www.keithwilby.co.uk