As already pointed out, you should concatenate the value from the text box to separate strings, for example
Select * from product where product_ref like '%'" + TextBox_ref.Text + "'%'
However, what you really should do is to use paramters,
OleDbParameter Class (System.Data.OleDb)[
^] in this case. The query text would then look like
Select * from product where product_ref like '%' + @searchString + '%'
Now you would add a parameter for the OleDbCommand and set it's value. This would keep you safe from for example SQL injections.
Not sure if you wanted also to find rows having
HHH
even if the data in the database would be
H HH
. If that is the case, you can use REPLACE function to remove whitespace. For example
Select * from product where REPLACE(product_ref, ' ', '') like '%' + @searchString + '%'