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
Re: Using ODBC in VBA (Excel)
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
Re: Using ODBC in VBA (Excel)
Thank you very much!
I got the both ideas.
The first available reputation points will be for you. :thumb: