Network database username problem

[nickvans]

Hi all,

After several months of development (I was totally new to SQL, VBA and MS Access when I started) I finally have my database all squared away and operating exactly how I want. (With a great deal of help from you fine people at theScripts--thank you!) So the time finally arrived to put my form on the network and allow other people to begin using it. Much to my dismay, none of the users/workgroups I set in the database seemed to come along for the ride.

I created a new Workgroup Administrator file (cleverly named System1.mdw) and put it in the network drive with the database and set the database to look for that file, but it seems that this only affects me on my machine. Other users are not even prompted for a username and password, which needless to say causes some problems.

My security is minimal and primarily geared towards preventing unauthorized users from using several forms which modify table data. I have a front end form (frmFrontEnd) which is the only means for a user to access other forms, and before frmFrontEnd opens any other form it checks the current user name. In psudo-code it looks something like:

If username = "Admin" Then
open formX
Else
msgbox("You don't have permission to use this form.")
End If

Is there any way cause people opening the database from other machines to be required to log in? If not, is there another way to easily accomplish my goal of preventing unauthorized users from accessing other forms?

In advance, thank you!

nickvans

[ADezii]

Hi all,

After several months of development (I was totally new to SQL, VBA and MS Access when I started) I finally have my database all squared away and operating exactly how I want. (With a great deal of help from you fine people at theScripts--thank you!) So the time finally arrived to put my form on the network and allow other people to begin using it. Much to my dismay, none of the users/workgroups I set in the database seemed to come along for the ride.

I created a new Workgroup Administrator file (cleverly named System1.mdw) and put it in the network drive with the database and set the database to look for that file, but it seems that this only affects me on my machine. Other users are not even prompted for a username and password, which needless to say causes some problems.

My security is minimal and primarily geared towards preventing unauthorized users from using several forms which modify table data. I have a front end form (frmFrontEnd) which is the only means for a user to access other forms, and before frmFrontEnd opens any other form it checks the current user name. In psudo-code it looks something like:

If username = "Admin" Then
open formX
Else
msgbox("You don't have permission to use this form.")
End If

Is there any way cause people opening the database from other machines to be required to log in? If not, is there another way to easily accomplish my goal of preventing unauthorized users from accessing other forms?

In advance, thank you!

nickvans

The other Users are obviously not 'seeing' the Secured Workgroup Information File on the Network Drive. What they are seeing is the Default Workgroup File System.mdw residing in the C:\Program Files\Microsoft Office\Office\ Directory. You can run this little code snippit or run the MS Access Workgroup Administration Utility to find out which Workgroup File you are working under.
[CODE=vb]Dim retVal
retVal = SysCmd(acSysCmd GetWorkgroupFil e)

MsgBox "The current Workgroup File is: " & retVal[/CODE]
If the Users are using System.mdw locally, have each one in turn Join the Workgroup File on the Network Drive via the Workgroup Administrator. The last resort would be to force the Users to use System1.mdw via the Command Line /wrkgrp Parameter, but this is really not recommended, and we should never really have to go this route. Just as a side-note, do the Users have Permissions on the Network Drive and Folder where System1.mdw resides?

[nickvans]

The other Users are obviously not 'seeing' the Secured Workgroup Information File on the Network Drive. What they are seeing is the Default Workgroup File System.mdw residing in the C:\Program Files\Microsoft Office\Office\ Directory. You can run this little code snippit or run the MS Access Workgroup Administration Utility to find out which Workgroup File you are working under.
[CODE=vb]Dim retVal
retVal = SysCmd(acSysCmd GetWorkgroupFil e)

MsgBox "The current Workgroup File is: " & retVal[/CODE]
If the Users are using System.mdw locally, have each one in turn Join the Workgroup File on the Network Drive via the Workgroup Administrator. The last resort would be to force the Users to use System1.mdw via the Command Line /wrkgrp Parameter, but this is really not recommended, and we should never really have to go this route. Just as a side-note, do the Users have Permissions on the Network Drive and Folder where System1.mdw resides?

Thanks for the quick response ADezii. It looks like other users are in fact using System.mdw locally. I'm curious if having the users join the workgroup file will be a "global" change on their system. In playing around with the workgroup file yesterday, I discovered that when I open a different Access database I was still prompted for a username/password even though that database didn't have one initially. Is there a way to restrict Access to only using System1.mdw when opening my specific database?

To answer your side question, all users who will be using the database have access to the network drive/folder where System1.mdw resides, yes. The .mdw is currently in the same folder as the database.

Thanks again for your help!

nickvans

[Curben]

Thanks for the quick response ADezii. It looks like other users are in fact using System.mdw locally. I'm curious if having the users join the workgroup file will be a "global" change on their system. In playing around with the workgroup file yesterday, I discovered that when I open a different Access database I was still prompted for a username/password even though that database didn't have one initially. Is there a way to restrict Access to only using System1.mdw when opening my specific database?

To answer your side question, all users who will be using the database have access to the network drive/folder where System1.mdw resides, yes. The .mdw is currently in the same folder as the database.

Thanks again for your help!

nickvans

Make sure that the Admin account has a password, is moved to the "Users" group, and none of the object have "Admin" as the owner

Next you can make a shortcut to the database for users to use that will load your workgroup file ONLY when they access yoru Database.

Below is an example shortcut

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" 
"\\127.0.0.1\Project\Project.mdb" 
/wrkgrp "\\127.0.0.1\Project\ProjectFiles\Security\Project.  mdw"
/User "MyName" 
/Pwd "MyPa55word"

keep in mind that the username and password operators are options, this bypasses the login screen and logs the user in with the info supplied in the shortcut. I use this version for my own desktop which is secured, and include the link w/o in the directory for the average user.

[Curben]

The other Users are obviously not 'seeing' the Secured Workgroup Information File on the Network Drive. What they are seeing is the Default Workgroup File System.mdw residing in the C:\Program Files\Microsoft Office\Office\ Directory. You can run this little code snippit or run the MS Access Workgroup Administration Utility to find out which Workgroup File you are working under.
[CODE=vb]Dim retVal
retVal = SysCmd(acSysCmd GetWorkgroupFil e)

MsgBox "The current Workgroup File is: " & retVal[/CODE]
If the Users are using System.mdw locally, have each one in turn Join the Workgroup File on the Network Drive via the Workgroup Administrator. The last resort would be to force the Users to use System1.mdw via the Command Line /wrkgrp Parameter, but this is really not recommended, and we should never really have to go this route. Just as a side-note, do the Users have Permissions on the Network Drive and Folder where System1.mdw resides?

ADezii,
Why do you recommend against using the workgroup peramater?
I have yet to experience an issue with it, and in my division there are many different DBs ou there using different workgroups. I am open to better or cleaner options.

[ADezii]

ADezii,
Why do you recommend against using the workgroup peramater?
I have yet to experience an issue with it, and in my division there are many different DBs ou there using different workgroups. I am open to better or cleaner options.

I make it a policy never to incorporate User Names, Passwords, Workgroup Information Files, or anything similar to these as Command Line Arguments since there is virtually no security involved. It is a simple matter to have each Front End Application join the appropriate Workgroup, and as far as Users and Passwords as Command Line Arguments, I feel the reasons for not using them are obvious.

[nickvans]

Make sure that the Admin account has a password, is moved to the "Users" group, and none of the object have "Admin" as the owner

Next you can make a shortcut to the database for users to use that will load your workgroup file ONLY when they access yoru Database.

Below is an example shortcut

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" 
"\\127.0.0.1\Project\Project.mdb" 
/wrkgrp "\\127.0.0.1\Project\ProjectFiles\Security\Project.  mdw"
/User "MyName" 
/Pwd "MyPa55word"

keep in mind that the username and password operators are options, this bypasses the login screen and logs the user in with the info supplied in the shortcut. I use this version for my own desktop which is secured, and include the link w/o in the directory for the average user.

Thanks for the reply Curben. As the primary function of wanting to password protect my database is to prevent certain users from getting into certain objects, I think your shortcut idea is great. I'm having problems getting the shortcut to work though. I'm getting an error "The path specified was invalid, or may be too long." when trying to run it. The path I currently have is

 "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" " Z:\VCC\Nicks Improvements/VCC_2.05.mdb" /wrkgrp "Z:\VCC\Nicks Improvements/System1.mdw"

I've tried removing the /wrkgrp part to try to get it to simply open the file, but no luck. Should I only have quotation marks around the outside of the entire target path, or should they be around each file path as shown?

Thanks in advance!

[Curben]

Thanks for the reply Curben. As the primary function of wanting to password protect my database is to prevent certain users from getting into certain objects, I think your shortcut idea is great. I'm having problems getting the shortcut to work though. I'm getting an error "The path specified was invalid, or may be too long." when trying to run it. The path I currently have is

 "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" " Z:\VCC\Nicks Improvements/VCC_2.05.mdb" /wrkgrp "Z:\VCC\Nicks Improvements/System1.mdw"

I've tried removing the /wrkgrp part to try to get it to simply open the file, but no luck. Should I only have quotation marks around the outside of the entire target path, or should they be around each file path as shown?

Thanks in advance!

Well first thing i notice is the extra space between the " and the path for the database itself. That may have been a typo into the forums here so I'll ignore it for now. Second thing, I have seen Access depending on version and image use different directories and as you have the same path as my example I will suggest checking that as well.

the /wrkgrp argument is required as a peramater, but we can test them like this.
Create a second shortcut, and use it to test each part of the path, make your shortcut target C:\Program Files\Microsoft Office\OFFICE11 \MSACCESS.EXE, test it, then change the target to Z:\VCC\Nicks Improvements/VCC_2.05.mdb and test again, and you can do the same thing with even the workgroup file (link direct dont use the peramater argument)
The links individually should each work, if not your problem is in the path and thats your solution.


Edit: Mind your slashes and backslashes, after submitting I noticed what likely is the issue. Your pathname includes backslashes as appropriate till you have the actual filenames:
Z:\VCC\Nicks Improvements/VCC_2.05.mdb

[Curben]

Well first thing i notice is the extra space between the " and the path for the database itself. That may have been a typo into the forums here so I'll ignore it for now. Second thing, I have seen Access depending on version and image use different directories and as you have the same path as my example I will suggest checking that as well.

the /wrkgrp argument is required as a peramater, but we can test them like this.
Create a second shortcut, and use it to test each part of the path, make your shortcut target C:\Program Files\Microsoft Office\OFFICE11 \MSACCESS.EXE, test it, then change the target to Z:\VCC\Nicks Improvements/VCC_2.05.mdb and test again, and you can do the same thing with even the workgroup file (link direct dont use the peramater argument)
The links individually should each work, if not your problem is in the path and thats your solution.


Edit: Mind your slashes and backslashes, after submitting I noticed what likely is the issue. Your pathname includes backslashes as appropriate till you have the actual filenames:
Z:\VCC\Nicks Improvements/VCC_2.05.mdb

The peramater should still be /wrkgrp with a slash

[nickvans]

The peramater should still be /wrkgrp with a slash

Yup, that slash was what did it. Thanks for helping me out!

nickvans

[Curben]

Yup, that slash was what did it. Thanks for helping me out!

nickvans

Its kinda nice when the answer is simple :).

Good luck with the rest of your project