vba 在 Access 中查找随机记录（真随机）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6266593/
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
In Access find a random record (true random)
提问by Ben
what I'm trying to do is everytime the program opens the image on a form is different. So I have a simple table with 2 columns ID and ImagePath, how do I create the code so a random record(ImagePath) is chosen, on a form load event or something similar? Rnd is no good, as it will be the same image everytime the database is reopened.
我想要做的是每次程序打开表单上的图像时都是不同的。所以我有一个包含 2 列 ID 和 ImagePath 的简单表，如何创建代码以便在表单加载事件或类似事件中选择随机记录（ImagePath）？Rnd 不好，因为每次重新打开数据库时它都会是相同的图像。
Thanks!
谢谢！
采纳答案by HansUp
See this article by Susan Harkins on TechRepublic: http://www.techrepublic.com/blog/howdoi/how-do-i-retrieve-a-random-set-of-records-in-microsoft-access/149
请参阅 Susan Harkins 在 TechRepublic 上的这篇文章：http: //www.techrepublic.com/blog/howdoi/how-do-i-retrieve-a-random-set-of-records-in-microsoft-access/149
I used her GetRandomValue function in this query, which returns a different record each time.
我在这个查询中使用了她的 GetRandomValue 函数，它每次返回不同的记录。
SELECT TOP 1 f.id, GetRandomValue(f.id) AS rnd_value
FROM tblFoo AS f
ORDER BY 2;
The function:
功能：
Public Function GetRandomValue(fld As Variant)
Randomize
GetRandomValue = Rnd(1)
End Function
Caution: This approach requires running a function against every row of the table. It may be tolerable for small to medium tables. But you should not use it with very large tables.
注意：这种方法需要对表的每一行运行一个函数。对于中小型桌子来说，这可能是可以接受的。但是您不应该将它用于非常大的表。
回答by Brian Camire
Try calling Randomize once -- before the first time you call Rnd. As, the help topic for Rnd says, "Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer."
尝试调用 Randomize 一次——在第一次调用 Rnd 之前。正如 Rnd 的帮助主题所说，“在调用 Rnd 之前，使用不带参数的 Randomize 语句以基于系统计时器的种子初始化随机数生成器。”
回答by ray
Rnd is no good?
Rnd不行吗？
Option Compare Database
Option Explicit
Sub Test()
Randomize
Dim x As Integer
'Print the first field of a 100 random records
For x = 0 To 100
CallRandomRecord
Next x
End Sub
Sub CallRandomRecord()
Dim rs As DAO.Recordset
Dim recordCount As Long
Dim randomRecord As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
rs.MoveLast 'To get the count
rs.MoveFirst
recordCount = rs.recordCount - 1
randomRecord = CLng((recordCount) * Rnd)
rs.Move randomRecord
Debug.Print "Random Record No:" & randomRecord & " Field 1: " & rs.Fields(0)
End Sub
回答by mwolfe02
I wrote a couple functions of my own to return a random record and then timed them along with the other solutions offered here. Both of mine beat the Harkins method, but neither of them could touch @ray023's (slightly modified for benchmarking) solution. @ray023's solution is also arguably the simplest. Take that Susan Harkins!
我自己编写了几个函数来返回随机记录，然后将它们与此处提供的其他解决方案一起计时。我的两个方法都击败了 Harkins 方法，但他们都无法触及@ray023 的（针对基准测试略有修改）解决方案。@ray023 的解决方案也可以说是最简单的。拿那个苏珊哈金斯！
Here's the code. You can copy it and paste into a standard module to test on your data. You just need to change the three constants at the top of the TimeThemmodule:
这是代码。您可以将其复制并粘贴到标准模块中以测试您的数据。您只需要更改TimeThem模块顶部的三个常量：
Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub TimeThem()
Const Loops As Integer = 10
Const TblName As String = "Batches"
Const FldName As String = "BatchName"
Const IndexFld As String = "BatchID"
Dim i As Integer, s As Long, dummy As Variant
s = GetTickCount
For i = 1 To Loops
dummy = HarkinsRandom(TblName, FldName)
Next i
Debug.Print "Harkins:"; GetTickCount - s
s = GetTickCount
For i = 1 To Loops
dummy = RandomRecord(TblName, FldName)
Next i
Debug.Print "RandomRecord:"; GetTickCount - s
s = GetTickCount
For i = 1 To Loops
dummy = RandomRecordWithIndex(TblName, FldName, IndexFld)
Next i
Debug.Print "WithIndex:"; GetTickCount - s
s = GetTickCount
For i = 1 To Loops
dummy = CallRandomRecord(TblName, FldName)
Next i
Debug.Print "CallRandom:"; GetTickCount - s
End Sub
Function HarkinsRandom(TblName As String, FldName As String)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(" SELECT TOP 1 " & FldName & _
" FROM " & TblName & _
" ORDER BY GetRandomValue(" & FldName & ")", _
dbOpenForwardOnly)
HarkinsRandom = rs(0)
End Function
Public Function GetRandomValue(fld As Variant)
Randomize
GetRandomValue = Rnd(1)
End Function
Function RandomRecord(TblName As String, FldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String
Randomize
NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
RecNum = Int(Rnd() * NumRecs + 1)
SQL = " SELECT TOP 1 " & FldName & _
" FROM (" & _
" SELECT TOP " & RecNum & " " & FldName & " " & _
" FROM " & TblName & _
" ORDER BY " & FldName & ")" & _
" ORDER BY " & FldName & " DESC"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
RandomRecord = rs(0)
End Function
Function RandomRecordWithIndex(TblName As String, FldName As String, _
Optional IndexedFieldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String
Randomize
NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
RecNum = Int(Rnd() * NumRecs + 1)
If Len(IndexedFieldName) = 0 Or IndexedFieldName = FldName Then
SQL = " SELECT TOP 1 " & FldName & _
" FROM (" & _
" SELECT TOP " & RecNum & " " & FldName & " " & _
" FROM " & TblName & _
" ORDER BY " & FldName & ")" & _
" ORDER BY " & FldName & " DESC"
Else
SQL = " SELECT TOP 1 " & FldName & _
" FROM (" & _
" SELECT TOP " & RecNum & " " & FldName & ", " & IndexedFieldName & _
" FROM " & TblName & _
" ORDER BY " & IndexedFieldName & ")" & _
" ORDER BY " & IndexedFieldName & " DESC"
End If
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
RandomRecordWithIndex = rs(0)
End Function
Function CallRandomRecord(TblName As String, FldName As String)
Dim rs As DAO.Recordset
Dim recordCount As Long
Dim RandomRecord As Long
Set rs = CurrentDb.OpenRecordset("SELECT " & FldName & " FROM " & TblName)
rs.MoveLast 'To get the count
rs.MoveFirst
recordCount = rs.recordCount - 1
RandomRecord = CLng((recordCount) * Rnd)
rs.Move RandomRecord
CallRandomRecord = rs(0)
' Debug.Print "Random Record No:" & randomRecord & " Field 1: " & rs.Fields(0)
End Function
And here are the results of the test running against a table with about 50,000 records (it's a locally linked Jet table; ie, it's in an .mdb on the same computer as where I ran the test):
以下是针对包含大约 50,000 条记录的表运行的测试结果（它是本地链接的 Jet 表；即，它位于我运行测试所在的同一台计算机上的 .mdb 中）：
Harkins: 4461
RandomRecord: 2528
WithIndex: 1918
CallRandom: 172
Harkins: 4150
RandomRecord: 2278
WithIndex: 2043
CallRandom: 47
CallRandom: 63
WithIndex: 2090
RandomRecord: 2324
Harkins: 4197
CallRandom: 46
WithIndex: 1997
RandomRecord: 2169
Harkins: 4150
I ran it four times reversing the order after the first two to account for potential caching advantages. As you can see, my two functions ran about twice as fast as the Harkins solution, but @ray023's solution was at its slowest more than 25 times faster (and at its fastest nearly 100 times faster).
为了考虑潜在的缓存优势，我在前两次之后颠倒了顺序运行了四次。如您所见，我的两个函数的运行速度大约是 Harkins 解决方案的两倍，但@ray023 的解决方案最慢时快了 25 倍以上（最快时快了近 100 倍）。
But by all means, benchmark against your own data.
但无论如何，请根据您自己的数据进行基准测试。
回答by David-W-Fenton
I may be too simple to understand the problem, but it seems to me that if you want to retrieve a single random image, then all you need to do is generate a single random number that somehow keys into the table of images available to you. If there are 100 images to choose from, you want a random number from 1 to 100.
我可能太简单而无法理解这个问题，但在我看来，如果您想检索单个随机图像，那么您需要做的就是生成一个随机数，该随机数以某种方式键入您可用的图像表。如果有 100 张图片可供选择，您需要一个从 1 到 100 的随机数。
So, you generate that number:
因此，您生成该数字：
Round(100 * Rnd(), 0)
...and then you use that to retrieve the image. If the table of images has an Autonumber PK, you could just use that, and it would be VERY FAST. If your image is in a subform, you could set the LinkMaster to the literal PK value and that would retrieve the image for you.
...然后你用它来检索图像。如果图像表有一个自动编号 PK，你可以使用它，它会非常快。如果您的图像在子表单中，您可以将 LinkMaster 设置为文字 PK 值，这将为您检索图像。
On the subject of Randomize(), I can't seem to get it to repeat when I call Rnd() in the Immediate window, so I'm not sure if it's needed.
关于 Randomize() 的主题，当我在立即窗口中调用 Rnd() 时，我似乎无法让它重复，所以我不确定是否需要它。
But it all seems like a very simple operation to me, one that may not require any SQL or the use of a recordset. If you go the recordset route, I'd recommend opening it once and persisting it and then navigating it each time you need it, rather than opening it repeatedly each time you need a new image. But if I were doing this, I'd make things as simple for myself as possible and go the Autonumber PK route for the images. If you wanted to do it in SQL, that would be:
但这对我来说似乎是一个非常简单的操作，可能不需要任何 SQL 或使用记录集。如果您走记录集路线，我建议您打开一次并保留它，然后在每次需要时导航它，而不是每次需要新图像时重复打开它。但是，如果我这样做，我会尽可能让事情变得简单，并为图像走自动编号 PK 路线。如果你想用 SQL 来做，那就是：
SELECT Images.ID, Images.Path
FROM Images
WHERE Images.ID = Round(100 * Rnd(), 0)
Obvoiusly, you'd change 100 to an appropriate number. If you need Randomize(), then replace the direct Round(100 * Rnd(), 0)with a function that calls Randomize() and then returns Round(100 * Rnd(), 0).
显然，您会将 100 更改为适当的数字。如果您需要 Randomize()，则将直接替换为Round(100 * Rnd(), 0)调用 Randomize() 然后返回 的函数Round(100 * Rnd(), 0)。
But maybe I'm missing some important details that makes this much more complicated than I seem to think it is.
但也许我遗漏了一些重要的细节，这使得这比我想象的要复杂得多。
