What's wrong with my VBA code?

[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:

<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">

Private Sub CommandButton21_Click()</code>
'Create a recordset object.
Dim rec As ADODB.Recordset
Dim my_sql As String

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

'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

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

<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">End Sub

</code>
It stops at

[COLOR=#4F4F4F].Open my_sql, cn, adOpenForwardOnly, adLockReadOnly[/COLOR]

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()
[FONT=tahoma].
.
.
End Sub[/FONT]

 

[khelza]

Ok so apparently it's because my_sql is defined as a string, but it should be an integer? Not sure how to correct that..