Access 03 Security

[Monty]

I have searched from head to toe and can't find an answer in this aor any
other forum.

I'm trying to secure an Access 03 database on a corporate wide network.
Some use the program in English, some use French. The DB is split into FE
and BE, both secured by the same Workgroup File. I have disabled the
Admin/Administrateur & Users/Usagées permissions, ownerships, admin
privilages etc.... I have set up the User Groups and Accounts with no
problems. The problem is that the file is accessible on the network to all
that want it. Whenever someone opens either the FE or BE without the using
the prescribed Workgroup file, they have full access to the DB. It's as if
the computer is creating a new admin file evertime and overriding my security
efforts. Once the Workgroup file is mapped THEN the ULS kicks in and works
well. In essence, it's harder for assigned users to access the DB then
unauthorised people....go figure. I'm at a loss as to what to do and how to
fix this.

Thanks

 

[Joan Wild]

I have searched from head to toe and can't find an answer in this aor any
other forum.

I'm trying to secure an Access 03 database on a corporate wide network.
Some use the program in English, some use French. The DB is split into FE
and BE, both secured by the same Workgroup File. I have disabled the
Admin/Administrateur & Users/Usagées permissions, ownerships, admin
privilages etc.... I have set up the User Groups and Accounts with no
problems. The problem is that the file is accessible on the network to
all
that want it. Whenever someone opens either the FE or BE without the
using
the prescribed Workgroup file, they have full access to the DB.

I'm sorry, but that *does* mean there are problems. You may think you have
setup security properly, however you have missed a step. It's a good test
to see if you've secured it properly, by attempting to open the secure mdb
while using (joined) to the standard system.mdw that ships with Access. If
you can even open the mdb, then you didn't secure it properly. It's likely
that the 'Admin' user still owns the database object; since that user is
common to every mdw file, anyone using any mdw can open the mdb.

It's as if
the computer is creating a new admin file evertime and overriding my
security
efforts. Once the Workgroup file is mapped THEN the ULS kicks in and
works
well. In essence, it's harder for assigned users to access the DB then
unauthorised people....go figure. I'm at a loss as to what to do and how
to
fix this.

Once you have it secured properly, then only those using the proper mdw file
will be able to get in. Instead of joining them to your secure mdw, leave
them joined to system.mdw and provide a desktop shortcut to open the FE.
Its target would look like...
"path to msaccess.exe" "path to FE.mdb" /wrkgrp "path to secure mdw on
server"

 

[Graham Wideman [Visio MVP]]

Hi Monty,

You might also find this article helpful:

http://grahamwideman.com/gw/tech/access/accesssec/index.htm

Graham

 

[MFSCLARK]

Graham, I read your article (after reading about 5 others) and something
clicked. I've been trying to secure my database for the past 3 days and the
problem I'm having is that is I run it from the shortcut with the target
C:\.........ACCESS.EXE" "C:\My Documents........my database" /wrkgrp
"O:.......wrkgrp.mdw"
It opens with security; however, if I go to My documents thru the windows
explorer and click on my database, it will open under the default mdw file
and that does not have securities.

What clicked and I hope this is my misunderstanding, on your article, under
"Controlling and Viewing which MDW is in use" - "Start Access with Different
Wrokgroup file" You say: "...you can start Access with a Command line that
specifies to use a different MDW instead." IS there somewhere besides the
desktop shortcut that I can place this command line? To me is seems that it
would have to be in the database itself somewhere where it would be run upon
opening the database. How else will the wkjgrp.mdw get activated if a person
clicks it from the Windows Explorer File????

 

[Joan Wild]

It opens with security; however, if I go to My documents thru the windows
explorer and click on my database, it will open under the default mdw file
and that does not have securities.

If that's the case, then you missed a step in securing the mdb. You
shouldn't be able to even open the mdb using system.mdw.

What clicked and I hope this is my misunderstanding, on your article,
under
"Controlling and Viewing which MDW is in use" - "Start Access with
Different
Wrokgroup file" You say: "...you can start Access with a Command line
that
specifies to use a different MDW instead." IS there somewhere besides
the
desktop shortcut that I can place this command line?
No

To me is seems that it
would have to be in the database itself somewhere where it would be run
upon
opening the database.

No, the mdw is tied to a session of Access, not a database. You can setup
security such that a database can be used with a number of mdw files; and
you can have many databases that will open using one mdw file.

Access always uses security. It ships with a mdw named system.mdw that is
used for every session. You are silently logged in as 'Admin' using this
mdw for unsecure sessions. Access always has some mdw that is set as the
default one to use (that would be in effect if you just opened Access, or
opened a mdb file from Windows Explorer). You can change the default via
the workgroup administrator, or you can use a desktop shortcut with the
/wrkgrp switch to override the default *for just that session* of Access.
Once you've opened a session of Access using a secure mdw, you can open any
number of mdb files during that session, and you're still using the secure
mdw.

How else will the wkjgrp.mdw get activated if a person
clicks it from the Windows Explorer File????

Your choice is to use/distribute a desktop shortcut, or change each
computer's default mdw to your secure mdw. The former is preferred, since
users likely have unsecured databases and if you join them by default to
your secure mdw, they'd get a login prompt for every session of Access.

 

[Larry]

I'm using this setup to give specific security permissions to certain users
of a DB on a corp shared drive. How ever i am finding some difficulty trying
to import data from queries into excel with this security format.

Please advise.

Thanks,
Larry

 

[Larry]

Sorry I should elaborate a little more. I'm unable to log into the db
through excel to import data using the data>import data command in excel.

When i try to open it, excel seems unable to associate it to the correct mdw
file.

 

[Joan Wild]

I'm going from memory here, but from Excel I believe you setup a query using
MS Query - during the setup of that, you have the option of specifying a
system file (mdw) and username/password.

I think you'll find it easier to export from Access to Excel rather than the
other way. The user would already be authenticated within Access and have
no problem exporting to Excel. (Tools, Office Links, Analyze with Excel).

 

[Larry]

Ok I got it working! Thanks for your help Joan.

For anyone else posing this issue I had to go through Data > Import external
Data > New Database Query.

- Click on New Data Source
- Create a name for it in Excel and select MS Access for the driver.
- Click Connect
- Select your Access DB
- In the System Database area click on the database radio button
- Click the System Database button and select your MDW file.
- click on advanced to input your login info for the DB

And your all set!

I elected to pull the data from access through excel instead of pushing the
data out of access to excel because this data will need to be updated daily
or more. Excel has the ability to rerun the needed queries and add or update
cells as needed with in its current format. If i were to push it from access
then there would be alot more manual labor on my part to keep the spread
sheet organized and calculating the data correctly.