Can this code be made more efficient ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
somanybugssolittletimetofixthem said:
I have to run this many thousands of times in a conversion program, is
Click to expand...
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.
 
Back
Top