Ok, I struggled with this for a while when I was trying to use the SQL to insert the record. I ended up just using the VB to insert the record, but if you absolutely have to do it with SQL, I believe that you have to either send your string to SQL in double quotes (which i think only works with some SQL clients) or the other option would be to pass your string to a fuction first and check each character to see if it is a ' and if it is prefix it with another '.
Preprocessing the string to handle the apostrophes is the easy answer to this specific problem (a short function to scan the string and prefix any apostrophe with another apostrophe). Using ADO instead of writing your own INSERT or UPDATE statements is an easy way to get this functionality automagically.
Thinking in more generic terms, I think you are looking for VB's ON ERROR statement.