用VBA实现随机选择记录

用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