DLookup Equivalent with ADODB.Recordsets
Brian VanDyke
Access, but I'm doing so in VB 2005 (which pulls a specific value from a
given database table with the criteria given).
Here is the code...
Public Function DLookup(ByVal dbField As String, ByVal dbRecordset As
String, ByVal dbCriteria As String) As Object
Dim dsResult As New ADODB.Recordset
DLookup = System.DBNull.Value
On Error GoTo Error_DLookup
dsResult.Open(Source:="SELECT " & dbField & " FROM " & dbRecordset &
" WHERE " & dbCriteria & ";", ActiveConnection:=DBConn,
CursorType:=ADODB.CursorTypeEnum.adOpenDynamic,
LockType:=ADODB.LockTypeEnum.adLockOptimistic)
dsResult.Find(dbCriteria)
If Not (dsResult.BOF And dsResult.EOF) Then
dsResult.MoveFirst()
DLookup = (dsResult(dbField).Value) ' This line is where I'm
having the problem!
End If
DLookup_Exit:
dsResult.Close()
dsResult = Nothing
Exit Function
Error_DLookup:
MsgBox("Error (" & Err.Number & "): " &
ErrorToString(Err.Number) & " in DLookup." , 64)
Resume DLookup_Exit
End Function
The problem is the "DLookup = (dsResult(dbField).Value)" line. Unless I
explicitly place my field name there (instead of dbField), I get an error
3265: Application-defined or object-defined error. In other words, if I
wrote "DLookup = (dsResult("Name").Value" (assuming the field I am
interested in is called "Name"), it works, but this removes the range of the
function significantly. Is there a way to get an ADODB recordset field by
use of a variable?
Richard Mueller
I have used code similar to:
DLookup = dsResult.Fields(dbField).Value
--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
"Brian VanDyke" <bri...@community.nospam> wrote in message
news:%23P7C8w$iGHA...@TK2MSFTNGP02.phx.gbl...
Terry Kreft
Have you tried
DLookup = dsResult(0).Value
You're only pulling 1 field which will always therefore be the zeroth field
in the fields collection.
--
Terry Kreft
"Richard Mueller" <rlmuelle...@ameritech.NOSPAM.net> wrote in message
news:Omu7RYEj...@TK2MSFTNGP03.phx.gbl...
Brian VanDyke
every once in a while when I run through it step by step, but most of the
time I get the error mentioned below. Perhpas myADODB connection is to
blame?
-Brian
"Terry Kreft" <terry...@mps.co.uk> wrote in message
news:O1uPxHGj...@TK2MSFTNGP02.phx.gbl...
Terry Kreft
Well you should be able to get the value using a literal string a literal
long or a variable of either type.
Have you tried checking the connection objects errors collection to see if
there are any other errors or if the description of the error you are
getting is any better.
--
Terry Kreft
"Brian VanDyke" <bri...@community.nospam> wrote in message
news:%23ypSipM...@TK2MSFTNGP05.phx.gbl...
Brian VanDyke
dsResult.Open(Source:="SELECT " & dbField & " FROM " & dbRecordset & " WHERE
" & dbCriteria & ";", ActiveConnection:=DBConn,
CursorType:=ADODB.CursorTypeEnum.adOpenDynamic,
LockType:=ADODB.LockTypeEnum.adLockOptimistic)
...always fails to find any records. The...
If Not (dsResult.BOF And dsResult.EOF) Then
...line ALWAYS returns no records, even though it is not the case. I cannot
seem to figure it out.
-Brian
"Terry Kreft" <terry...@mps.co.uk> wrote in message
news:O1uPxHGj...@TK2MSFTNGP02.phx.gbl...
Brian VanDyke
to go back to a DAO recordset object to avoid this problem (although every
other function that uses a recordset uses ADODB).
Perhaps there is a simpler or more elegant way to retrieve a single value
from a table in a database based on some criteria? Is there a
different/better way to do what I am trying to do?
-Brian
"Terry Kreft" <terry...@mps.co.uk> wrote in message
news:eLp4YuXj...@TK2MSFTNGP04.phx.gbl...
Henning
"Brian VanDyke" <bri...@community.nospam> skrev i meddelandet
news:%23yAN7DZ...@TK2MSFTNGP03.phx.gbl...
> Another problem I'm having, is that the actual open command...
>
> dsResult.Open(Source:="SELECT " & dbField & " FROM " & dbRecordset & "
WHERE
> " & dbCriteria & ";", ActiveConnection:=DBConn,
> CursorType:=ADODB.CursorTypeEnum.adOpenDynamic,
> LockType:=ADODB.LockTypeEnum.adLockOptimistic)
>
> ...always fails to find any records. The...
>
> If Not (dsResult.BOF And dsResult.EOF) Then
You can't have BOF and EOF at the same time, if there are any records.
If Not (dsResult.BOF Or dsResult.EOF) Then
or
If Not(dsResult.BOF) And Not(dsResult.EOF) Then
/Henning
Terry Kreft
Therefore:-
If Not (dsResult.BOF And dsResult.EOF) Then
... is the correct test for records.
Brian,
If I were doing this in classical VB or VBA the code would be as follows:-
Public Function DLookup( _
ByVal dbField As String, _
ByVal dbRecordset As String, _
Optional ByVal dbCriteria As String _
) As Variant
Dim dsResult As ADODB.Recordset
Dim strSQL As String
DLookup = Null
On Error GoTo Error_DLookup
strSQL = "SELECT [" & dbField & "] FROM [" & dbRecordset & "] WHERE " &
dbCriteria
Set dsResult = New ADODB.Recordset
Call dsResult.Open( _
Source:=strSQL, _
ActiveConnection:=DBConn, _
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic _
)
If Not (dsResult.BOF And dsResult.EOF) Then
DLookup = dsResult(dbField).Value ' This line is where I'm having
the problem!
End If
DLookup_Exit:
dsResult.Close
Set dsResult = Nothing
Exit Function
Error_DLookup:
Call MsgBox("Error (" & Err.Number & "): " & ErrorToString(Err.Number) &
" in DLookup.", 64)
Resume DLookup_Exit
End Function
A sample call could be
?DLookup("Value", "tConnect", "Key = 'APP'")
--
Terry Kreft
"Henning" <comput...@coldmail.com> wrote in message
news:448ca9a1$0$28495$57c3...@news3.bahnhof.se...
Kevin Yu [MSFT]
Please also step into the DLookup code to see if dbField has the correct
value.
Kevin Yu
Microsoft Online Community Support
============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)