Why is my SQL Updating Code so Slow 2

[JoshuaBloom]

Im doing what can be considered a Mail Merge. Taking Field values from one table using a Replace Function and then inserting the results into another table using a
doCmd.RunSql ("Insert ...&quot
My throughput is about 2000 insertions a minute, does anyone think this can be speeded up, it seems very slow to me.

Thank you for your Help.
Josh

The code looks like this.

Public rst, rst2 As DAO.Recordset
Public fld2 As DAO.Field
Public Paragraph As String

Sub Insert()
Dim TblName As String
TblName = ContactTableName.Value

Set rst = CurrentDb.OpenRecordset("Select * from [" & TblName & "] Where [prefered_city] Is Not Null AND [city_alt1] Is Not Null AND [city_alt2] Is Not Null", dbOpenForwardOnly)
InsertDIExportTable 3, (TblName)
End Sub

Public Sub InsertDIExportTable(city_num As Integer, TblName As String)
Dim fld As DAO.Field
Dim email, FirstName, last_name, city_primary, st_primary, AltCity1, AltCity2, CID, Paragraph, EventParagraph As String

Do Until rst.EOF
For Each fld In rst.Fields
If fld.Name = "email" Then
email = fld.Value
ElseIf fld.Name = "first_name" Then
FirstName = fld.Value
ElseIf fld.Name = "last_name" Then
LastName = fld.Value
ElseIf fld.Name = "prefered_city" Then
city_primary = fld.Value
ElseIf fld.Name = "prefered_state" Then
st_primary = fld.Value
ElseIf fld.Name = "city_Alt1" Then
AltCity1 = fld.Value
ElseIf fld.Name = "city_Alt2" Then
AltCity2 = fld.Value
ElseIf fld.Name = "SecretID" Then
CID = fld.Value
End If
Next

Paragraph = ParaHandler(city_num, city_primary, AltCity1, AltCity2)
EventParagraph = SqlWrap(Paragraph)

email = SqlWrap(email)
FirstName = SqlWrap(FirstName)
LastName = SqlWrap(LastName)
city_primary = SqlWrap(city_primary)
st_primary = SqlWrap(st_primary)
CID = SqlWrap(CID)


sqlstatement = "INSERT INTO " & Me.ExportTable.Value & " (EmailAddress,FirstName,LastName,EventParagraph,city_primary,st_primary,CID)" _
& "VALUES (" & email & "," & FirstName & "," & LastName & "," & EventParagraph & "," & city_primary & "," & st_primary & "," & CID & &quot"
DoCmd.RunSQL (sqlstatement)
rst.MoveNext
Loop
End Sub

Private Function ParaHandler(city_num As Integer, city_primary As Variant, city_alt1 As Variant, city_alt2 As Variant)
If city_num = 3 Then
Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}. Upcoming seminars are also scheduled for {city_alt1} and {city_alt2}:", "{city_primary}", city_primary, , , vbTextCompare)
Paragraph = Replace(Paragraph, "{city_alt1}", city_alt1, , , vbTextCompare)
Paragraph = Replace(Paragraph, "{city_alt2}", city_alt2, , , vbTextCompare)
ParaHandler = Paragraph
ElseIf city_num = 2 Then
Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}. Upcoming seminars are also scheduled for {city_alt1}:", "{city_primary}", city_primary, , , vbTextCompare)
Paragraph = Replace(Paragraph, "{city_alt1}", city_alt1, , , vbTextCompare)
ParaHandler = Paragraph
ElseIf city_num = 1 Then
Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}:", "{city_primary}", city_primary, , , vbTextCompare)
ParaHandler = Paragraph
End If

End Function

Private Function SqlWrap(Txt)
If IsNull(Txt) Then
SqlWrap = "'" & Txt & "'"
Else
Txt = Replace(Txt, "'", "''", , , vbTextCompare) 'Handling for ' in the String
SqlWrap = "'" & Txt & "'"
End If
End Function

 

[mp9]

Here's my version of your code, which may or may not be faster but is certainly worth a try:

Public rst, rst2 As DAO.Recordset
Public fld2 As DAO.Field
Public Paragraph As String

Sub Insert()
Dim TblName As String
    TblName = ContactTableName.Value
    
    Set rst = CurrentDb.OpenRecordset("Select * from [" & TblName & "] Where [prefered_city] Is Not Null AND [city_alt1] Is Not Null AND [city_alt2] Is Not Null", dbOpenForwardOnly)
    InsertDIExportTable 3, (TblName)
End Sub
    
Public Sub InsertDIExportTable(city_num As Integer, TblName As String)
    Dim email, FirstName, last_name, city_primary, st_primary, AltCity1, AltCity2, CID, Paragraph, EventParagraph As String

    Do Until rst.EOF
        With rst  
            email = .email
            FirstName = .first_name
            LastName = .last_name
            city_primary = .prefered_city
            st_primary = .prefered_state
            AltCity1 = .city_Alt1
            AltCity2 = .city_Alt2
            CID = .SecretID
        End With
        
        Paragraph = ParaHandler(city_num, city_primary, AltCity1, AltCity2)
        EventParagraph = SqlWrap(Paragraph)

        email = SqlWrap(email)
        FirstName = SqlWrap(FirstName)
        LastName = SqlWrap(LastName)
        city_primary = SqlWrap(city_primary)
        st_primary = SqlWrap(st_primary)
        CID = SqlWrap(CID)

         
        sqlstatement = "INSERT INTO " & Me.ExportTable.Value & " (EmailAddress,FirstName,LastName,EventParagraph,city_primary,st_primary,CID)" _
            & "VALUES (" & email & "," & FirstName & "," & LastName & "," & EventParagraph & "," & city_primary & "," & st_primary & "," & CID & ");"
        DBEngine(0)(0).Execute sqlstatement
    rst.MoveNext
    Loop
End Sub

Private Function ParaHandler(city_num As Integer, city_primary As Variant, city_alt1 As Variant, city_alt2 As Variant)
        If city_num = 3 Then
            Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}. Upcoming seminars are also scheduled for {city_alt1} and {city_alt2}:", "{city_primary}", city_primary, , , vbTextCompare)
            Paragraph = Replace(Paragraph, "{city_alt1}", city_alt1, , , vbTextCompare)
            Paragraph = Replace(Paragraph, "{city_alt2}", city_alt2, , , vbTextCompare)
            ParaHandler = Paragraph
        ElseIf city_num = 2 Then
            Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}. Upcoming seminars are also scheduled for {city_alt1}:", "{city_primary}", city_primary, , , vbTextCompare)
            Paragraph = Replace(Paragraph, "{city_alt1}", city_alt1, , , vbTextCompare)
            ParaHandler = Paragraph
        ElseIf city_num = 1 Then
            Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}:", "{city_primary}", city_primary, , , vbTextCompare)
            ParaHandler = Paragraph
        End If
        
End Function

Private Function SqlWrap(Txt)
    If IsNull(Txt) Then
        SqlWrap = "'" & Txt & "'"
    Else
        Txt = Replace(Txt, "'", "''", , , vbTextCompare)  'Handling for ' in the String
    SqlWrap = "'" & Txt & "'"
    End If
End Function

It's untested, but I hope it helps.

http://www22.brinkster.com/accessory/

 

[JoshuaBloom]

Thanks for the help mp9.
The DBEngine(0)(0).Execute sqlstatement is definitely faster than RunSql. I didnt know you could use that.

The with statement is giving me problems for some reason though
With rst
email = .email
Is highlighting .email and saying
"Compile error:
Method or data member not found"
Any idea why that is happening?
Thanks Again
Joshua Bloom

 

[mp9]

Well, you could try takin that whol block out of the With loop, so you' just have this instead:

email = rst.email
FirstName = rst.first_name
LastName = rst.last_name
city_primary = rst.prefered_city
st_primary = rst.prefered_state
AltCity1 = rst.city_Alt1
AltCity2 = rst.city_Alt2
CID = rst.SecretID

If this still falls over, could the e-mail field in the recordset be named something else other than "email"?

 

[jrbarnett]

You can simplify the SQLWrap function thus:

Private Function SqlWrap(Txt)
    SqlWrap = Replace (txt & "", "'", "''")
End Function

The & "" after the txt in the Replace call is essential - as it means that replace won't fall over if txt is Null (as it is treated as an empty string), and gets around the need to call the if isnull and all the rest of it.

John

 

[JoshuaBloom]

Thanks guys,
I had already taken out the with statement and that worked with the same field names, I was hoping you might have an idea why the With statement wasnt working, maybe pointing me to a bad reference or something like that.

jbarnett thats a much cleaner WrapFunction, thank you.

Josh

 

[jrbarnett]

The dot notation in the with statement refers to properties of the object. In order to refer to fieldnames, you need to use the Fields collection of the object, or the caution mark thus:

email = rst!email

or
email = rst.Fields ("email&quot

you will be fine.

John

 

[mp9]

And here's a marginally faster version of your ParaHandler function:

Private Function ParaHandler(city_num As Integer, city_primary As Variant, city_alt1 As Variant, city_alt2 As Variant)
    If city_num = 3 Then
        Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}. Upcoming seminars are also scheduled for {city_alt1} and {city_alt2}:", "{city_primary}", city_primary, , , vbTextCompare)
        Paragraph = Replace(Paragraph, "{city_alt1}", city_alt1, , , vbTextCompare)
        ParaHandler = Replace(Paragraph, "{city_alt2}", city_alt2, , , vbTextCompare)
    ElseIf city_num = 2 Then
        Paragraph = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}. Upcoming seminars are also scheduled for {city_alt1}:", "{city_primary}", city_primary, , , vbTextCompare)
        ParaHandler = Replace(Paragraph, "{city_alt1}", city_alt1, , , vbTextCompare)
    ElseIf city_num = 1 Then
        ParaHandler = Replace("Click below to register, get more info, or view the schedule of events in {city_primary}:", "{city_primary}", city_primary, , , vbTextCompare)
    End If    
End Function

Also, you should consider (if you haven't already) reordering your (Else)If's so that they are in the order of their probability.

Hope this helps.

http://www22.brinkster.com/accessory/