×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Accessing data from ODBC source using VBA?

Accessing data from ODBC source using VBA?

Accessing data from ODBC source using VBA?

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources