CodeItBetter Programming Another VB Programming Blog

'Database - How to use ADO to insert records into a database when fields contain quotes
'The program creates a SQL INSERT statement. To protect against quotes in the
'user-entered values, the program replaces each single quote with two single
'quotes. The database replaces the pairs of quotes with single quotes when it
'inserts the values. For example, the database treats the text "O''Toole" as
'"O'Toole" when it adds it to the database.

Private Sub Command1_Click()
    Dim db_file As String
    Dim statement As String
    Dim conn As ADODB.Connection
    Dim ctl As Control
 
    ' Get the data.
    db_file = App.Path
    If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
    db_file = db_file & "people.mdb"
 
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & _
        ";" & "Persist Security Info=False"
    conn.Open
 
    ' Compose the INSERT statement.
    statement = "INSERT INTO Addresses " & "(Name, Street, City, State, Zip) " & " VALUES (" & _
        "'" & Replace$(txtName.Text, "'", "''") & "', " & "'" & _
        Replace$(txtStreet.Text, "'", "''") & "', " & "'" & _
        Replace$(txtCity.Text, "'", "''") & "', " & "'" & _
        Replace$(txtState.Text, "'", "''") & "', " & "'" & _
        Replace$(txtZip.Text, "'", "''") & "'" & ")"
 
    ' Execute the statement.
    conn.Execute statement, , adCmdText
 
    ' Close the connection.
    conn.Close
 
    ' Clear the TextBoxes.
    For Each ctl In Controls
        If TypeOf ctl Is TextBox Then
            ctl.Text = ""
        End If
    Next ctl
End Sub