The inbuilt Replace() function isn’t available in versions of Access prior to A2000, so the following function will handle single quotes for SQL purposes. For versions of Access from A2000 upwards, the Replace() function can be used

For Access versions prior to 2000:

' Code courtesy of UtterAccess Wiki
' http://www.utteraccess.com/wiki/index.php/Category:FunctionLibrary
' Original submission by John White (jwhite)
' Date contributed: 08/26/2010
'
' DESCRIPTION
' Procedure to convert a string with single quote characters to a string with delimited single
' quote characters. Such must be done to strings before appended or updating fields with
' for proper SQL syntax.

Public Function ConvertQuote(strInput As String) As String

'* Note that it calls a ErrorHanlder function to process any error that occurs - not
'* included in the example. MODULE_NAME is the Constant that is set at the top of the
'* code module that I pulled this function from.

   On Error GoTo ConvertQuote_Error
   
   Dim strTemp As String
   Dim intTemp As Integer, i As Integer

   If InStr(1, strInput, "'") > 0 Then
       intTemp = Len(strInput)
       For i = 1 To intTemp
           If Mid$(strInput, i, 1) = "'" Then
               'Convert one single quote (') to two ('')
               strTemp = strTemp & "''"
           Else
               strTemp = strTemp & Mid$(strInput, i, 1)
           End If
       Next i
       
       ConvertQuote = strTemp
   Else
       ConvertQuote = strInput
   End If

ConvertQuote_Cleanup:
   Exit Function

ConvertQuote_Error:
   ErrorHandler MODULE_NAME, "ConvertQuote"
   Resume ConvertQuote_Cleanup
End Function

For Access versions 2000 onwards:

Public Function ConvertQuote(strInput As String) As String

  ConvertQuote = Replace(strInput,"'","''")

End Function