CODEWITHSUNDEEP
vbaexcelms-accessunionado
ch-pub
ch-pub

Reputation: 1724

Excel VBA to Execute Stored UNION Query from MS Access 2003

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 UNION. For example, I'm using the following code to execute the stored queries:

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

If testQuery is a simple query (e.g., does not use UNION), then data is returned as expected. However, if the testQuery contains a UNION, 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.

Upvotes: 1

Views: 1368

Answers (2)

user5656611
user5656611

Reputation:

Are you still struggling with this? Try using an ODBC Query. Follow the steps listed here.

http://translate.google.pl/translate?js=n&prev=_t&hl=pl&ie=UTF-8&layout=2&eotf=1&sl=pl&tl=en&u=http%3A%2F%2Fafin.net%2FKsiazkaSQLwExcelu%2FGraficznyEdytorZapytanSqlNaPrzykladzieMsQuery.htm

Make sure you have no nulls in your Union and if you do, you must use the NZ Function to convert nulls into zeros.

Upvotes: 1

Parfait
Parfait

Reputation: 107567

Consider using ADO's Open Recordset method. Usually, Execute are for action commands (append/update/delete, stored procedures, etc.). Additionally, if Execute returns a recordset it is only a forward-only (i.e, snapshot with no cursor), read-only recordset without the facilities of MoveNext, RecordCount, Update.

Dim cn As Object
Dim rst As Object

Dim strConnection As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' 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
' Open the recordset
rst.Open "testQuery", cn

Sheets(1).Range("A2").CopyFromRecordset rst

' Close recordset and db connection
rst.Close
cn.Close

Upvotes: 1

Related Questions