Access VBA, unescaped single quotes, Replace(), an

问题:

Working on a script in Microsoft VBA to take a massive flat database and split it between about 20 different tables. The script consists mainly of opening a table, checking every row in the flat database to make sure it's not a duplicate, then adding the necessary fields. Repeat for every table.

The first time I ran it everything was going well until I tried to process the name O'Malley. I think it's obvious what went wrong. A quick search on Google turned up this related StackOverflow post. I took their advice and added Replace(str, "'", "''") to every field before inputting it into the new tables. Now I've run into a new problem and Google is less helpful.

Replace(null, "'", "''") causes a run-time error, and the flat database is just riddled with null values. I can add an extra line above every Replace() call to check IsNull() and if so put null into the database instead of Replace(str, "'", "''"), although I would prefer a solution that can fit into a single line if possible. Is there any more elegant way to solve this dilemma, or will I need 216 If statements in my code?

EDIT -

Another reason that I'm searching for a more elegant solution is my duplicate checking code. At the moment I have something like the following:

        'Check for duplicates
        'Assume student is duplicate if it shares:
        '   (StudentName and School) or SSN
        Set rstDuplicate = CurrentDb.OpenRecordset("select * from Student where (StudentName = '" & Replace(rstFrom("Student").Value, "'", "''") & "' AND School = '" & Replace(rstFrom("School").Value, "'", "''") & "') OR SSN = '" & Replace(rstFrom("Social").Value, "'", "''") & "'")
        If rstDuplicate.RecordCount = 0 Then
            'Duplicate was not found
            rstTo.AddNew
            ' Add fields to the new table
            rstTo.Update
        End If

Since the Replace() calls are inline with the duplicate checking, if I were to instead use If statements to check for null then I would have to either save the result to a string or update to flat database. A function that returns Replace(str, "'", "''") OR null without the need for extra variables would be ideal.

回答1:

If you want to keep everything inline, you can use an immediate If function (IIf):

IIf(IsNull(rstFrom("Student").Value), " Is Null", "= " & Replace(rstFrom("Student").Value)

That will be a nightmare to read and maintain, though. You are better off writing your own function to handle the change in comparison operator as well as the apostrophe escaping:

Function CompFld(Val As Variant) As String
    If IsNull(Val) Then
        CompFld = " Is Null "
    Else
        CompFld = "= '" & Replace(Val, "'", "''") & "' "
    End If
End Function

Use it as so:

Dim SQL As String
SQL = "SELECT * FROM Student " & _
      "WHERE (StudentName " & CompFld(rstFrom("Student").Value) & " AND " & _
      "       School " & CompFld(rstFrom("School").Value) & ") " & _
      "   OR (SSN " & CompFld(rstFrom("Social").Value) & ") "
Set rstDuplicate = CurrentDb.OpenRecordset(SQL)
If rstDuplicate.RecordCount = 0 Then
    'Duplicate was not found
    rstTo.AddNew
    ' Add fields to the new table
    rstTo.Update
End If

回答2:

A terse, yet ugly little gem handed down to me from ages ago:

Replace(str & "", "'", "''")

Appending an empty string to a null value returns an empty string in VBA, and won't modify a non-empty string.

回答3:

Access' database engine will accept either single or double quotes as delimiters for string values in queries. So instead of ...

SELECT * FROM Student WHERE StudentName = 'O''Malley'

... you can do this ...

SELECT * FROM Student WHERE StudentName = "O'Malley"

That would allow you to handle inputs which contain apostrophes. OTOH, if your string inputs also contain double quotes, this will break.

I suspect you may have more going on than just the apostrophe issue, but I don't understand your big picture. You seem to be opening a third DAO recordset for each record in rstFrom, to check whether a match exists in the Student table. I would use DCount() instead.

Dim strCriteria As String
strCriteria = "StudentName = ""O'Malley"" AND School = ""foo"""
If DCount("*", "Student", strCriteria) = 0 Then
    'no match found --> add it '
End If