Using Workgroup Accounts on a Network

[Guest]

I have developed a very extensive database in Micrsoft Access 2003. I am
trying to place the databse on in a shared folder over a network. There will
be roughly 6-8 users, and I have assigned each of them usernames, passwords,
and corresponding permissions. I loaded the database on the main computer in
the shared folder. I then set up the workgroup. I could pull up the
database on that computer, and sign in under all of the users, with the
correct permissions in tact.

When I tried to open the database on other computers on the network, it gave
me the error telling me I did not have the necessary permissions. I
researched a little, and joined the workgroup on the other computers. I was
then able to view the database, but none of the permissions were in tact.
How can I place the database on the network, so it will constantly be updated
by all users, and still enforce necessary permissions to prevent corruption
of the design? I would love some help! Thank you!

 

[Rick Brandt]

I have developed a very extensive database in Micrsoft Access 2003.
I am trying to place the databse on in a shared folder over a
network. There will be roughly 6-8 users, and I have assigned each
of them usernames, passwords, and corresponding permissions. I
loaded the database on the main computer in the shared folder. I
then set up the workgroup. I could pull up the database on that
computer, and sign in under all of the users, with the correct
permissions in tact.

When I tried to open the database on other computers on the network,
it gave me the error telling me I did not have the necessary
permissions. I researched a little, and joined the workgroup on the
other computers. I was then able to view the database, but none of
the permissions were in tact. How can I place the database on the
network, so it will constantly be updated by all users, and still
enforce necessary permissions to prevent corruption of the design? I
would love some help! Thank you!

The workgroup only stores groups, user accounts, and the group memberships of
the user accounts. Permissions are stored in the MDB file so something else is
still not correct with your setup as the MDB cannot "lose permissions".

 

[TC]

You're missing a number of things there.

First, you do not normally select the right workgroup file by "joining"
it (on each PC) using the workgroup administrator program or function.
You do it by using a shortcut with the /wrkgrp switch in the target.
Also, you start the process by creating a brand new workgroup file, not
using the existing one (which you might have done, from the sound of
it).

If the above info is news, you really need to start again, this time
following an explicit list of written instructions; eg. the Access
Security FAQ, often referenced in this newsgroup. It's too hard to get
it right just by using the wizard & help file.

Second, if the database is shared by several simultaneous users, you
should split it into a so-called "front tne/back end" structure, to
reduce the chance of data corruption.

Googling this & other Access groups for /wrkgrp switch, startup
shortcut, FE/BE & similar terms will give you lots more information.

HTH,
TC

 

[Guest]

TC

Thank you for your input. I looked at a number of sites, like you said and
I am a little clearer, but still cannot get my database properly on the
server. I split the database, but when I split it, I cannot access the BE
from other computers. Do I need to used the linked table manager to link the
tables to the BE on the server? Also, do I just place a copy of the front
end on each individual computer, not on the network? If so, then what
happens if any changes need to be made to forms or reports? Do I have to
replace all of the front end copies with the updated version on each
computer? There must be a better way. After I have split the database
properly, and the BE is on the server, do I set up the workgroup settings and
permissions through the BE on the server, or the front end? And you said not
to use the "Join a workgroup," So, how do I get the permissions and workgroup
settings on each individual PC. I am very confused. I am sorry for all of
the questions. I have a lot of experience with Access, but placing it on a
server is new to me. All of your help is much appreciated.

AIMUS

 

[Joan Wild]

Thank you for your input. I looked at a number of sites, like you said
and
I am a little clearer, but still cannot get my database properly on the
server. I split the database, but when I split it, I cannot access the BE
from other computers. Do I need to used the linked table manager to link
the
tables to the BE on the server?

Yes, but you just need to do it once. Put the BE on the server. Ensure
that all users have read/write/create/delete permissions on the folder.
Open the frontend and use the Linked Table Manager to refresh the links to
the backend in its new location (suggest you use Network Neighborhood to
locate the backend). Then copy that frontend to each user (the links will
be retained).

Also, do I just place a copy of the front
end on each individual computer, not on the network?
Yes.

If so, then what
happens if any changes need to be made to forms or reports? Do I have to
replace all of the front end copies with the updated version on each
computer?

Yes. There are ways of automating updates. Have a look at Tony Toews' site
for a sample:
http://www.granite.ab.ca/access/autofe.htm

There must be a better way. After I have split the database
properly, and the BE is on the server, do I set up the workgroup settings
and
permissions through the BE on the server, or the front end?

Usually, one would want to secure both the FE and the BE using the same
workgroup file.

And you said not
to use the "Join a workgroup," So, how do I get the permissions and
workgroup
settings on each individual PC.

Leave each PC joined by default to the standard system.mdw that ships with
Access. Give each user a desktop shortcut that includes the /wrkgrp switch.
That will use a specified workgroup file *for that session only* and not
affect their default, used in all other sessions.
The target would look like
"path to msaccess.exe" "path to frontend" /wrkgrp "path to secure mdw"

The secure mdw would be put in the same location as the backend. You can
use the UNC pathname to the mdw in the shortcut above.

 

[Guest]

Joan Wild:

Thank you so much for your help. With the information you gave me and a day
of playing with it, i got it to work successfully! However, i have one minor
glitch: Now, I have the database on the server, and all the computers can
access it with the necessary permissions. There are only about 8-10 PC's,
and the system works
perfectly on all of them. However, on just one PC none of my drop down boxes
work. It shoots me an error "Run time error '429' Active X component can't
create object." Then, it shuts down, reopens, and saves a backup copy. When
it tries to reopen it sends another error saying it cannot find the file
location. The line of code it is sending the error to is "set rs =
me.Recordset.clone" I am confused. It only happens on that one computer.
Any help is greatly appreciated! Thank you!

 

[Joan Wild]

However, on just one PC none of my drop down boxes
work. It shoots me an error "Run time error '429' Active X component
can't
create object."

Given that this happens only on one computer, suggests it isn't setup the
same as other workstations.

Ensure *all* PCs are uptodate with the latest service releases/packs, and
that all PCs have the latest version of Jet 4.0 (you can check the version
of this file in the Windows System folder). Right-click the msjet40.dll and
check the version. It should be 4.0.8618.0

I can't check right now, but if you go to the MS knowledge base you should
be able to locate more information on that error.
http://support.microsoft.com/default.aspx?scid=FH;EN-US;KBHOWTO
and search for 429 or Active X component can't create object.

 

[TC]

Unfortunately there are 50 squintillion different causes of runtime
error 429. I personally think they should rename that error to: 429 -
"something boobed, bad luck buddy!"

Are you /sure/ that it is occurring on "set rs = me.recordset.clone"?
(Maybe put a msgbox statement immediately before, and immediately
after, just to check.) You should have dim'med "rs" to "as recordset".
Sure you don't mean "recordsetclone" (not recordset.clone)?

HTH,
TC

 

[Guest]

Thank you for your input and support. I am still in the process of figuring
the error out, with no success so far. I feel I am stuck at a dead end. If
you have any other ideas, please let me know. Thank you again for your help!

Aimus

 

[TC]

Quote:

Are you /sure/ that it is occurring on "set rs = me.recordset.clone"?
(Maybe put a msgbox statement immediately before, and immediately
after, just to check.) You should have dim'med "rs" to "as recordset".
Sure you don't mean "recordsetclone" (not recordset.clone)?

TC