用VBA通过创建一个临时表,实现随机选取记录。
Sub PickRandom()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTableName As String
strSQL = "Select tblStaff.Firstname, tblStaff.Lastname " & _
"INTO tblTemp " & _
"FROM tblStaff;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbDouble)
tdf.Fields.Append fld
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "Select TOP 25 tblTemp.Firstname, tblTemp.Lastname " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
db.TableDefs.Delete ("tblTemp")
End Sub