Can this code be made more efficient ?

[somanybugssolittletimetofixthem]

I have to run this many thousands of times in a conversion program, is this the most efficient way to do it? I was unsure if I need to close the recordset before I do the INSERT, or if I can just leave it open. Thanks.

Dim strSQL As String
KW = Replace(KW, "'", "''")
strSQL = "SELECT ID FROM tblKeywords WHERE UCase(KWName) = '" & UCase(KW) & "'"
Call rsKW.Open(strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly)
If rsKW.EOF Then
rsKW.Close
strSQL = "INSERT INTO tblKeywords (KWName) VALUES ('" & KW & "')"
CurrentProject.Connection.Execute (strSQL)
Else
rsKW.Close
End If

 

[Ron Weiner]

You don't say where KW comes from. If KW comes from another Table then you
could make a query that used a OUTER join that table to tblKeywords and in a
single Insert Into you could add all of the missing values at one time.
This would MUCH faster than the iterative process you are using below.

If you will supply the DDL and some sample data I am sure someone here will
be able to help you write the Query.

Ron W

I have to run this many thousands of times in a conversion program, is

this the most efficient way to do it? I was unsure if I need to close the
recordset before I do the INSERT, or if I can just leave it open. Thanks.