在查询中循环遍历字段名时出现类型不匹配错误

Question

尝试遍历记录集中的字段时出现类型不匹配错误:

Public Function fnCompareTableHeaders(strFirstQuery As String, strSecondQuery As String) As Boolean
On Error GoTo ErrHandler
Dim db As Database
Dim rstFirstTable As dao.Recordset
Dim rstSecondTable As dao.Recordset
Dim strFirstString As String
Dim strSecondString As String
Dim fld As Field
DoCmd.Hourglass True
DoCmd.SetWarnings False

Set db = CurrentDb
'create recordsets for each table table to be compared
Set rstFirstTable = db.OpenRecordset(strFirstQuery, dbOpenDynaset)
Set rstSecondTable = db.OpenRecordset(strSecondQuery, dbOpenDynaset)

'loop through first table and write field names to string
For Each fld In rstFirstTable.Fields
If strFirstString = "" Then
strFirstString = fld.Name
Else
strFirstString = strFirstString & "|" & fld.Name
End If
Next

'loop through second table and write field names to string
For Each fld In rstSecondTable.Fields
If strSecondString = "" Then
strSecondString = fld.Name
Else
strSecondString = strSecondString & "|" & fld.Name
End If
Next

'compare field name strings to determine same fields in each table
If strFirstString = strSecondString Then
fnCompareTableHeaders = True
Else
fnCompareTableHeaders = False
End If


ExitFunct:
DoCmd.Hourglass False
DoCmd.SetWarnings True
Set rstFirstTable = Nothing
Set rstSecondTable = Nothing
db.Close
Exit Function

ErrHandler:
MsgBox Err.Description, vbExclamation, Err.Number & " - Error Message"
Err.Clear
fnCompareTableHeaders = False
Resume ExitFunct
End Function

该错误明确指出"类型不匹配"。传递给函数的变量是两个查询，它们具有相同的数据(理论上)。这个函数是验证的开始，通过验证两个表是否有相同的头名称，它们确实匹配。

在

处抛出错误。

For Each fld In rstFirstTable.Fields
If strFirstString = "" Then
strFirstString = fld.Name
Else
strFirstString = strFirstString & "|" & fld.Name
End If
Next

Answer 1

我想@June7确实在评论中回答了。
如果你在同一个项目中引用两个库，它们都有相同的命名对象，比如'field'，比如DAO和ADO库，你会得到一个类型不匹配。

看看你是否有，在工具>>引用:

Microsoft Office 14.0 Access database engine Object Library
Microsoft ActiveX Data Objects 6.1 Library

这就是为什么使用完整的"。"限定对象，以避免在以后引用第二个库时破坏正在运行的代码。做一个Dim fld As ADODB.Field，看起来你会很好。

在添加了对Excel的支持后，我在处理Word和Outlook的Office项目中遇到了很多问题;Excel的对象模型有很多名称与Word的对象模型相冲突，比如Range;我必须完全限定它们。