Select a Random Record from a Microsoft Access 2007 Table

Here's a tidbit I figured out today, how to select a random record from table in Microsoft Access 2007. If you don't know how to create a function in Microsoft Access 2007, see Creating Custom Access Functions.

Assume "MyTable" is the table from which you want to select a record, and that the table has a identifier field, "ID", which is a number that is unique, but not necessarily sequential. Here is the code for a function which returns the identifier for a random row in the table:

Public Function RandomRow() As Integer

'Get access to the database
Dim db As DAO.Database
Set db = CurrentDb()

'Get access to the table
Dim tdf As DAO.TableDef
Set tdf = db.TableDefs("MyTable")

'Get the number of records in the table
Dim total As Integer
total = tdf.RecordCount

'Get the records in the table
Dim rst As DAO.Recordset
Set rst = tdf.OpenRecordset

'Select a random record in the table
Dim randomRecord As Integer
randomRecord = Int(Rnd() * total)+1

'Move to the random record
Dim currentRecord As Integer
currentRecord = 1
rst.MoveFirst
Do While (currentRecord <> randomRecord)
currentRecord = currentRecord + 1
rst.MoveNext
Loop

'Return the unique identifier of the random record
Dim recordID as Integer
recordID = rst.Fields("ID").Value

'Clean up pointers
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing

'Return value
RandomRow = recordID

End Function

I found another solution for this at VBA Tips, but my solution is simpler, not requiring the creation of a temporary table.

Sphere: Related Content

This is Not an Abortion Rights Issue

According to CNN, Dr. Pierre Jean-Jacques Renelique lost his medical license in Florida when his staff aborted an 18 year old's pregnancy in it's 23rd week. The young woman gave birth after being given two pills and saw her baby breathe then be thrown away with the other products of conception. The decomposing baby was later found by the police in a cardboard box in a closet when the police executed a search warrant.

This situation provides a gruesome example of group psychology gone wrong. Several people worked together and abdicated responsibility as medical professionals for this to have occurred. The procedure itself, late-term abortion, is rare. That an 18 year old accepted the choice to terminate her pregnancy in the 23 week, apparently without health reasons to do so, highlights her lack of responsibility and the staff's callousness and lack of empathy in directing her to alternative resources to, at the very least, carry the baby to term in a few weeks and have the baby paced by adoption. The staff then initiated the procedure without the doctor present and panicked when the young woman delivered the baby. The doctor apparently had abdicated all responsibility in his practice because they were willing to proceed. There was obviously some remorse about what happened because the doctor and his staff did not dispose of the baby completely.

This incident highlights failures from conception through medical practice. The young woman and her partner did not properly use contraception to avoid pregnancy. The young woman did not seek medical services and counseling early in the pregnancy. Her family did not support her in this difficult decision. The medical staff acted recklessly and the doctor did not properly supervise his practice. This situation could have been prevented and mitigated many times and is not just a matter of whether or not a woman should have an abortion.

Sphere: Related Content