Accessing data from ODBC source using VBA?

[SabreWolf3]

Hi all,

I am new to VBA coding and am working on a project that will save me a TREMENDOUS amount of time. I currently use MSQuery to query an ODBC data source (a RedBrick 32 db). The problem is, when I try to write advanced queries, I lose some functionality in MSQuery; for example, the ability to set up parameters and have my user prompted for values.

To solve this, I would like to implement a script where I can write my own SQL statement using values I received from prompts, and submit it directly to the database. I have found some samples online using DAO, but I can't seem to make any of them work correctly.

Here is the code I am currently using, but with a simple query in place of the complex ones I hope to use soon:

Sub dbconnect()
Dim wkrJet As DAO.Workspace, wrkODBC As DAO.Workspace
Dim conODBCDirect As DAO.Connection
Dim rsODBCDirect As DAO.Recordset
Dim strConn As String

strConn = "ODBC;DATABASE=dbname;UID=admin;PWD=password;DSN=dsnname;"

Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set conODBCDirect = wrkODBC.OpenConnection("", , , strConn)
sqlstr = "SELECT * FROM SALES"

Set rs = conODBCDirect.OpenRecordset(sqlstr, dbOpenDynamic)

Set objXL = New Excel.Application
    With objXL
    .Visible = True
    Set objWkb = .Workbooks.Add
    Set objSht = objWkb.Worksheets(1)
    With objSht
    
objSht.Range("A2").CopyFromRecordset rs
    End With
    End With

'close recordset
rs.Close
wrkODBC.Close
Set rsODBCDirect = Nothing
Set wrkODBC = Nothing
Set conODBCDirect = Nothing
End Sub

This code will work. I get my results. However, if I change the sqlstr to this:

sqlstr = "SELECT * FROM SALES ORDER BY SALES_ID"

Then, I get an error message. "ODBC call failed." I get this error message whenever I use anything but a simple "SELECT *" statement.

Does anyone have any ideas? Or a way to improve my code?

Thanks,
Richard