Random Sample

[KevW]

I had a module that could create a random smaple fo an Access 97 database. Unfortunately I now have to to create a random sample in Access 2000 for a database that contains approx 2.5 million records I have tried amending the code to the following but am now getting an 424 error object required at the line below. The code does though create the tblTemp. Any ideas of were I am going wrong.

Set tbl = db.TableDefs("Temp")

The full code is as shown below

Sub PickRandom()
Dim MyDb As Object
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim strSQL As String
Dim strTableName As String


' Create a new temporary table containing the required fields

Set MyDb = DBEngine.Workspaces(0).Databases(0)

strSQL = "SELECT tblStaff.StaffNo " & _
"INTO tblTemp " & _
"FROM tblStaff;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' Add new field to the new table

Set MyDb = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld

' Place a random number in the new field for each record

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

' Sort the data by the random number and move the top 416 into a new table

strTableName = "tblRandom_"
strSQL = "SELECT TOP 416 tblTemp.StaffNo" & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' Delete the temporary table

db.TableDefs.Delete ("tblTemp")

End Sub