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:

 
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:
 
.Open my_sql, cn, adOpenForwardOnly, adLockReadOnly


And I can't figure out why

Any feedback would be greatly appreciated!

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


Public cn As ADODB.Connection


Sub ADOExcelSQLServer()
.
.
.
End Sub