Using VBA to pull data from mySQL to excel with Macro button

[khelza]

Hey everyone,

So I have a materials database made up in mySQL, which holds linked tables for materials, manufacturers and vendors.

I also have a Bill of Materials (BOM) in excel, in which there are empty columns for manufacturers, vendors, etc.

The goal is to have a Macro button that, once pressed, it will populate the BOM's empty columns with the appropriate data from the database.

I have very little experience with VBA and macros, so the information I have gathered is all from forums and the web.

In VBA, I have created a module for the connection to the database.

Sub ADOExcelSQLServer()     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
     '
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    Server_Name = "localhost" ' Enter your server name here
    Database_Name = "database" ' Enter your database name here
    User_ID = "rooty" ' enter your user ID here
    Password = "nevergunnagiveyouup" ' Enter your password here
    SQLStr = "SELECT * FROM materials" ' Enter your SQL here
     
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    
End Sub

After getting a few debug errors, I am now able to run it without any errors. Although when I click run, nothing really seems to happen, but I am assuming a connection is being made behind the scenes?

*Not sure if it's useful to mention at this point, I have already set up the ODBC driver and successfully tested the connection to the db, as well as added ActiveX Data Object 2.8 to Reference in VBA

Back in excel, I added a macro button, and inserted the following code:

Private Sub CommandButton21_Click()

' Create a recordset object.
Dim rsMaterialsdb As ADODB.Recordset
Set rsMaterialsdb = New ADODB.Recordset


With rsMaterialsdb
    ' Assign the Connection object.
    .ActiveConnection = cnMaterialsdb
    ' Extract the required records.
    .Open "SELECT * FROM Manufacturers"
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsMaterialsdb
    
    ' Tidy up
    .Close
End With


cnPubs.Close
Set rsMaterialsdb = Nothing
Set cnMaterialsdb = Nothing
                


End Sub

I seem to be getting an error at the .ActiveConnection line and not sure why.

Was wondering if someone could let me know if I have the right idea/heading in the right direction, and possibly, what might be wrong with my code?

Thanks!