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

[khelza]

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! : pray2:

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



Public cn As ADODB.Connection


Sub ADOExcelSQLServer()
.
.
.
End Sub