SystemDB and MDW questions

I have a question regarding to the MDW file. The default of system.mdw is store in "c:\windows\system" directory.  And in the RegistryEditor "\HKEY_LOCAL_MACHINES\Software\Microsoft\Jet\3.5\Engines" I can found out where the MDW file located. My question is:

Is that a way by using VBA to change the location of the MDW file to the Network directory whenever users run the MDB file from Network ? So, that I don't have to go to each user's machine to Join the MDW file from Network. What I did is that I write the VBA code:

DBEngine.SystemDB = "\\Network\database\db.mdw"

and then put it in Autoexec macros, but it seems won't change the location of MDW whenever the MDB file opened. Is that a way to do it or any problem with the VBA code. Thank you very much.
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.

twlewAuthor Commented:
Adjusted points to 100
Twlew:  Welcome to EE.

You put the location of the MDW file in the command line of the shortcut which opens Access and the MDB file from the network.

The user can not just switch from one MDW file to another with Access open.

They will either have to call another instance of Access with the proper MDW specified or close and reopen through the proper shortcut.

The command line switch is /wrkgrp path-file.

Unless you need to have password login, there are other methods for using security settings which could allow the user to get access to the network application with their local System.MDW.

twlewAuthor Commented:

   I know that if I put the MDW to the shortcut, which add some command line on it, it will works, and just let the users click on the shortcut. But the problem is, once the users found out the MDB file and click on it, user will run the MDW file on his/her machine instead of from the network which is not secure even though the MDB is hidden
  Ya, I need to have username and password login for each user. May I know what is another method for using security setting that allow the user to get access to the network application with their local System.MDW ?? Is that I have to go to each users' machine to do some setup ??

  By the way, thank you very much for your comments, I appreicate it.
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!

are you concerned about people being able to open and use the file who don't belong to the workgroup and are using there own system.mdw file? If that means you did not remove the default permissions for the "admin" user, and the "User" and possibly that the admin user is still the owner of the db objects
I guess I'm like blakeh1 at this point in time.  Exactly what are you trying to do or protect?

Passwords are primarily used to allow ingress into Access.  Once in Access the user has the permissions assigned to that passworded user id.  Else all users are actually logged in as Admin and have all rights that the Admin user have.

There is some good information in the help files of Access which talk about WorkGroups and Passwords.  Every time that I pass on a segment of those files to a member on EE, there will be someone fuzzing that I shouldn't have done that.  So I can't win and you have to go look the information up.

The best thing that I can say is password security is not very good.  If the information or the files are very sensitive, you are better off with user level security, which is what I was referring to in my first comment.  You won't have to deal with any user MDW's as any user can use their own and still have access to the database.  However, you can decide what level access they have depending on the security you set up.

Since the users are on a network, I'd let the network security decide who can have access to the database.  They are already required to login with a username and password into the network so that is the first step.  Inside the network, you can restrict any directory or file to certain users or user groups.

Before I can advise you further, you need to decide what you want the users to be able to see.  With the MDW, you were only restricting which users could open Access with that workgroup setup.

To give you some suggestions:  we have our database split into serveral databases.  Programs (modules, forms, queries, etc.) have read permission for modules and no administration permisssions for anything.  Queries are all with owner access.  Data has no read permissions for anyone except the owner and we have disabled Shift entry into the db.  When someone tries to enter directly, we warn them and kick them out.  We also have some other databases like Reports - the canned reports are read only, the queries are read only, however a user can create their own reports using the canned ones as templates and can access any data via queries for read only.

The key to this whole scenario is making all queries With Owner Access and allowing no access to the tables except through these queries.

We go through quite a lot of security setups before the DB is released and the time element is from 2 to 4 hours to put a development DB into production.  It may be worth while for you to set your system up like this but the first time through can be quite time consuming until you get it the way that you like it.  We spent several thousands of dollars in security consulting fees to get ours the way that it is, but I don't have to fear anyone hacking into our MDW (it is an Access database, you know) and jeopardizing our security.  I don't care if people can see the query design or not.  They can't change it, nor can they see or change Forms, Macros, Reports, or Modules.

What more can I tell you?


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
twlewAuthor Commented:
   Thank you very much for the detail information. Well, actually what I want is that when user click on the MDB file, the Username and Password window pop up to let user logon. I need the Username to keep track who updates and creates the database record. Besides, if I used the shortcut command line:

C:\Program Files\Microsoft                        Office\Office\MSACCESS.EXE" "\\network\                       database\db2.mdb"/wrkgrp "\\network\database\Irina.mdw"

it works just for those users whom MsOffice is installed to "C:\Program Files\Microsoft Office" directory ONLY. Those who has different directory won't work. And I have change it manually one by one. In addition, if a user copy the MDB file and run it in his/her personal computer(without network which it MDW file in his/her computer is default setted to "c:\windows\system.mdw") the user can access the MDB without the username and password.

  I really fed up with the security of MsAccess. Do you have any suggestion ? Thanks in advance.
twlew:  The reason what you are fed up with Access security is that you aren't really using any real security.  It is a difficult subject and isn't really well documented.

I just wrote a half hour's worth of info for you here and then I lost it all when I tried to submit the comment.

So, because I am really behind schedule, I must take a shortcut and give you a primer 101 which brewdog has been handing out to people on EE.  What you need to do will take a little understanding and time to accomplish.  If this information is too beginner, I apologize.  As I said, I'm a bit rushed at the moment and wanted to respond to you as I can understand your frustration.

I'll give you more when I have a free moment.


1) Open Workgroup Administrator (installs with Access, usually located in C:\WINNT\system32\ and called WRKGADM.EXE) and choose New. Give the workgroup a name and workgroup ID. Workgroup ID is important because, if your workgroup ever gets corrupted, you can restore it only by creating a new one using the exact same name and workgroup ID. This is case-sensitive, too.

a) This must be done so users can still get into the default, unsecure version of Access without logging in

b) This creates a new system database, which stores all groups, users, and passwords

i) System database stores: groups, users, passwords

ii) Standard database stores: permissions on individual objects

2) Create an icon on your desktop that points to the Access executable and your workgroup, something like: "C:\Program Files\Microsoft Office\Office\msaccess.exe" /wrkgrp "c:\my database\my system.mdw"

a) I put paths in quotes because of the spaces in the file names

b) The /wrkgrp switch functions like the ini file did in Access 2, redirecting Access immediately to verify the security system involved before looking to anything else

3) Open Access through your new icon

4) Choose Tools | Security | User and Group Accounts

a) Create a new user name and personal ID for yourself (or whomever will be the system administrator)

b) Add this new user to the Admins group

c) Enter a new password for the current user - Admin - you are by default logged in as Admin. Passwords are case-sensitive; this is the trigger for Access' security system: the Admin user has to have a password

d) There is no old password

5) Exit Access and re-open

a) Security will be enforced now that the Admin user has a password

b) Log on with your own (new) user ID

c) You do not have a password yet; leave the password blank

6) Create a blank database

a) This will make you, and not the Admin user, the owner of the database and all of it objects

i) Even if you remove all permissions for all objects from other users, if another user is the owner of an object (or the database itself), Access will grant them certain rights (open/read, etc.)

b) If you are securing an already-existing database, import all objects from that database into this new blank database

7) Choose Tools | Security | User and Group Accounts and remove Admin from the Admins group. This must be done if you want your user ID to have sole control over the database. If you don't take this step, any user can open your database(s) as long as they guess the password and have whatever privileges you have left to the Admin user.

8) Add, through Tools | Security | User and Group Accounts, other groups and users who will need access to your database(s)

a) Create Groups first, then assign Users to those groups

i) Each individual user is granted, by Access, all rights assigned to them individually and all rights given to any and all groups to which they belong. Access rights are as widely inclusive as possible

b) Assign Group permissions first, then individual User permissions next

9) Set a password for the current user, the system administrator

10) Exit Access

11) Modify your desktop icon to point to the Access executable, then your database, then the workgroup file:

a) "C:\Program Files\Microsoft Office\Office\msaccess.exe" "c:\my database\mydatabase.mdb" /wrkgrp "C:\my database\my system.mdw"

You can at this point either open Workgroup Administrator again and Join the default system.mdw to reset the security on the default version of Access, or let it reset itself when you reboot your machine. On each user's machine, then, you will want to create an icon pointing directly to your database, with a command line like that shown above. This prevents user confusion when opening the unsecure version of Access.
twlewAuthor Commented:

   Thank you very much for your time, I really appreciate it. Well, my main problem is that

"C:\Program Files\Microsoft Office\Office\msaccess.exe" "c:\my
                        database\mydatabase.mdb" /wrkgrp "C:\my database\my

the Shortcut command line is working fine, but IF user's Microsoft Office97 is installed in different directory(for example: in "C:\Program Files\Office97" instead of "C:\Program Files\Micorsoft Office") than the Shortcut that I created, he/she cannot get into the MDB file coz his/her MsOffice installed in different directory. If I need to create an Icon for each user manually, then I prefer do it manually for joining the MDW file on network manually. But it's kind of time consuming and not good for long run.

  If we use VB to write a code to change the Registry whenever user open MDB file. It's perfect !! But I know you told me before that it's impossible. Anyway, I really appreciate your help and suggestion. Thanks in million
You should not need to have the full path to MSAccess.exe in the shortcut.  Windows will look in the registry for known programs, i.e. Microsoft's programs, and find the program and run it.  To test this out, go to Start > Run and enter MSACCESS.EXE.  Access should start.  If it does, it is registered properly and is not really needed in the path.

Another way around this is to spend the $300 for the ODE (Office Developers Edition) which the users can run from a network directory.  It will find the appropriate paths and create the shortcuts automatically.

twlewAuthor Commented:

   Thanks for the suggestion again, I will think about the ODE. You know what, I just create a System.Reg to change the Registry of SystemDB, but maybe it won't help. Anyway, don't know what you think

the code of System.reg is:




then Save As xxx.reg file and run it. Just wanna share it with you.

By the way, is Office97 Y2K compliance ?? Because I read some info on web that it preffer Office97 users to change the short date to 4 digit. Just go to Control Panel-->Regional Setting-->Date--> and change the Short Date to
"MM/DD/YYYY" instead of "MM/DD/YY". Is that necessary ??

I'm not that familiar with working with the Registry.  So I can't help you there.  Looks interesting.  Does it work?

As far as the Y2K aspect, if you use the long date, then the dates will be correct.  If you use the short date, any two year date before, I believe, '32 will be treated as 2032.  Any other date will be treated as 19XX.

I have gone through my all my apps and changed the date fields formats to long date.

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.