Lecture 20 _ Exercise Manual
1. Please go download a driver (mySQL) from the BB (file named mysql-connector-odbc-5.1.9-win32.msi), and install
2. If you are using other OS, go to http://dev.mysql.com/downloads/connector/odbc/5.1.html and download the correct driver and install
NOTE you need to make sure the appropriate driver is installed. My computer (64bits) though uses 32 bit driver.
3. In Excel VBE, tools->reference->Microsoft ActiveX Data Objects 2.8 Library

4. Now, let's work on the first example, a simple query
1. in module, create a sub procedure as following:

Sub querydata() src = "SELECT * FROM Manufacturers" Dim cntMyConnection As ADODB.Connection Set cntMyConnection = New ADODB.Connection CnctSource = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=YOURDB; Uid=root; pwd=YOURPASSWD; OPTION=3;" cntMyConnection.Open ConnectionString:=CnctSource Dim rstFirstRecordset As ADODB.Recordset Set rstFirstRecordset = New ADODB.Recordset rstFirstRecordset.Open Source:=src, ActiveConnection:=cntMyConnection i = 4 Worksheets("DatabaseConnection").Activate With ActiveSheet .Cells(3, 1) = rstFirstRecordset(0).name .Cells(3, 2) = rstFirstRecordset(1).name While Not rstFirstRecordset.EOF .Cells(i, 1) = rstFirstRecordset(0).Value .Cells(i, 2) = rstFirstRecordset(1).Value rstFirstRecordset.MoveNext i = i + 1 Wend End With Set rstFirstRecordset = Nothing cntMyConnection.Close Set cntMyConnection = Nothing
End Sub
2. now on the spreadsheet, add a button (ActiveX Button, or FORM button, you make the choice), and connect the button to the sub procedure named querydata.

NOTEs
First of all, you need to remember it is connected to the DB in your MySQL... what is the name of your DB you are connecting?
Second, you need to make sure use the correct password you are using in connecting to your mySQL.
Third, in looking at the src = "SELECT * FROM Manufacturers" in the coding, Manufacturers is the table we created in the DB in our earlier mySQL exercise!
Last, in the above coding, Worksheets("DatabaseConnection").Activate, I have the spreadsheet named "DatabaseConnection", if your spreadsheet has different name, do make sure you make necessary changes...
Same Notes apply for the following two sub procedure!

5. Now, let's work on the second example, query on selection criteria
1. create a sub procedure as following

Sub querydata_selectionCriteria() Dim test As String test = InputBox("Please input the selection criteria for manufacturer's name:")

src = "SELECT * FROM Manufacturers where Name =" & "'" & test & "'" Dim cntMyConnection As ADODB.Connection Set cntMyConnection = New ADODB.Connection CnctSource = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=YOURDB; Uid=root; pwd=YOURPASSWD; OPTION=3;" cntMyConnection.Open ConnectionString:=CnctSource Dim rstFirstRecordset As ADODB.Recordset Set rstFirstRecordset = New ADODB.Recordset