title: Get MySQL database table row count on Excel as a VBA variable without loading the table on the sheet
body: <p>I have a table on an Excel sheet with external data from a MySQL database (using the <a href="https://dev.mysql.com/doc/connector-odbc/en/" rel="nofollow noreferrer">MySQL Connector</a>. I'm going to create a form so the user can pick a date range of data to load but before starting loading I want to tell how many rows are getting loaded with <code>SELECT COUNT(*) FROM tableName WHERE date BETWEEN start AND end</code>.</p>
  
  <p>I can do something like this:</p>
  
  <pre><code>With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};UID=user;PWD=user;SERVER=xxx.xxx.xx.xxx;DATABASE=dbName;PORT=3306;", Destination:=Range("$A$1")).QueryTable
    .CommandText = Array("SELECT COUNT(*) FROM tableName")
    .Refresh
  End With
  </code></pre>
  
  <p>And then get the amount of rows from the cell A2 but what I want is to know if I can only create the connection on VBA without loading the table on the sheet and have the result in a variable. Not looking to load on the sheet, get the value from it and then delete the table.</p>
  
  <p>Somehow changing the destination part to a variable or doing something like this (can't get it to work):</p>
  
  <pre><code>Dim Cn As ADODB.Connection 'or As ODBC.Connection
  Set Cn = New ADODB.Connection 'or New ODBC.Connection
  Cn.Open With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};UID=user;PWD=user;SERVER=xxx.xxx.xx.xxx;DATABASE=dbName;PORT=3306;"
  '...
  </code></pre>
  
  <p><strong>Edit:</strong></p>
  
  <p>For future reference (mostly once I forget and need this again) download <a href="https://dev.mysql.com/downloads/connector/odbc/5.3.html" rel="nofollow noreferrer">MySQL Connector/ODBC</a> preferably the 32-bit version as the 64-bit version might not show the driver (<code>MySQL ODBC 5.3 Unicode Driver</code>) on Excel/Microsoft Query's list (it did show on <code>C:\WINDOWS\syswow64\odbcad32.exe</code> though). Then on VBA, menu/dropdown <code>Tools</code>, option <code>References...</code>, check <code>Microsoft ActiveX Data Objects 6.1 Library</code> (or the latest version available) and use the code from the <a href="https://stackoverflow.com/a/49287995">answer</a> like this (change at least <code>dbName</code> and <code>tableName</code>):</p>
  
  <pre><code>Sub Test()
    Dim c As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim sq As String
    Dim strConnectionString As String
    Dim result As Long
    strConnectionString = "DRIVER={MySQL ODBC 5.3 Unicode Driver};UID=root;PWD=;SERVER=127.0.0.1;DATABASE=dbName;PORT=3306;"
    sq = "SELECT Count(*) As row_count FROM tableName;"
    Set c = New ADODB.Connection
    c.Open strConnectionStringstring
    Set rst = c.Execute(sq)
    result = rst!row_count
    c.Close
    'MsgBox c.Errors
    MsgBox result
  End Sub
  </code></pre>
  
  <p><strong>Another edit:</strong></p>
  
  <p>Code compacted:</p>
  
  <pre><code>Sub Test()
    Dim conn As ADODB.Connection
    On Error GoTo ErrorHandler
    conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};UID=user;PWD=user;SERVER=192.168.20.198;DATABASE=dbName;PORT=3306;"
    MsgBox conn.Execute("SELECT COUNT(*) As row_count FROM tableName;")!row_count
    conn.Close
    On Error GoTo 0
    Exit Sub
  ErrorHandler:
    MsgBox "An error has occurred while connecting to the server. Verify your internet connection." &amp; Chr(10) &amp; Chr(10) &amp; "Error:" &amp; Chr(10) &amp; Err.Number &amp; Chr(10) &amp; Err.Source &amp; Chr(10) &amp; Err.Description
  End Sub
  </code></pre>
  
  <p><code>!row_count</code> can be changed to <code>.Fields("row_count")</code></p>
  
  <p><code>conn.Execute("SELECT COUNT(*) As row_count FROM tableName;")!row_count</code> can be changed to <code>conn.Execute("SELECT COUNT(*) FROM tableName;").Fields("COUNT(*)")</code></p>