ADO（Excel，ACE OLEDB 12.0）不比较Key-Strings区分大小写 [英] ADO (Excel, ACE OLEDB 12.0) does not compare Key-Strings case-sensitive
问题描述
问题描述：当使用Excel和ADO（ACE OLEDB 12.0）在字母数字字符串上连接2个表时，ADO不区分键a12a和A12a（它将它们视为相同，即不区分大小写）。但是，我的数据中有字母数字键。加入将错误地链接数据！
Problem Description: When Joining 2 Tables using Excel and ADO (ACE OLEDB 12.0) on alphanumeric Strings, ADO does not distinguish between the Keys "a12a" and "A12a" (it treats them as if they would be the same, i.e. case-insensitive). I, however, have alphanumeric keys in my data. The Join will link the data wrongly!
我在Excel工作簿中构建了一个小例子来重现行为。
Excel工作簿包含3张表格：
I built a small example in an Excel Workbook to reproduce the behavior. The Excel Workbook contains 3 Sheets:
- AlphaNum1
- AlphaNum2
- 结果
AlphaNum1表格包含以下数据
AlphaNum1 Sheet contains the following data
Key Val
a12b 1
A12b 2
a12B 3
A12B 4
e12f 7
E12F 8
1234 9
AlphaNum2表包含以下数据：
AlphaNum2 Sheet contains the following data:
Key Val
a12b 1
A12b 2
a12B 3
A12B 4
c12d 5
C12D 6
1234 9
我使用以下VBA代码连接到ADO并加入表（LEFT JOIN）：
I Use the following VBA code to connect to ADO and join the tables (LEFT JOIN):
Sub AlphaNumTest()
Dim oAdoConnection As New ADODB.Connection
Dim oAdoRecordset As New ADODB.Recordset
Dim sAdoConnectString As String, sPfad As String
Dim sQuery As String
On Error GoTo ExceptionHandling
sPfad = ThisWorkbook.FullName
sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties='Excel 12.0 Xml;HDR=YES;';Data Source=" & sPfad
oAdoConnection.Open sAdoConnectString
sQuery = "Select a1.[Key], a2.[Val] from [AlphaNum1$] a1 LEFT JOIN [AlphaNum2$] a2 ON a1.[Key] = a2.[Key]"
With oAdoRecordset
.Source = sQuery
.ActiveConnection = oAdoConnection
.Open
End With
Dim writeRange As Range
Dim headerRange As Range
'Set headerRange = ThisWorkbook.Sheets("WriteHere").Range("A1")
Set writeRange = ThisWorkbook.Sheets("Result").Range("A2")
' print the table header from recordset
For i = 0 To oAdoRecordset.Fields.Count - 1
' careful! the recordset is zero-indexed like it should be! Excel table however starts at index one, thus the i+1~
ThisWorkbook.Sheets("Result").Cells(1, i + 1).Value = oAdoRecordset.Fields(i).Name
' set bold
ThisWorkbook.Sheets("Result").Cells(1, i + 1).Font.Bold = True
Next i
' print the data directly from recordset!
writeRange.CopyFromRecordset oAdoRecordset
CleanUp:
On Error Resume Next ' Lazy skip
oAdoRecordset.Close
oAdoConnection.Close
Set oAdoRecordset = Nothing
Set oAdoConnection = Nothing
Exit Sub
ExceptionHandling:
MsgBox "Fehler: " & Err.Description
Resume CleanUp
End Sub
请注意，没关系如果我使用INNER或LEFT JOIN;结果是错误的方法 - 在这个例子中，我使用LEFT JOIN来演示行为。
Note that it does not matter if I use an INNER or a LEFT JOIN; the result is wrong eiter way - in this example here I use a LEFT JOIN to demonstrate the behavior.
结果输出是由LEFT加入的AlphaNum1.Key和AlphaNum2.Val JOIN。
The Result output is AlphaNum1.Key and AlphaNum2.Val joined by LEFT JOIN.
预期结果（我加入使用=不喜欢...）是：
The Expected Result (I am joining using "=" not LIKE...) is:
Key Val
a12b 1
A12b 2
a12B 3
A12B 4
e12f
E12F
1234 9
但ADO给了我实际结果（它处理Keys不区分大小写...）：
But ADO gives me the Actual Result (it treats the Keys case insensitive...):
Key Val
a12b 4
a12b 3
a12b 2
a12b 1
A12b 4
A12b 3
A12b 2
A12b 1
a12B 4
a12B 3
a12B 2
a12B 1
A12B 4
A12B 3
A12B 2
A12B 1
e12f
E12F
1234 9
为什么ADO行为如此吗？任何想法如何/如果我可以改变行为？
推荐答案
我发现了一个解决方案的ADO。似乎 COLLATE 不存在（请参阅： http://www.utteraccess.com/forum/Collate-Access-t1940463.html ）。
I found a workaround for ADO. Seems COLLATE does not exist (see: http://www.utteraccess.com/forum/Collate-Access-t1940463.html).
可以使用StrComp 并将其设置为二进制比较：
One can use StrComp and set it to binary compare:
sQuery = "Select a1.[Key], a2.[Val] from [AlphaNum1$] a1 LEFT JOIN [AlphaNum2$] a2 **ON StrComp(a1.[Key], a2.[Key], 0)=0**"
如果有更好的解决方案，我很乐意提供更多建议：）
If there is a better solution I am happy for more suggestions :)
这篇关于ADO（Excel，ACE OLEDB 12.0）不比较Key-Strings区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
