When to open a dbase connection?

Results 1 to 3 of 3

Thread: When to open a dbase connection?

  1. #1
    Join Date
    Dec 1969

    Default When to open a dbase connection?

    The script below opens a pop3 mailbox, and if a mail in it is one that has bounced back to me, puts it in a list of emails addresses to be deleted from my dbase.<BR><BR>Barring any debugging it should work... But my question is "When should I open the dabse connection?" At present I open it at the start, loop through all the messages and close it again.<BR><BR>Is that the most efficient way to do it, bearing in mind I might be workng with thousands of emails...?<BR><BR>Thanks!<BR><BR><BR><BR>&#060;HTM L&#062;<BR>&#060;!--#include file="DatabaseConnect.asp"--&#062; <BR>&#060;!-- #include file="adovbsSHORT.inc" --&#062;<BR>&#060;%<BR><BR>Server.ScriptTimeout = 30000<BR><BR>Set Mailer = Server.CreateObject("POP3svg.Mailer")<BR>Mailer.Re moteHost = "isp.net"<BR>Mailer.UserName = "username"<BR>Mailer.Password = "password"<BR><BR><BR>Set objRec = Server.CreateObject("ADODB.Recordset")<BR><BR>objR ec.Open "ToDelete", objConn, , adLockOptimistic, adCmdText<BR><BR><BR><BR>Mailer.GetPopHeaders<BR>< BR>Mailer.OpenPop3<BR><BR>For x = 1 to Mailer.MessageCount<BR><BR>&#039; Retrieves email to memory<BR>strMsg = Mailer.RetrieveToMemory(x)<BR>strSubject = Mailer.Subject<BR><BR> <BR> If Instr(UCase(strSubject), "FAIL")&#062;0 OR Instr(UCase(strSubject), "USER UNKNOWN")&#062;0 OR Instr(UCase(strSubject), "DELIVER")&#062;0 Then<BR> &#039; It failed... <BR><BR> &#039;Response.write("This failed: ")<BR> &#039;Response.write strSubject<BR> &#039;Response.write("<BR>")<BR><BR> If Instr(strMsg, "Bounce-From: ") &#062; 0 then<BR> &#039;Its bounced<BR> <BR> intStart = InStr(strMsg, "Bounce-From: ") + 13 <BR><BR> &#039;13 moves the start point to the first letter of the email address<BR> intEnd = InStr(intStart, strMsg, vbCr)<BR> strBounceFrom = Mid(strMsg, intStart, intEnd - intStart)<BR><BR> strBounceFrom = Trim(strBounceFrom)<BR><BR><BR> &#039;Response.write("This failed and bounced: ")<BR> &#039;Response.write strBounceFrom<BR> &#039;Response.write("<BR>")<BR><BR> objRec.AddNew<BR> objRec("Email")= strBounceFrom<BR> objRec.Update<BR> y=y+1<BR> End if<BR><BR> &#039; Delete whether its in the dbase or not<BR><BR> Mailer.Delete(x)<BR> End if<BR>Next<BR>Mailer.ClosePop3<BR><BR>objRec.Close <BR>set objRec = Nothing<BR>objConn.Close<BR>set objConn = Nothing<BR><BR>Response.write("All done!")<BR>Response.write(" ")<BR>Response.write y<BR>Response.write(" to be deleted")<BR>%&#062;<BR>&#060;/HTML&#062;

  2. #2
    Join Date
    Dec 1969

    Default RE: When to open a dbase connection?

    Well, looks good to me. The only thing I can suggest is that you investigate ADODB.RecordSet.BatchUpdate or UpdateBatch (can never remember!).<BR><BR>Or do Insert statements...<BR><BR>Craig.

  3. #3
    Join Date
    Dec 1969

    Default On balance it's probably better to...

    ...open the connection once at the beginning, keep it open during the loop and then close it again at the end. If your loop finishes in good time, then it&#039;s most likely the most efficient option.<BR><BR>Oliver.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts