Another Problem - Setting Query Criteria at login

[don]

Hi Group,

Newbie trying to learn.

I have a DB which I'm trying to develop. The Db is intended to be
used at different locations. As work comes in it can be assigned to
these location teams for further action. All access rights and
permissions would be the same for each of these teams.

What I would like to achieve is a link reference which would be
generated at login which set the criteria for the location. So all
teams could use the same appointments form but the login reference
would only return their appointments. I can I can see creating a
login table with passwords etc would work but that would go away from
the built in groups and permissions available.


Can anyone give me some advice on which way would be best /
achievable?

Any help would be greatly appreciated.


Regards


DonH

 

[Arvin Meyer [MVP]]

If you are using Access built in security you can do:

Function InGroup(strUser As String, strGrp As String) As Boolean
' Description : Indicates, if a user is part of a specific group
' Parameters : Name of user and group
' Result : True/ False
' Example : Call InGroup("Arvin", "Developers") -> True

Dim usr As User
Dim grp As Group

Set usr = DBEngine.Workspaces(0).Users(strUser)

For Each grp In usr.Groups
If (grp.Name = strGrp) Then
InGroup = True
Exit Function
End If
Next

InGroup = False

End Function

To authenticate both user and group. The problem is, the user may belong to
a group which doesn't have permissions to view security. My solution is to
use the Windows security api to get the network user name and compare it to
a list in the table. Here's the code to get the username:

http://www.mvps.org/access/api/api0008.htm

Now just create a function to look the user up in your table of users and
return a group, location, or permission level. Now each user only has access
as long as they can authenticate on your network. And you can use the value
the code returns to pass the location to a query.

 

[Larry Daugherty]

FYI you should jumble your email address just a little to make it more
difficult for the address harvesting bots. They're not too smart but
they will snap up easily readable emails and generate a lot of spam.

Access doesn't play well with users who are not on the same local area
network. It's a file server, not a client/server. It can play using
Remote Desktop or Terminal Server. Another possible solution is to
use MSDE or SQL Server for the Back End. That would significantly
reduce the network load. Yet another solution might be Replication.
With replication, each set of users uses a local Back End which must
be made available to the Replication Master in order to incorporate
their data.

Unless there is a need to capture all of the above information in a
single place then I'd just give each location its own copy of the
application.

Note that after you've got your design pretty well along and the table
design seems to be stable you should split your application into a
FrontEnd and a BackEnd. That makes it easier to maintain and enhance.

Most of the solutions indicated in the 2nd paragraph are a stretch for
a newbie.

HTH

 

[don]

Thank you both for your advice, will change my e-mail too.

Many thanks

DonH