ADODB连接 - SQL中的“NOT LIKE”查询无法正常工作

Question

我有一个非常奇怪的问题。我有一个Access数据库（2013 64位.accdb），里面有几个基于其他查询“Q_ManifestedByStatus”的查询。

所有查询在Access中都按预期工作，没有问题。

问题是当我使用ADODB连接将数据提取到Excel（2013 64位）时。其中一个查询具有“WHERE”子句[PO No] LIKE '8 *'。当SQL代码嵌入VBA时，它可以正常工作，但当SQL被替换为来自Access的查询名称时，它无法正常工作。

当我从Access中删除WHERE子句时，两种方式都可以正常工作......当我将LIKE子句更改为NOT LIKE时，两种方式都可以正常工作...

欢迎任何建议！

请参见下面的代码。

Access SQL:

SELECT 
Int([Collection date]) AS [Date], 
Sum(IIf([Status]="Early",1,0)) AS Early, 
Sum(IIf([Status]="On Time",1,0)) AS [On Time], 
Sum(IIf([Status]="Late",1,0)) AS Late, 
Sum(IIf([Status]="Not Manifested",1,0)) AS [Not Manifested], 
[Early]+[on time]+[late]+[not manifested] AS [Sum], 
Round([Early]/[Sum],2)*100 & "%" AS [Early%], 
Round([On Time]/[Sum],2)*100 & "%" AS [On Time%], 
Round([Late]/[Sum],2)*100 & "%" AS [Late%], 
Round([Not Manifested]/[Sum],2)*100 & "%" AS [Not Manifested%]
FROM Q_ManifestedByStatus
WHERE [PO No] Like '8*' Or [PO No] Like '9*'
GROUP BY Int([Collection date]);

VBA - SQL嵌入 - 有效

Sub tt()

Dim objAdoCon       As Object
Dim rst             As Object
Dim provider        As String
Dim sql             As String

sql = "SELECT Int([Collection date]) AS [Date], Sum(IIf([Status]=""Early"",1,0)) AS Early," & _
"Sum(IIf([Status]=""On Time"",1,0)) AS [On Time], Sum(IIf([Status]=""Late"",1,0)) AS Late," & _
"Sum(IIf([Status]=""Not Manifested"",1,0)) AS [Not Manifested], [Early]+[on time]+[late]+[not manifested] AS [Sum]," & _
"Round([Early]/[Sum],2)*100 & ""%"" AS [Early%], Round([On Time]/[Sum],2)*100 & ""%"" AS [On Time%], Round([Late]/[Sum],2)*100 & ""%"" AS [Late%]," & _
"Round([Not Manifested]/[Sum],2)*100 & ""%"" AS [Not Manifested%] FROM Q_ManifestedByStatus WHERE [PO No] NOT Like '8*' GROUP BY Int([Collection date]);"

Set objAdoCon = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

If Application.Version = 15# Then provider = "Provider = Microsoft.ACE.oledb.12.0;" Else provider = "Provider = Microsoft.ACE.oledb.4.0;"
objAdoCon.Open provider & "Data Source = C:\Users\pplusa\Documents\SupplierCompliace.accdb"

'Get 81 POs data
'Daily
Worksheets("81 POs Daily").Select
Cells.Delete

rst.Open sql, objAdoCon
Range("A2").CopyFromRecordset rst

For i = 0 To rst.Fields.Count - 1
    Range(Cells(1, i + 1).Address).Value = rst.Fields.Item(i).Name
Next i

Cells.EntireColumn.AutoFit
rst.Close

End Sub

VBA - 按查询名称 - 不起作用

Sub tt()

Dim objAdoCon       As Object
Dim rst             As Object
Dim provider        As String

Set objAdoCon = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

If Application.Version = 15# Then provider = "Provider = Microsoft.ACE.oledb.12.0;" Else provider = "Provider = Microsoft.ACE.oledb.4.0;"
objAdoCon.Open provider & "Data Source = C:\Users\pplusa\Documents\SupplierCompliace.accdb"

'Get 81 POs data
'Daily
Worksheets("81 POs Daily").Select
Cells.Delete

rst.Open "Q_ManifestedByDate81", objAdoCon
Range("A2").CopyFromRecordset rst

For i = 0 To rst.Fields.Count - 1
    Range(Cells(1, i + 1).Address).Value = rst.Fields.Item(i).Name
Next i

Cells.EntireColumn.AutoFit
rst.Close

End Sub

- aberforth

Answer 1

SQL中不使用*进行多字符匹配，而是使用%。

- MatBailie

---

我在Access应用程序中进行了更改，现在它在Access上停止工作，但在Excel上工作。 这是因为VBA使用不同的SQL语法来编写Access SQL查询吗？ - aberforth

@aberforth 看起来很有可能。对于每个环境和每个方言，你需要重新阅读手册。SQL不是一个统一的通用语言的例子是，甚至 / 在不同的实现中也没有被实现相同。 （在某些实现中进行整数除法，在其他实现中进行定点除法）。 - MatBailie

那么就有道理了...换句话说，我不能使用VBA按查询名称提取查询数据，我需要将SQL嵌入VBA或更改Access中的SQL，但这样做Access将无法工作。 我会想出来的。 谢谢Mat！ - aberforth

@aberforth ... VBA字符串查询比Access存储查询效率低，因为Jet/ACE引擎会对存储查询进行缓存和预编译，以获得最佳执行计划。VBA查询是即时运行的，这样你就可以避免在代码中使用冗长混乱的字符串！ - Parfait

Answer 2

这是在MS Access内部运行LIKE和通过OLEDB/ODBC外部运行之间已知的问题。请记住，MS Access既是GUI程序又是后端数据库。

默认情况下，MS Access通过其GUI .exe界面使用ANSI-89语法，并将*用作通配符运算符。通过OLEDB使用Excel等方法访问MS Access时，它使用ANSI-92语法，并将%用作通配符运算符。请参见MS Office支持文档中有关Access通配符字符参考的内容。

考虑以下两个选项以使两者一致：

1. 在Access查询中使用%运算符的ALIKE运算符而不是LIKE。
2. 或者，在“文件”\“选项”\“对象设计器”下将.accdb数据库设置为使用SQL Server兼容语法（ANSI 92），然后LIKE将遵循%而不是*。

- Parfait