访问VBA，未转义的单引号，Replace（）和null

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.

在Microsoft VBA中处理脚本以获取大量平面数据库并将其拆分为大约20个不同的表。该脚本主要包括打开一个表,检查平面数据库中的每一行以确保它不是重复的,然后添加必要的字段。对每个表重复一遍。

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 * 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.

我第一次运行它一切都很顺利,直到我试图处理O'Malley这个名字。我认为很明显出了什么问题。在Google上快速搜索了这个相关的*帖子。我接受了他们的建议,并在将其输入新表之前将Replace(str,“'”,“''”)添加到每个字段。现在我遇到了一个新问题,谷歌的帮助不大。

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?

替换(null,“'”,“''”)会导致运行时错误,而flat数据库只会出现空值。我可以在每个Replace()调用上面添加一个额外的行来检查IsNull(),如果是这样,将null放入数据库而不是Replace(str,“'”,“''”),尽管我更喜欢一个适合的解决方案如果可能的话,分成一行。有没有更优雅的方法来解决这个难题,还是我需要在我的代码中使用216 If语句?

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.

由于Replace()调用与重复检查内联,如果我改为使用If语句来检查null,那么我必须将结果保存到字符串或更新到flat数据库。返回Replace(str,“'”,“''”)或null而不需要额外变量的函数将是理想的。

3 个解决方案

#1

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

如果你想保持所有内联,你可以使用立即If函数(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.

将空字符串附加到空值会在VBA中返回空字符串,并且不会修改非空字符串。

#3

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

Access'数据库引擎将接受单引号或双引号作为查询中字符串值的分隔符。而不是......

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.

这将允许您处理包含撇号的输入。 OTOH,如果你的字符串输入也包含双引号,这将打破。

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.

我怀疑你可能有更多的事情发生,而不仅仅是撇号问题,但我不明白你的大局。您似乎正在为rstFrom中的每条记录打开第三个DAO记录集,以检查Student表中是否存在匹配项。我会改用DCount()。

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

#1