Alternative to DLookup?

[jal]

I like DLookup but it seems a bit limited in the sense that I don't think it accepts a complex SELECT query.

I'd like to know if Access VBA allows you return a single value from a Command object. (VB.Net has a Command.ExecuteScalar function which is much like DLookup). As I wasn't sure, for the moment I'm using a recordset instead of a command object. In other words here's my VBA version of DLookup.

Private Function ExecuteScalar(ByVal query As String, ByVal ColName As String) As Variant
Dim rs As New ADODB.Recordset
rs.Open query, CurrentProject.Connection
Dim recCount As Integer
Do While Not rs.EOF
recCount = recCount + 1
rs.MoveNext
Loop
If recCount > 1 Then Err.Raise "Your custom Execute Scalar method has returned more than one value."
If recCount = 0 Then
ExecuteScalar = Null
Else: rs.MoveFirst
ExecuteScalar = rs(ColName)
End If
rs.Close
Set rs = Nothing
End Function


Is there a way to do this in VBA without the overhead of a recordset?

 

[stopher]

This might help a little more.

Chris

 

[gemma-the-husky]

dlookup returns the first (and that implies random) match for the select criteria you enter.

if you need a set of data, rather than a single value, you can use a select query. You may be able to use the results of that query directly to achieve your desired result.

Otherwise, you can process this query by iterating a recordset

 

[jal]

dlookup returns the first (and that implies random) match for the select criteria you enter.

if you need a set of data, rather than a single value, you can use a select query. You may be able to use the results of that query directly to achieve your desired result.

Otherwise, you can process this query by iterating a recordset

True, I guess in some respects my question wasn't very pertinent (although it seemed pertinent at the time).

 

[jal]

This might help a little more.

Chris

Nice article. That code could come in handy.

 

[DCrake]

Another tip is that when opening up a record set using ADODB unlike Access Jet you do not need to enumerate through the contents or use the Rs.MoveLast method to get the recordcount. Simply use the Open command then use the .RecordCount command to get the number of matching records.

Example

rs.Open StrSql, MasterDbConn, adOpenStatic, adLockReadOnly, adCmdText
        
            If rs.RecordCount > 0 Then

 

[jal]

Another tip is that when opening up a record set using ADODB unlike Access Jet you do not need to enumerate through the contents or use the Rs.MoveLast method to get the recordcount. Simply use the Open command then use the .RecordCount command to get the number of matching records.

Example

rs.Open StrSql, MasterDbConn, adOpenStatic, adLockReadOnly, adCmdText
 
            If rs.RecordCount > 0 Then

I'll try that. I was using RecordCount in all situations until I got burned on it because in some modes it gives -1. I need to pin down which modes where it actually works and which it doesn't.

I know that in the Optimistic-Keyset combo mode it does work. However, this mode isn't the most performant.

 

[LPurvis]

Well, on that subject of Recordcount and ADO cursor types, rather than spend ages - I'll regurgitate some thoughts of mine from elsewhere... ;-)


The RecordCount is a property you might take for granted in DAO - all three main types support it.

ADO is not only provider specific - but the type of cursors it opens have considerable different implementations.
Although Jet doesn't support it - the Dynamic is the most feature rich but with by far the greatest overhead.
Keyset and Static type ADO recordsets both support a recordcount too.
All three of those recordsets allow full navigation - and hence the recordcount isn't an effort.
(ADO recordsets which support a recordcount populate fully upon opening - though only a Static actually fills with the full data - the others use the primary key to maintain membership).

However the ForwardOnly cursor type recordset is, as its name implies, a one way deal.
An attempt to navigate (in one) to the end of the recordset fails - because it would be effectively spent.
Hence to support a RecordCount in such a recordset would be near pointless.
(You can navigate to the end of a Forward only recordset iteratively of course - and even move around backwards within the confines of your selected Cache size).

If you open a recordset such as
rst.Open strSQL
(i.e. without providing any of the trailing arguments) then the defaults are assumed.
The default cursor type is - no prizes for guessing this lol - ForwardOnly ;-)
The statement is analogous to
rst.Open strSQL, , adOpenForwardOnly, adLockReadOnly
(assuming that you'd assigned the connection previously).

As the default - this is also the type of recordset aquired with an Execute method
Set rst = cnn.Execute(strSQL)

The example offered
rs.Open StrSql, MasterDbConn, adOpenStatic, adLockReadOnly, adCmdText
is a Static type and hence would be able to accurately return the recordcount (all things being equal).

As an aside, although it might seem like a contradiction, you can issue a MoveFirst method on a Forward Only recordset and it will execute. But it's not in contradiction to the type's permitted behaviour, behind the scenes ADO has re-executed the query and you're just beginning again. (Rarely would you want such behaviour).

 

[jal]

Well, on that subject of Recordcount and ADO cursor types, rather than spend ages - I'll regurgitate some thoughts of mine from elsewhere... ;-)


The RecordCount is a property you might take for granted in DAO - all three main types support it.

ADO is not only provider specific - but the type of cursors it opens have considerable different implementations.
Although Jet doesn't support it - the Dynamic is the most feature rich but with by far the greatest overhead.
Keyset and Static type ADO recordsets both support a recordcount too.
All three of those recordsets allow full navigation - and hence the recordcount isn't an effort.
(ADO recordsets which support a recordcount populate fully upon opening - though only a Static actually fills with the full data - the others use the primary key to maintain membership).

However the ForwardOnly cursor type recordset is, as its name implies, a one way deal.
An attempt to navigate (in one) to the end of the recordset fails - because it would be effectively spent.
Hence to support a RecordCount in such a recordset would be near pointless.
(You can navigate to the end of a Forward only recordset iteratively of course - and even move around backwards within the confines of your selected Cache size).

If you open a recordset such as
rst.Open strSQL
(i.e. without providing any of the trailing arguments) then the defaults are assumed.
The default cursor type is - no prizes for guessing this lol - ForwardOnly ;-)
The statement is analogous to
rst.Open strSQL, , adOpenForwardOnly, adLockReadOnly
(assuming that you'd assigned the connection previously).

As the default - this is also the type of recordset aquired with an Execute method
Set rst = cnn.Execute(strSQL)

The example offered
rs.Open StrSql, MasterDbConn, adOpenStatic, adLockReadOnly, adCmdText
is a Static type and hence would be able to accurately return the recordcount (all things being equal).

As an aside, although it might seem like a contradiction, you can issue a MoveFirst method on a Forward Only recordset and it will execute. But it's not in contradiction to the type's permitted behaviour, behind the scenes ADO has re-executed the query and you're just beginning again. (Rarely would you want such behaviour).

Nice tutorial. For one thing, I didn't realize MoveFirst could be called on a forward only rs. And I don't know anything about caching recordsets so I certainly didn't know about moving backwards.

And I've updated my little function as to make use of RecordCount.

 

[LPurvis]

Kewl.

(And some more text to take me past 10 characters :-s)

 

[Banana]

In my case, I just check for .BOF and .EOF rather than recordcount to verify there's records. It always works whether the recordcount is -1, 0, or n.

I also would second Allen's functions- more often than not, SQL statements, especially if it's prepared in a fashion or other, is always faster than VBA functions doing the same thing.

HTH.

 

[LPurvis]

Just to mention as a parting offering...

The domain aggregates are Access functions (rather than being part of VBA ;-).
And they are indeed often thought of as slow/sluggish/lethargic - you get the idea.
But that's only true sometimes - and not always as bad as they're made out.
True - they can be poor (disasterous if used iteratively), but on local data there really isn't anything to beat them.
(Access' internal Jet interface exposes things we don't get to see

They have definite limitations though certainly. And are pretty rarely used in professional development. But they're not without use (e.g. the fore-mentioned local data).

When wanting to still make use of them though - an bit of imagination with the WHERE parameter allows you to make requests with a bit more scope (e.g. In, Exists etc).
That's just for fun though - an appropriate SQL statement is the correct course of action.