title: Excel VBA to Execute Stored UNION Query from MS Access 2003
body: <p>I'm writing a function in Excel VBA to execute a stored queries on a database created using MS Access 2003. The code works when the stored query is a simple query, but NOT if the query is a <code>UNION</code>. For example, I'm using the following code to execute the stored queries:</p>
  
  <pre><code>Public Function QueryDB()
  
      Dim cn As Object
  
      Dim strConnection As String
      Set cn = CreateObject("ADODB.Connection")
  
      ' Hard code database location and name
      strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\server1\myDatabase.mdb"
  
      ' Open the db connection
      cn.Open strConnection
  
      ' Create call to stored procedure on access DB
      Dim cmd As Object
      Set cmd = CreateObject("ADODB.Command")
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "testQuery"
      cmd.ActiveConnection = cn
  
      ' Execute stored query
      Dim rs As ADODB.Recordset
      Set rs = cmd.Execute()
  
      MsgBox rs.Fields(0) ' prints as expected
  
      QueryDB2 = rs.Fields(0)
  
  
      ' close connections
      rs.Close
      Set rs = Nothing
      cn.Close
      Set cn = Nothing
  
  End Function
  </code></pre>
  
  <p>If <code>testQuery</code> is a simple query (e.g., does not use <code>UNION</code>), then data is returned as expected. However, if the <code>testQuery</code> contains a <code>UNION</code>, then the Excel VBA code fails (and no particular error is returned). How can I work-around this issue? I'd like to avoid writing the SQL statement in VBA.</p>