Is there a faster way

[Access]

I am looking for the most efficient method to check for a entity
membership in a group. Is there a better method than this? I am
working with a native Access database. At some point I'll probably end
up splitting the data from the front end.

Public Function isGroupMember(lngEntityID As Long, lngGroupID As Long)
As Boolean
On Error GoTo err_isGroupMember
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblGroupMembership.GroupID,
tblGroupMembership.EntityID FROM tblGroupMembership WHERE
(((tblGroupMembership.GroupID)=" & lngGroupID & ") AND
((tblGroupMembership.EntityID)=" & lngEntityID & "));"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rs.EOF Then
isGroupMember = False
Else
isGroupMember = True
End If
rs.Close
Set rs = Nothing

err_isGroupMember:
If Err <> 0 Then
isGroupMember = False
End If
End Function

[Allen Browne]

How about:
Not IsNull(DLookup("GroupID", "tblGroupMembership", _
"(GroupID = " & lngGroupID & ") AND (EntityID = " & lngEntityID & ")"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Access" <al*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I am looking for the most efficient method to check for a entity
membership in a group. Is there a better method than this? I am
working with a native Access database. At some point I'll probably end
up splitting the data from the front end.

Public Function isGroupMember(lngEntityID As Long, lngGroupID As Long)
As Boolean
On Error GoTo err_isGroupMember
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblGroupMembership.GroupID,
tblGroupMembership.EntityID FROM tblGroupMembership WHERE
(((tblGroupMembership.GroupID)=" & lngGroupID & ") AND
((tblGroupMembership.EntityID)=" & lngEntityID & "));"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rs.EOF Then
isGroupMember = False
Else
isGroupMember = True
End If
rs.Close
Set rs = Nothing

err_isGroupMember:
If Err <> 0 Then
isGroupMember = False
End If
End Function

[Jamey]

DCount(...as in Allen's example...) > 0
and
Nz(DLookup(...as in Allen's example...)) <> ""
work as well.

i usually use the DCount variety of this test, and do so quite often.

Allen,

you see any advantages, disadvantages to either approach?

[Lyle Fairfield]

Not CurrentProject.Connection.Execute("SELECT * FROM
[4060148Transactions] WHERE CreditAccountID = 19 AND DebitAccountID =
18").BOF

[Allen Browne]

DCount() is slower: Access can't just return the first value; it has to
continue through the entire table to get the total count.

The Nz() is superfluous, because DLookup() is wrongly programmed by
Microsoft. It is incapable of distinguishing between a Null and a
zero-length string (ZLS), so if returns Null even if it should return a ZLS.
So, in this case, checking for a ZLS adds nothing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jamey" <ca*********@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

DCount(...as in Allen's example...) > 0
and
Nz(DLookup(...as in Allen's example...)) <> ""
work as well.

i usually use the DCount variety of this test, and do so quite often.

Allen,

you see any advantages, disadvantages to either approach?

[Terry Kreft]

Public Function isGroupMember( _
lngEntityID As Long, _
lngGroupID As Long _
) As Boolean
Dim rs As New ADODB.Recordset
Dim strSQL As String

On Error GoTo err_isGroupMember

strSQL = "SELECT Count(*) " _
& "FROM tblGroupMembership " _
& "WHERE tblGroupMembership.GroupID)=" & lngGroupID _
& " AND tblGroupMembership.EntityID)=" & lngEntityID

call rs.Open(strSQL, CurrentProject.Connection, _
adOpenForwardOnly, adLockReadOnly

isGroupMember = cbool(rs(0))
rs.Close
Set rs = Nothing
Exit Function
err_isGroupMember:
If Err <> 0 Then
isGroupMember = False
End If
End Function

--

Terry Kreft
"Access" <al*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I am looking for the most efficient method to check for a entity
membership in a group. Is there a better method than this? I am
working with a native Access database. At some point I'll probably end
up splitting the data from the front end.

Public Function isGroupMember(lngEntityID As Long, lngGroupID As Long)
As Boolean
On Error GoTo err_isGroupMember
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblGroupMembership.GroupID,
tblGroupMembership.EntityID FROM tblGroupMembership WHERE
(((tblGroupMembership.GroupID)=" & lngGroupID & ") AND
((tblGroupMembership.EntityID)=" & lngEntityID & "));"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

If rs.EOF Then
isGroupMember = False
Else
isGroupMember = True
End If
rs.Close
Set rs = Nothing

err_isGroupMember:
If Err <> 0 Then
isGroupMember = False
End If
End Function

[david epsom dot com dot au]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Not CurrentProject.Connection.Execute("SELECT * FROM
[4060148Transactions] WHERE CreditAccountID = 19 AND DebitAccountID =
18").BOF

Surprised. Would have expected ...("SELECT Null FROM ...

Would that work as well?
(david)

[Lyle Fairfield]

I think so.
I suppose with a large table one might want to use:
"SELECT TOP 1 0 FROM [4060148Transactions] WHERE ...
(That's not ten; it's the top 1 of the "zeroeth" field.)
I expect there are many ways of dealing with this. If the table had a
million records and the code were to be run a million times a day I'd
probably use an index. As it is, this is takes between 50% and 70% of
the time the DLoopUp thingme takes/