用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
联系客服