Using ODBC in VBA (Excel)
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Using ODBC in VBA (Excel)

  1. #1
    ovidiucucu's Avatar
    ovidiucucu is offline Moderator/Reviewer Power Poster
    Join Date
    Feb 2003
    Location
    Iasi - Romania
    Posts
    7,994

    Using ODBC in VBA (Excel)

    I have a MySQL database and a corresponding ODBC data source.
    I have added the ODBC add-in (XLODBC.XLA) to Excel (2000) application.
    Now, I want to connect to database and perform a simple query.

    How to start? What type objects must I declare?
    A simple short example code should be wonderful.

    Thanks,
    Ovidiu

  2. #2
    Join Date
    Nov 2004
    Location
    Poland
    Posts
    1,355

    Re: Using ODBC in VBA (Excel)


  3. #3
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Using ODBC in VBA (Excel)

    Here is an example of connecting to the database using ODBC in Excel. For this open the Macro Editor and goto TOOLS/References Menu...
    For this reference I selected "Microsoft ActiveX Data Objects 2.8" in the references..


    Code:
    Sub Macro1()
        'Declare a Connection object
        Dim cnDB As New ADODB.Connection
    
        'Declare a Recordset Object
        Dim rsRecords As New ADODB.Recordset
    
        'Open the ODBC Connection using this statement
        cnDB.Open "WriteDSNNameHere"
        rsRecords.Open "Select * from TABLENAME", cnDB
    
        'Print the numberof records in A1 cell
        Range("A1").Select
        ActiveCell.FormulaR1C1 = rsRecords.RecordCount
        Range("A2").Select
    
    
        'Close everything and set the references to nothing
        rsRecords.Close
        Set rsRecords = Nothing
        cnDB.Close
        Set cnDB = Nothing
    End Sub

    Hope this helps

  4. #4
    ovidiucucu's Avatar
    ovidiucucu is offline Moderator/Reviewer Power Poster
    Join Date
    Feb 2003
    Location
    Iasi - Romania
    Posts
    7,994

    Re: Using ODBC in VBA (Excel)

    Thank you very much!
    I got the both ideas.
    The first available reputation points will be for you.
    Ovidiu Cucu
    "When in Rome, do as Romans do."
    Visit: Microsoft Virtual Academy
    Follow: https://twitter.com/#!/ovidiucucu
    My blog: http://codexpert.ro/blog/author/ovidiu-cucu/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center