Access 2007 VBA 查询在查询分析器中显示数据，但不在 VBA 编码记录集中显示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/253913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access 2007 VBA Query Shows Data in Query Analyzer But Not in VBA Coded Recordset
提问by Mr Furious
I have a function I've written that was initially supposed to take a string field and populate an excel spreadsheet with the values. Those values continually came up null. I started tracking it back to the recordset and found that despite the query being valid and running properly through the Access query analyzer the recordset was empty or had missing fields.
我有一个我写的函数，它最初应该接受一个字符串字段并用这些值填充一个 excel 电子表格。这些值不断出现空值。我开始将它跟踪回记录集，发现尽管查询有效并且通过 Access 查询分析器正常运行，但记录集是空的或缺少字段。
To test the problem, I created a sub in which I created a query, opened a recordset, and then paged through the values (outputting them to a messagebox). The most perplexing part of the problem seems to revolve around the "WHERE" clause of the query. If I don't put a "WHERE" clause on the query, the recordset always has data and the values for "DESCRIPTION" are normal.
为了测试这个问题，我创建了一个子程序，在其中创建了一个查询，打开了一个记录集，然后翻阅了这些值（将它们输出到一个消息框）。问题中最令人困惑的部分似乎与查询的“WHERE”子句有关。如果我不在查询中放置“WHERE”子句，则记录集总是有数据并且“DESCRIPTION”的值是正常的。
If I put anythingin for the WHERE clause the recordset comes back either totally empty (rs.EOF = true) or the Description field is totally blank where the other fields have values. I want to stress again that if I debug.print the query, I can copy/paste it into the query analyzer and get a valid and returned values that I expect.
如果我为 WHERE 子句输入任何内容，则记录集会返回完全空 ( rs.EOF = true) 或“描述”字段完全空白，其中其他字段具有值。我想再次强调，如果我 debug.print 查询，我可以将它复制/粘贴到查询分析器中并获得我期望的有效和返回值。
I'd sure appreciate some help with this. Thank you!
我肯定会很感激这方面的一些帮助。谢谢！
Private Sub NewTest()
' Dimension Variables
'----------------------------------------------------------
Dim rsNewTest As ADODB.Recordset
Dim sqlNewTest As String
Dim Counter As Integer
' Set variables
'----------------------------------------------------------
Set rsNewTest = New ADODB.Recordset
sqlNewTest = "SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, " & _
"dbo_part.partdescription as Description, dbo_partmtl.qtyper as [Qty Per] " & _
"FROM dbo_partmtl " & _
"LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum " & _
"WHERE dbo_partmtl.mtlpartnum=" & Chr(34) & "3C16470" & Chr(34)
' Open recordset
rsNewTest.Open sqlNewTest, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do Until rsNewTest.EOF
For Counter = 0 To rsNewTest.Fields.Count - 1
MsgBox rsNewTest.Fields(Counter).Name
Next
MsgBox rsNewTest.Fields("Description")
rsNewTest.MoveNext
Loop
' close the recordset
rsNewTest.Close
Set rsNewTest = Nothing
End Sub
EDIT: Someone requested that I post the DEBUG.PRINT of the query. Here it is:
编辑：有人要求我发布查询的 DEBUG.PRINT。这里是：
SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, dbo_part.partdescription as [Description], dbo_partmtl.qtyper as [Qty Per] FROM dbo_partmtl LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum WHERE dbo_partmtl.mtlpartnum='3C16470'
I have tried double and single quotes using ASCII characters and implicitly.
我已经尝试过使用 ASCII 字符和隐式的双引号和单引号。
For example:
例如：
"WHERE dbo_partmtl.mtlpartnum='3C16470'"
I even tried your suggestion with chr(39):
我什至用 chr(39) 尝试了你的建议：
"WHERE dbo_partmtl.mtlpartnum=" & Chr(39) & "3C16470" & Chr(39)
Both return a null value for description. However, if I debug.print the query and paste it into the Access query analyzer, it displays just fine. Again (as a side note), if I do a LIKE statement in the WHERE clause, it will give me a completely empty recordset. Something is really wonky here.
两者都返回一个空值进行描述。但是，如果我 debug.print 查询并将其粘贴到 Access 查询分析器中，它显示得很好。再次（作为旁注），如果我在 WHERE 子句中执行 LIKE 语句，它将给我一个完全空的记录集。这里的东西真的很奇怪。
Here is an interesting tidbit. The tables are linked to a SQL Server. If I copy the tables (data and structure) locally, the ADO code above worked flawlessly. If I use DAO it works fine. I've tried this code on Windows XP, Access 2003, and various versions of ADO (2.5, 2.6, 2.8). ADOwill not work if the tables are linked.
这是一个有趣的花絮。这些表链接到一个SQL Server. 如果我在本地复制表（数据和结构），上面的 ADO 代码可以完美运行。如果我使用 DAO，它工作正常。我试过这个代码Windows XP，Access 2003以及各种版本ADO (2.5, 2.6, 2.8)。 ADO如果表已链接，则将不起作用。
There is some flaw in ADO that causes the issue.
ADO 中存在一些导致此问题的缺陷。
Absolutely I do. Remember, the DEBUG.PRINTquery you see runs perfectly in the query analyzer. It returns the following:
我绝对愿意。请记住，DEBUG.PRINT您看到的查询在查询分析器中完美运行。它返回以下内容：
Job/Sub Rev Description Qty Per 36511C01 A MAIN ELECTRICAL ENCLOSURE 1 36515C0V A VISION SYSTEM 1 36529C01 A MAIN ELECTRICAL ENCLOSURE 1
However, the same query returns empty values for Description (everything else is the same) when run through the recordset (messagebox errors because of "Null" value).
但是，相同的查询在通过记录集运行时为 Description 返回空值（其他一切都相同）（由于“Null”值导致消息框错误）。
I tried renaming the "description" field to "testdep", but it's still empty. The only way to make it display data is to remove the WHERE section of the query. I'm starting to believe this is a problem with ADO. Maybe I'll rewriting it with DAO and seeing what results i get.
我尝试将“描述”字段重命名为“testdep”，但它仍然是空的。让它显示数据的唯一方法是删除查询的 WHERE 部分。我开始相信这是 ADO 的问题。也许我会用 DAO 重写它，看看我得到了什么结果。
EDIT: I also tried compacting and repairing a couple of times. No dice.
编辑：我也尝试过几次压缩和修复。没有骰子。
回答by
When using ADO LIKE searches must use % instead of *. I know * works in Access but for some stupid reason ADO won't work unless you use % instead.
使用 ADO LIKE 搜索时，必须使用 % 而不是 *。我知道 * 可以在 Access 中使用，但出于某些愚蠢的原因，除非您改用 %，否则 ADO 将无法使用。
I had the same problem and ran accoss this forum while trying to fix it. Replacing *'s with %'s worked for me.
我遇到了同样的问题，并在尝试修复它时访问了这个论坛。用 % 替换 * 对我有用。
回答by Zac Ortiz
I know some time has passed since this thread started, but just in case you're wondering, I have found out some curious about Access 2003 and the bug may have carried over to 2007 (as I can see it has).
我知道自此线程开始以来已经过去了一段时间，但以防万一您想知道，我发现了一些对 Access 2003 的好奇，并且该错误可能已经延续到 2007 年（正如我所见）。
I've had a similar problem with a WHERE clause because I needed records from a date field that also contained time, so the entire field contents would look like #6/14/2011 11:50:25 AM# (#'s added for formatting purposes).
我在 WHERE 子句中遇到了类似的问题，因为我需要来自也包含时间的日期字段的记录，因此整个字段内容看起来像 #6/14/2011 11:50:25 AM#（#'s added用于格式化目的）。
Same issue as above, query works fine with the "WHERE ((tblTransactions.TransactionDate) Like '" & QueryDate & "*');" in the query design view, but it won't work in the VBA code using ADO.
与上述相同的问题，查询与“WHERE ((tblTransactions.TransactionDate) Like '” & QueryDate & “*');”一起工作正常。在查询设计视图中，但它在使用 ADO 的 VBA 代码中不起作用。
So I resorted to using "WHERE ((tblTransactions.TransactionDate) Like '" & QueryDate & " %%:%%:%% %M');" in the VBA code, with ADO and it works just fine. Displays the record I was looking for, the trick is not to use "*" in the Like clause; or at least that was the issue in my case.
所以我求助于使用“WHERE ((tblTransactions.TransactionDate) Like '” & QueryDate & “ %%:%%:%% %M');” 在 VBA 代码中，使用 ADO，它工作得很好。显示我正在寻找的记录，诀窍是不要在 Like 子句中使用“*”；或者至少这是我的问题。
回答by DJ.
Description is a reserved word - put some [] brackets around it in the SELECT statement
描述是一个保留字 - 在 SELECT 语句中将一些 [] 括号括起来
EDIT
编辑
Try naming the column something besides Description
尝试将列命名为描述之外的其他内容
Also are you sure you are using the same values in the where clause - because it is a left join so the Description field will be blank if there is no corresponding record in dbo_part
您是否确定在 where 子句中使用了相同的值 - 因为它是一个左连接，所以如果 dbo_part 中没有相应的记录，那么描述字段将为空白
EDIT AGAIN
再次编辑
If you are getting funny results - try a Compact/Repair Database - It might be corrupted
如果您得到有趣的结果 - 尝试压缩/修复数据库 - 它可能已损坏
回答by Mr Furious
Well, what I feared is the case. It works FINE with DAO but not ADO.
好吧，我担心的就是这种情况。它适用于 DAO 但不适用于 ADO。
Here is the working code:
这是工作代码：
Private Sub AltTest()
' Dimension Variables
'----------------------------------------------------------
Dim rsNewTest As DAO.Recordset
Dim dbl As DAO.Database
Dim sqlNewTest As String
Dim Counter As Integer
' Set variables
'----------------------------------------------------------
sqlNewTest = "SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, " & _
"dbo_part.partdescription as [TestDep], dbo_partmtl.qtyper as [Qty Per] " & _
"FROM dbo_partmtl " & _
"LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum " & _
"WHERE dbo_partmtl.mtlpartnum=" & Chr(39) & "3C16470" & Chr(39)
Debug.Print "sqlNewTest: " & sqlNewTest
Set dbl = CurrentDb()
Set rsNewTest = dbl.OpenRecordset(sqlNewTest, dbOpenDynaset)
' rsnewtest.OpenRecordset
Do Until rsNewTest.EOF
For Counter = 0 To rsNewTest.Fields.Count - 1
MsgBox rsNewTest.Fields(Counter).Name
Next
MsgBox rsNewTest.Fields("TestDep")
rsNewTest.MoveNext
Loop
' close the recordset
dbl.Close
Set rsNewTest = Nothing
End Sub
结束子
I don't use DAO anywhere in this database and would prefer not to start. Where do we go from here?
我不在这个数据库的任何地方使用 DAO，也不想开始。我们从这里去哪里？
回答by DJ.
Ultimately I think it's a problem with running ADO 2.8 on Vista 64
最终我认为在 Vista 64 上运行 ADO 2.8 是个问题
Personally I have always used DAO in Access projects.
我个人一直在 Access 项目中使用 DAO。
回答by Mr Furious
I put brackets around the word "Description" in the SELECT statement, but it's behavior remains. It works fine as long as I don't put anything in the WHERE clause. I've found if I put anything in the where clause, the description is blank (despite showing up in the Query analyzer). If I use a LIKE statement in the WHERE clause, the entire recordset is empty but it still works properly in the Query Analyzer.
我在 SELECT 语句中的“描述”一词周围加上了括号，但它的行为仍然存在。只要我不在 WHERE 子句中添加任何内容，它就可以正常工作。我发现如果在 where 子句中添加任何内容，则描述为空白（尽管出现在查询分析器中）。如果我在 WHERE 子句中使用 LIKE 语句，则整个记录集为空，但它在查询分析器中仍能正常工作。
