Excel VBA Querying SQL Server including Sheet Cell Reference in Query

Good Afternoon, Everyone! I'm trying to put together a workbook that queries a SQL server and have been successful in using the original vb code, but when I try to include a 'WHERE' in the SQL statement, it doesn't work. Here is what I have that works and returns all records. I have tried multiple ways of putting a 'WHERE' clause after 'FROM table' with all of them failing. What I'm trying to accomplish is, in Sheet1 of my workbook, I have Cell B4 that I would like the SQL statement to reference; (i.e. return records 'WHERE name = B4). I greatly appreciate anyone and everyone's help!

This code, immediately below, works to return all records from the table.

Private Sub RunQuery_Click()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=SERVERNAME\SQLEXPRESS;" & _
                  "Initial Catalog=CATALOG;" & _
                  "Integrated Security=SSPI;"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT name, description, comment FROM table;")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(2).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub

I've tried, to no avail:

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = B1 ;")

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = B1 ")

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = " & B1 & ";")

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = " & B1 & ")

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = " & Range("B1") & ";")

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = " & B1 & ";")

Set rs = conn.Execute("SELECT name, description, comment FROM products WHERE name = "  & Sheets("Sheet1").Range("B4").Value & ";")