使用非重读搜索文本访问数据库查找重读(Unicode)记录

I have an MS Access database table consisting of records of German customers. I have a user form in MS Excel in which I enter a name to search. After I submit the form, it creates a search query in VBA, establishes connection to database and runs the query:

我有一个MS Access数据库表，其中包含德国客户的记录。我在MS Excel中有一个用户表单，在其中输入要搜索的名称。提交表单后，它在VBA中创建一个搜索查询，建立与数据库的连接并运行查询:

SELECT CustomerNumber FROM CustomerTable WHERE (CustomerName LIKE '%loffel%');

The problem is, this customer is recorded in database as 'Löffel'. When I search for 'loffel', it does not return any results.

问题是，这个客户在数据库中被记录为“行李包”。当我搜索“粗呢”时，它不会返回任何结果。

Is there a way to search with Unicode characters and still find results?

是否有一种方法可以使用Unicode字符进行搜索并仍然找到结果?

1 个解决方案

#1

Here is a workaround you might consider. It ain't pretty, but it does seem to work.

这里有一个你可以考虑的变通方法。它不漂亮，但看起来确实有用。

The idea is to let the user enter a search term with no accented characters, and the VBA code will replace the specified unaccented letters with a list of possible variants, e.g. o is replaced by [oö], so

其想法是让用户输入一个没有重音字符的搜索词，VBA代码将用可能的变体列表替换指定的非重音字母，例如o被[oo]取代，所以

... LIKE '%loffel%'

becomes

就变成了

... LIKE '%l[oö]ffel%'

using code like this:

使用下面这样的代码:

Option Explicit

Sub so38010103()
    Dim oChars As String
    ' e.g., U+00F6 is "Latin Small Letter O With Diaeresis"
    oChars = "[o" & ChrW(&HF6) & "]"
    ' (add other accented "o"s above, and other character lists below, as needed)

    'test data
    Const searchFor = "loffel"

    Dim conn As New ADODB.Connection
    conn.Open _
            "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};" & _
            "DBQ=C:\Users\Public\so38010103.accdb"
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT COUNT(*) AS n FROM CustomerTable WHERE (CustomerName LIKE ?)"
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255)

    Dim rst As ADODB.Recordset

    ' test 1: literal search
    cmd.Parameters(0).Value = "%" & searchFor & "%"
    Set rst = cmd.Execute
    Debug.Print rst(0).Value & " record(s) found"  ' 0 record(s) found
    rst.Close

    ' test 2: replace "o" with list of accented variants
    cmd.Parameters(0).Value = "%" & Replace(searchFor, "o", oChars, 1, -1, vbTextCompare) & "%"
    Set rst = cmd.Execute
    Debug.Print rst(0).Value & " record(s) found"  ' 1 record(s) found
    rst.Close

    conn.Close
End Sub

#1