MS Access中SQL查询的奇怪结果

Alright, here's an odd one from an MS Access database I'm running.

好吧,这是我正在运行的MS Access数据库中的一个奇怪的。

I have a SQL query:

我有一个SQL查询:

SELECT * 
FROM [Service Schedule]
WHERE ID=2 
  AND Volume <= 3000 
  AND Term='Monthly'
  AND special = 'Regular'
ORDER BY volume

When I put that into the SQL view of the query builder, I get 2 records, one with a volume of 0 and one with a volume of 3000.

当我将它放入查询构建器的SQL视图中时,我得到2条记录,一条卷的卷为0,另一条卷的卷为3000。

When I use this code:

当我使用这段代码时:

sSQL = "SELECT * FROM [Service Schedule] WHERE ID=2 AND Volume <= 3000 AND Term='Monthly' and special = 'Regular' ORDER BY volume"            
Set rsServiceSched = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

** To see what I'm getting from the query in the code, I'm using Debug.Print to output the recordcount and the volume.

**要查看我从代码中查询得到的内容,我使用Debug.Print输出recordcount和volume。

I only get 1 record, the one with the volume of 0.

我只获得1条记录,一条体积为0。

Here's where it gets really strange...

这里真的很奇怪......

When I change Volume <= 3000 to Volume < 3000 I get one record (volume = 0)

当我将Volume <= 3000更改为Volume <3000时,我得到一条记录(volume = 0)

When I change Volume <= 3000 to Volume = 3000 I get one record (volume = 3000)

当我将Volume <= 3000更改为Volume = 3000时,我得到一条记录(volume = 3000)

Anyone spot anything blatantly wrong with what I'm doing?

有人发现我正在做的事情明显错误吗?

5 个解决方案

#1

It sounds like you are expecting to 'see' all of the records, but I think you are just retrieving the first record. I say this because you are seeing what would be the first record with each case. You will probably need to move to the next record in your recordset in order to see the next one.

听起来你希望“看到”所有记录,但我认为你只是检索第一条记录。我这样说是因为你看到每个案例的第一条记录是什么。您可能需要移动到记录集中的下一条记录才能看到下一条记录。

rsServiceSched.MoveNext

#2

Digression: A discussion of the Recordcount of DAO recordsets

The recordcount of a DAO recordset is not guaranteed accurate until after a .MoveLast, but if any records are returned by the recordset, .RecordCount will be 1 or more.

直到.MoveLast之后,DAO记录集的记录数量才能保证准确,但如果记录集返回任何记录,则.RecordCount将为1或更多。

Note that a table-type recordset will return an accurate .RecordCount immediately, without the .MoveLast, but keep in mind that you can't open a table-type recordset on a linked table. Also, be careful and don't assume you're getting the recordset type you want unless you've explicitly specified it. While dbOpenTable is the default recordset type, if the table or SQL string can't be opened as a table-type recordset, it will fall over to opening a dynaset. Thus, you can think you're opening a table-type recordset with this because table-type is the default and you've passed a table name:

请注意,表类型记录集将立即返回准确的.RecordCount,而不包含.MoveLast,但请记住,您无法在链接表上打开表类型记录集。另外,小心并且不要假设您正在获得所需的记录集类型,除非您明确指定它。虽然dbOpenTable是默认记录集类型,但如果表或SQL字符串无法作为表类型记录集打开,则它将打开动态集。因此,您可以认为您正在使用此方法打开表类型记录集,因为table-type是默认值并且您已传递表名:

  Set rs = CurrentDB.OpenRecordset("MyTable")

but you have to remember that just because you pass it a table, it won't necessarily open a table-type recordset, and the recordcount won't necessarily be accurate. If you really want to be sure you're opening a table-type recordset, you need to specify that explicitly:

但你必须记住,仅仅因为你传递了一个表,它不一定会打开一个表类型的记录集,并且recordcount不一定是准确的。如果您确实要确定要打开表类型记录集,则需要明确指定:

  Set rs = CurrentDB.OpenRecordset("MyTable", dbOpenTable)

If "MyTable" is a linked table, that will throw an error. If you have a mix of linked tables and local tables, you'll have to use two different methods to obtain a table-type recordset. Otherwise (i.e., if you're not specifying the recordset type and letting it be table-type when possible and a dynaset when not), you need to know when you need to .MoveLast to get an accurate .RecordCount. If you really want to be efficient in that case, you'll test the .Type of the recordset:

如果“MyTable”是链接表,则会抛出错误。如果您混合使用链接表和本地表,则必须使用两种不同的方法来获取表类型记录集。否则(即,如果你没有指定记录集类型并在可能的情况下让它成为表类型,而在没有指定时使用动态集),你需要知道何时需要.MoveLast来获得准确的.RecordCount。如果你真的想在这种情况下有效率,你将测试记录集的.Type:

  Set rs = CurrentDB.OpenRecordset("MyTable")
  If rs.Type = dbOpenDynaset Then
     rs.MoveLast
  End If

At that point, you'll have an accurate .RecordCount property whether the recordset opened as table-type or as a dynaset.

此时,无论记录集是以table-type还是作为动态集打开,您都将获得准确的.RecordCount属性。

But keep in mind that it's very seldom that you need to use a full recordset to get a recordcount. Usually, you will examine the .RecordCount only to see if your recordset has returned any records, and in that case, you don't need an accurate count.

但请记住,您很少需要使用完整记录集来获取记录数。通常,您将检查.RecordCount仅查看您的记录集是否已返回任何记录,在这种情况下,您不需要准确的计数。

Likewise, if you're going to walk through the full recordset, you'll eventually have an accurate RecordCount. That is, it would be senseless to do this:

同样,如果您要浏览完整的记录集,您最终将获得准确的RecordCount。也就是说,这样做是毫无意义的:

  Set rs = CurrentDB.OpenRecordset("MyTable")
  rs.MoveLast
  If rs.RecordCount > 0 Then
     .MoveFirst
     Do Until rs.EOF
       [something or other]
       .MoveNext
     Loop
  End If
  Debug.Print rs.RecordCount

The .MoveLast is completely unneeded in that context as you don't need to know the exact count at that point in the code.

.MoveLast在该上下文中完全不需要,因为您不需要知道代码中该点的确切计数。

Also, keep in mind that in some contexts where you really do need to know the exact .RecordCount, it may be more efficient to just use the built-in Access DCount() function, or to do something like this:

另外,请记住,在某些情况下,您确实需要知道确切的.RecordCount,使用内置的Access DCount()函数可能更有效,或者执行以下操作:

  Dim lngRecordCount As Long
  lngRecordCount = CurrentDB.OpenRecordset("SELECT COUNT(*) FROM MyTable")(0)

Or:

  lngRecordCount = DBEngine.OpenDatabase(Mid(CurrentDB.TableDefs("MyTable").Connect, 11)).TableDefs("MyTable").RecordCount

There are all sorts of efficient shortcuts to get the accurate RecordCount without forcing the recordset pointer to travel to the last record.

有各种有效的快捷方式可以获得准确的RecordCount,而不必强制记录集指针前往最后一条记录。

BTW, one of the reason the RecordCount for a pure Table-Type record is accurate is because it doesn't have to be calculated -- Jet/ACE maintains the RecordCount property as part of its regular operations.

BTW,纯表类型记录的RecordCount准确的原因之一是因为它不需要计算 - Jet / ACE将RecordCount属性作为其常规操作的一部分。

Digression: A discussion of the Recordcount of ADO recordsets

By @onedaywhen

For an ADO recordset, the RecordCount property will always be the final value. That is, unlike DAO, if you check its value it cannot subsequently change. Navigating EOF does not affect the RecordCount value in any way for ADO.

对于ADO记录集,RecordCount属性将始终为最终值。也就是说,与DAO不同,如果检查其值,则无法随后更改。导航EOF不会以任何方式影响ADO的RecordCount值。

This is true even when fetching records asynchronously (something DAO recordsets does not explicitly support): that is, even when the recordset is not yet full, the RecordCount property still reflects the final value (not the number of records fetched so far, as for DAO).

即使异步提取记录(DAO记录集没有明确支持)也是如此:即使记录集尚未满,RecordCount属性仍然反映最终值(不是到目前为止提取的记录数,如同DAO)。

Some combinations of CursorLocation and CursorType will cause RecordCount to always be -1, meaning the property is not supported. But, again, this will remain constant i.e. if it is initially -1 then it will always be -1.

CursorLocation和CursorType的某些组合将导致RecordCount始终为-1,这意味着不支持该属性。但是,再次,这将保持不变,即如果它最初为-1,那么它将始终为-1。

The applicable combinations for the Access database engine for which RecordCount is not supported are adOpenForwardOnly and adOpenDynamic but only when using a server side cursor location. (Note the Access database engine doesn't actually support dynamic cursors: instead adOpenDynamic is overloaded for the user to provide an optimization 'hint' to the engine that the recordset's Source is dynamic SQL code).

不支持RecordCount的Access数据库引擎的适用组合是adOpenForwardOnly和adOpenDynamic,但仅限于使用服务器端游标位置时。 (注意Access数据库引擎实际上不支持动态游标:而是adOpenDynamic重载,以便用户向引擎提供记录集的Source是动态SQL代码的优化'提示'。

Note that setting the recordset's Filter property will change the RecordCount to reflect the number of records after the filter has been applied.

请注意,设置记录集的Filter属性将更改RecordCount以反映应用过滤器后的记录数。

#3

I'm not sure what you're doing for PK values of that table but when you say: "ID=2" that sounds fishy to me because I always use surrogate keys (never natural) and ID is always the PK. Since you aren't joining to any tables this tells me you should always expect one row/tuple from your result.

我不确定你对那张桌子的PK值做了什么,但当你说:“ID = 2”对我来说听起来很可疑,因为我总是使用代理键(从不自然)而ID总是PK。由于您没有加入任何表,这告诉我您应该总是期望结果中有一行/元组。

If ID isn't your PK would you mind letting me know via the comments?

如果ID不是你的PK,你会介意通过评论告诉我吗?

Perhaps you need to iterate over the result set using: Set.MoveNext

也许您需要使用以下方法迭代结果集:Set.MoveNext

#4

You are probably not looping through your result set. rsServiceSched is merely pointing to the first record, which will be the one with the lowest volume (bacuase of your order by clause). Now you need to do something with that, then advance to the next record

您可能没有循环遍历结果集。 rsServiceSched仅指向第一个记录,该记录将是具有最低音量的记录(您的order by子句的bacuase)。现在你需要对它做一些事情,然后前进到下一条记录

This example might help you...

这个例子可以帮助你...

#5

This is DAO, you need to movelast to get the full recordcount; otherwise, you'll just get 1 if records exist and 0 if they do not.

这是DAO,你需要movelast来获得完整的记录数;否则,如果记录存在,您将获得1,如果不存在,则获得0。