VBScript Excel ADO连接:在SQL查询中使用列别名获取值

Question

在以下代码中，我可以使用

从Excel获得计数

objTempRecordset.Fields.Item(0).Value

但是，我想在SQL中使用列名别名。即

sSQL = "Select Count(*) AS RecCount FROM [NELimits$] A WHERE A.Type = 'A' AND A.ID = " &Chr(39) & "R001" & Chr(39)

我想使用以下方式获得结果：

objTempRecordset.Fields.Item("RecCount").Value

我也尝试了objTempRecordset.Fields.Item("_Count(*)_").Value，但没有运气

有人可以让我知道如何在这种情况下使用列名别名吗？

注意：Excel有2列ID：具有诸如" R001"，" R002"之类的值类型：具有诸如" A"，" B"，" C"之类的值

示例代码：

sSQL = "Select Count(*) FROM [NELimits$] A WHERE A.Type = 'A' AND A.ID = " &Chr(39) & "R003" & Chr(39)
Sqlquery = sSQL 
sFilePath = "C:TempDataSheet.xlsx"
Dim objTempConnection : Set objTempConnection = CreateObject("ADODB.Connection")
Dim objTempRecordSet : Set objTempRecordSet = CreateObject("ADODB.Recordset")
Dim strPath
'Define constants for objTempRecordset
Const adOpenStatic=3
Const adLockOptimistic=3
Const adLockPessimistic=2
Const adCmdText = &H001
'Open connection 
objTempConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& sFilePath &";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
objTempRecordset.ActiveConnection = objTempConnection
objTempRecordset.CursorType = adOpenStatic
objTempRecordset.LockType = adLockOptimistic 
objTempRecordset.Open Sqlquery 
If objTempRecordset.EOF Or objTempRecordset.BOF Then
    msgbox "no record"
End If
msgbox "Record Count: "&objTempRecordset.RecordCount
msgbox "Value:" & objTempRecordset.Fields.Item(0).Value

---

Answer 1

带有ACE SQL Engine（在查询工作簿中使用），原始字段名称，列别名或带有空格，特殊字符（非alphanumeric）或保留词的表名称需要用方括号包裹或 Backticks 正确逃脱它们。

空格

sSQL = "Select Count(*) AS [Rec Count] FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Rec Count` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

特殊字符（例如连字符和磅/主题标签）

sSQL = "Select Count(*) AS [Rec-Count] FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Rec-Count` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS [Rec#] FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Rec#` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

保留单词（例如，计数）

sSQL = "Select Count(*) AS [Count] FROM [NELimits$] A" _
           & "  WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Count` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

否则，任何字段名称或列别名在查询中都是合法的，可以以以下格式以记录进行读取：

objTempRecordset.Fields.Item(0).Value               ' BY INDEX IN ITEM '
objTempRecordset.Fields.Item("Rec Count").Value     ' BY NAME IN ITEM '
objTempRecordset.Fields("Rec Count").Value          ' BY NAME IN FIELD COLLECTION '
objTempRecordset![Rec Count].Value                  ' BY NAME (EXCLAMATION POINT QUALIFIER) '

---

此外，缺少列别名以特殊的方式处理：

QUERY表达式上缺少别名（例如，计数函数汇总）

sSQL = "Select Count(*) FROM [NELimits$] A" _ 
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

在重复字段上缺少别名

sSQL = "Select ID, ID FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

对于上述两个缺失的别名，ACE引擎通常从 expr1 开始创建一个列别名（在MS访问中 - ACE引擎的通常接口）或 expr1000 forODBC连接并为所有其他未命名的表达式或未命名的重复字段引用增量。