Record Set Update

[KevW]

I have now got my random sample nearly working, It creates a temporary table and creates a newfield RandomNumber, but when it comes to assign a randnumber it updates the first 350,000 and then crashes. When I run debug the line of code it stops as it is rst.Update and any records in the temp table after this are not assigned a randon number. Any ideas why this is the case or any possible solutions. I am using access 2000 and that table contains just over 2 million records

Option Compare Database

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 tblAccount.Surname " & _
"INTO tblTemp " & _
"FROM tblSurname;"
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_"
strSQL = "SELECT TOP tblTemp.Account " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

db.TableDefs.Delete ("tblTemp")
End Sub

 

[RoyVidar]

What is the error?

Also, don't call Randomize inside the loop, place it before the loop.

You aren't hitting against any hard limits? File size growing towards 2GB?

 

[modest]

I performed the function on a database that was 7MB to start with. The table that was going to have Random Numbers eventually had somewhere around 450,000 records.

There were no errors, but the database is 1.7GB. I assume your database was larger than 7MB to start with and that Roy is right on the assumption that you reached the 2GB cap.

 

[KevW]

Thanks for your help, I never thought to check the size of the database and that was the problem it was reaching the 2 gb limit.

Looks like it is back to the drawing board for a solution to the random sample.

 

[RoyVidar]

One thing to try - in stead of a text field (surname) with umpteen characters, create a table with only two long integer numerics (hoping your PK/join field is such)

You could probably also test calling the function from an update query.