Macro button to import data from mySQL joined tables into excel based on matching ID

Hey guys,

Trying to create a macro button that, when pressed, populates empty columns, based on a match between Part_ID in excel sheet called BOM and Part_ID in database table called materials joined with tables manufacturers and vendors

The code is as follows:

Code:

---

Private Sub CommandButton21_Click()

'Create a recordset object.
Dim rec As ADODB.Recordset
Dim my_sql As String


Set rec = New ADODB.Recordset
Set cn = New ADODB.Connection


Debug.Print my_sql


'connect to your mysql server
ADOExcelSQLServer


With rec
'Assign the Connection object.
.ActiveConnection = cn


  'This is where my Part_ID column starts
  Range("L6").Select


  'Set Do loop to stop when an empty cell is reached.
  'create the connection to mysql db*


  Do Until IsEmpty(ActiveCell)
    my_sql = "Select manufactures.manufacturer, materials.model_number, vendors.vendor, materials.cost_usd from manufactures, materials, vendors"
    my_sql = my_sql & " where materials.manufacturer = manufactures.manufacturer And materials.alternate_vendor = vendor.ID And materials.cw_id = " & """ & ActiveCell.Value & """


    With rec
    .Open my_sql, cn, adOpenForwardOnly, adLockReadOnly
    End With


    If rec.BOF() = False Then
    'data fill needs to start from column O6
    Range("O" & ActiveCell.Row).CopyFromRecordset rec
    End If
    rec.Close
    'Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
  Loop


'Tidy up
.Close
End With


cn.Close    'close connect to db


End Sub

---

It stops at:

Code:

---

.Open my_sql, cn, adOpenForwardOnly, adLockReadOnly

---

And I can't figure out why

Any feedback would be greatly appreciated! : pray2:

Note: I have another module for the connection which includes:

Code:

---

Public cn As ADODB.Connection


Sub ADOExcelSQLServer()
.
.
.
End Sub

---