title: New ODBC issue Access 2007
body: <p>I have an MS Access 2007 front end to a MS SQL Server 2008R2 back end.</p>
  
  <p>I've got a query that's part of a loop, and it's suddenly started generating <code>Run-time error '3146': ODBC--call failed.</code> errors on one query as the loop itterates. Oddly, it's not the first iteration.  EDIT: When I say suddenly, it's been running like this just fine for several months (probably since March).</p>
  
  <pre><code>Set db = CurrentDb
  db.QueryTimeout = 480 'thought there was a timeout issue, so set this very high
      Set SupvRS = db.OpenRecordset("SELECT DISTINCT tblProcessors.Supervisor, tblProcessors.SupervisorEmail " &amp; _
          "  FROM tblProcessors INNER JOIN (tblAuditPr INNER JOIN tblAuditPr_A ON tblAuditPr.PrAudit_ID = tblAuditPr_A.PrAudit_ID)" &amp; _
                              " ON tblProcessors.Processor = tblAuditPr_A.Processor" &amp; _
          " WHERE tblProcessors.Supervisor IS NOT NULL " &amp; _
          "   AND tblAuditPr.EndDate BETWEEN " &amp; GetSQLDate(Me.txtFrom) &amp; " AND " &amp; GetSQLDate(Me.txtTo) &amp; _
          "   AND tblProcessors.Processor&lt;&gt;'Default Processor' " &amp; _
          "   AND tblAuditPr_A.Answer Not In ('NA','NF')" &amp; _
          "   AND tblAuditPr.Status = 'Submitted'")
      Do While Not SupvRS.EOF
          'Send Supervisor Email
          Attachment = PARG.GenerateAuditReport(SupvRS.Fields("Supervisor"), parSupervisor, Me.txtFrom, Me.txtTo)
          Set EmailRS = db.OpenRecordset("SELECT * FROM tblProcessors WHERE Supervisor = " &amp; GetSQLString(SupvRS.Fields("Supervisor")))
          If EmailRS.EOF Then
              Err.Raise -234923, Description:="Cannot find tblProcessors record for " &amp; SupvRS.Fields("Supervisor") &amp; "."
          End If
          If IsNull(EmailRS.Fields("SupervisorEmail")) Then
              MailTo = &lt;redacted&gt;
          Else
              MailTo = EmailRS.Fields("SupervisorEmail")
          End If
          Set EmailRS = Nothing
          Mailer.AddMailDocument MailTo:=MailTo, _
              Subject:=Subject, _
              Body:=Body, _
              Attachments:=Attachment, _
              From:=&lt;redacted&gt;
          'Iterate processor emails if requested.
          If Me.chkProcessor Then
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  '----------------------------------------------------------------------------------------
  'ERROR occurs here, but only after several supervisors have successfully passed through
              Set ProcRS = db.OpenRecordset("SELECT DISTINCT tblProcessors.Processor, tblProcessors.ProcessorEmail FROM tblAuditPr" &amp; _
                  " INNER JOIN (tblProcessors INNER JOIN tblAuditPr_A ON tblProcessors.Processor = tblAuditPr_A.Processor) " &amp; _
                  " ON tblAuditPr.PrAudit_ID = tblAuditPr_A.PrAudit_ID" &amp; _
                  " WHERE tblProcessors.Supervisor = " &amp; GetSQLString(SupvRS.Fields("Supervisor")) &amp; _
                  " AND tblAuditPr.EndDate BETWEEN " &amp; GetSQLDate(Me.txtFrom) &amp; " AND " &amp; GetSQLDate(Me.txtTo))
  '----------------------------------------------------------------------------------------
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
              Do While Not ProcRS.EOF
                  If Not IsNull(ProcRS.Fields("ProcessorEmail")) Then
                      On Error Resume Next
                      Attachment = PARG.GenerateAuditReport(ProcRS.Fields("Processor"), parProcessor, Me.txtFrom, Me.txtTo)
                      If Attachment &lt;&gt; "" Then
                          MailTo = ProcRS.Fields("ProcessorEmail")
                          Mailer.AddMailDocument MailTo:=MailTo, _
                              Subject:=Subject, _
                              Body:=Body, _
                              Attachments:=Attachment, _
                              From:=&lt;redacted&gt;
                      Else
                          Err.Clear
                      End If
                      On Error GoTo 0
                  End If
                  ProcRS.MoveNext
              Loop
              Set ProcRS = Nothing
          End If
          SupvRS.MoveNext
      Loop
  </code></pre>
  
  <p>As noted in the code, the error occurs on an inner loop that successfully executes for other supervisors. It does consistantly break on one particular individual. Using the <code>GetSQLString</code> and <code>GetSQLDate</code> functions (returns ' delimited, double '' when necessary, pretty strings, and # delimited dates, respectivly, for passing Access queries through to SQL Server) in break mode, I duplicated the exact query that was giving me problems in an Access query. It returned a timeout error, but changing the database default timeout (and closing/opening the db) didn't resolve the issue. I pasted the exact same query into SSMS (replacing the # date delimiters with ') and it executed just fine there (taking about 1:30-2:00 to execute each try).</p>
  
  <p>Taking a pause in typing up this post, I put some error trapping in to see if I could get the actual error message SQL Server was returning, when it ran without error. To me, that indicates that it is likely a time out issue. Anyone have a more educated guess as to what it actually is, or a suggestion on how to make the query more efficient so it doesn't time out?</p>