Search Code

[Luis]

Hello.
I'm trying to optimise a VBA code for search records. I'm using a tipical Do
..... Loop WHILE NOT rs.EOF with a test if the current record matches the
criteria i want and, if so i Exit Do.

This isn't efficient beacuse the table could have more than 1 million records.
The process that i need to do is the following:
1 - Find a record on Table1.Field1 that matches criteria, for example, "abcd".
2 - If record exists update Tabel1.Field2 with a value, let's assume "12345"
3 - If doesn't exist, add a new record with "abcd" in Field1.

Could someone give me a hint on making a portion of VBA code efficient to do
this?

Thanks,

Luis.

 

[John Spencer]

I would not use a record set to do this. I would use SQL statements.

UNTESTED CODE follows.
Assumption: Field1 and Field2 are text fields.

Public Function fAddOrUpdate(strCurrent, strNew)
Dim strSQL as String
Dim dbAny as DAO.Database

StrSQL = "Update SomeTable SET Field2 = """ & strNew & _
""" WHERE Field1 = """ & strCurrent & """ "

Set DbAny = CurrentDb()
dbAny.Execute strSQL

If dbAny.RecordsAffected = 0 Then
StrSQL = "INSERT INTO SomeTable (Field1, Field2) " & _
"Values(""" & strCurrent & """, """ & strNew & """) "
dbAny.Execute strSQL
End if

End Function
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..