title: Excel function with ADODB connection string to Access database
body: <p>I've created below Excel function which connects to an access database with ADODB (approx 10k lines).
  It generally works but there are two main issues:</p>
  
  <ol>
  <li>It is unreliable: often it returns 0 while the result should be different</li>
  <li>It is definitely slow</li>
  </ol>
  
  <p>Any suggestion on how to improve?</p>
  
  <pre><code>Public Function TotaleSQL(Cat As String, SubCat As String, Anno As Integer) As Long
      On Error Resume Next
  
      Dim cn As Object, rs As Object, output As String, sql As String
      Dim src As String
      Dim Total As Long
      Dim CatLong As String
  
      src = "Z:\Report.accdb"
  
      '---Connecting to the Data Source---
      Set cn = CreateObject("ADODB.Connection")
      With cn
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .connectionstring = "Data Source=" &amp; src &amp; ";Persist Security Info=False"
          .Open
      End With
  
  
      '---Run the SQL SELECT Query---
      CatLong = "'" &amp; Cat &amp; ":" &amp; SubCat &amp; "'"
      sql = "SELECT Report.Withdrawal, Report.Deposit, Report.Category, Report.Date FROM Report WHERE (((Report.Category)=" &amp; CatLong &amp; ") AND ((Year([date]))=" &amp; Anno &amp; "));"
  
      'sql = "SELECT * FROM [Sheet1$]"
      Set rs = cn.Execute(sql)
  
      Total = 0
  
      Do
         Total = Total + Val(rs(1) &amp; "") - Val(rs(0) &amp; "")
         rs.Movenext
      Loop Until rs.EOF
  
      '---Clean up---
      rs.Close
      cn.Close
      Set cn = Nothing
      Set rs = Nothing
  
      TotaleSQL = Total
  End Function
  </code></pre>