Dlookup versus SQL

[ChironeX1976]

Hi,

i have to look up a value in a table en put it into another table.
I have two options:
1. i use the dlookup function (sample codes below)
2. i use the command currentdb.openrecordset etc (sample codes below)

From readings on the web i noticed that the Dlookup function is slow for large databases, that is why i looked for an alternative.

Problem:
When i use Dlookup function, and there is no record found (null), then i can make a 0 from it using the Nz function. this works.
The problem with the "currentdb.openrecordset"-way is that this command is unstable when there are no records found... (error number -2147352567)

Question:
What should i do? Should i stick with dlookup, or does anyone have a solution for the case that no records are found with the currentdb.openrecordset method....

* 1. The dlookup code looks like this
Private Sub Vlaremnr_AfterUpdate()
zoekwat = "[VL_DOSJR]"
zoekwaar = "remmi_T_VL_MAIN"
zoekcriteria = "[VL_INT_NR] = " & "'" & Vlaremnr & "'"
VL_DOSJR = zoekresultrol2000(zoekwat, zoekwaar, zoekcriteria)
End Sub

Function zoekresultrol2000(zoekwat, zoekwaar, zoekcriteria) As Variant
zoekresultrol2000 = Nz(DLookup(zoekwat, zoekwaar, zoekcriteria), 0)
End Function

* 2. The CurrentDb.OpenRecordset looks like this
Private Sub Vlaremnr_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCTROW VL_DOSJR FROM remmi_T_VL_MAIN WHERE (((VL_INT_NR)='" & Vlaremnr & "'));"
VL_DOSJR = CurrentDb.OpenRecordset(strSQL)!VL_DOSJR
end sub

 

[Bartuls]

There is a third possibility. From your code I see that you use access.
In this case you should use a 'Toevoeg query' or Insert query.
In access usually that is the best solution to these kind of problems.

A fourth possibility is to compose an insert query in VBA and execute that query.

Dim strSQL as string

strSQL = "INSERT INTO tblYourTable ....."
docmd.runSQL