Using ODBC in VBA (Excel)

[ovidiucucu]

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

[Krzemo]

Look here:
http://support.microsoft.com/default...b;en-us;124218

Best regards,
Krzemo.

[Shuja Ali]

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..

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

[ovidiucucu]

Thank you very much!
I got the both ideas.
The first available reputation points will be for you.