To include single quotation marks in the text of an insert statement, you need to replace the single quotation mark with two single quotation marks. (NOT a double quote - thats a single charactor, but two single quotes with no seperation)
The following
INSERT INTO [BASE_TABLE]([C1]) VALUES('Joe''s Bar')
will work. Add code to the macro to replace ' with '' as you build the insert statement and you should be back in business