Module of User In Group

[Guest]

I copied a Module from one secured database to another secured database (both
Access 2000). The module works in the first database but does not work in
the other. The code is as follows:

Option Compare Database

Function faq_IsUserInGroup(strGroup As String, strUser As String) As Integer
' Returns True if user is in group, False otherwise
Dim ws As Workspace
Dim grp As Group
Dim strUserName As String

On Error Resume Next
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
strUserName = ws.Groups(strGroup).Users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function

When I run the code in the database I copied it to, the line Dim ws As
Workspace is shown to be a compile error: User defined type not defined. I
try to retype Workspace in the Dim line, and the list that pops out does not
include Workspace in there.

I am trying to run the code to see if the user is a member of a certain
group. I know something was posted months ago and I obtained the code from
that.

Any help is great! MIKE

 

[Douglas J. Steele]

Workspace is a DAO object. By default, Access 2000 uses ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[John Vinson]

When I run the code in the database I copied it to, the line Dim ws As
Workspace is shown to be a compile error: User defined type not defined.

Select Tools... References from the menu in the VBA editor; I suspect
that Microsoft DAO 3.6 is not checked. Scroll down, find it, and check
it - this should fix this error.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks! I checked the 3.6 and unchecked the 2.1. That's what I was missing.
MIKE

 

[Guest]

The IsUserInGroup is working fine. Now I am trying another module with the
following:

Function MultipleUsers() As String
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & Chr(34) & "N:\Common\DATAPROC\Computer
Operations.mdb" _
& Chr(34)

cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Chr(34) & "N:\Common\DATAPROC\Computer
Operations.mdb" _
& Chr(34)

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

rs.MoveFirst
If Trim(rs.Fields(1)) = "Admin" Then
rs.MoveNext
Else: MultipleUsers = Trim(rs.Fields(0))
End If
rs.MoveNext

While Not rs.EOF
If Trim(rs.Fields(1)) <> "Admin" Then
MultipleUsers = MultipleUsers & ", " & Trim(rs.Fields(0))
End If
rs.MoveNext
Wend

End Function

I found I needed to go to Tools - References to add an object library and I
added Microsoft ActiveX Data Objects 2.5 library. When I ran this function
in a query I got:

You do not have the necessary permissions to use the
N:\Common\DATAPROC\Computer Operations.mdb object. Have your system
administrator or the person who created this object establish the appropriate
permissions for you.

Which is incorrect because I created the object and I am in the Admins group
and have full permissions. Is the reference incorrect, do I need to use a
different one?

Thanks for any help. MIKE