Select Random Rows from MS Access

(Seleccionar filas aleatoriamente en MS Access)

Usually you have to take a random row from an Access DB, the following code makes it.

Tip: If You're accesing Access in a web (.aspx page, web service) You need remenber that ASPnet user must have full access(read, create, delete, modify) to the folder where the mdb lies, because it has to modify the .mdb file and create/deltete the .ldb file.

Added: Better code in v2.0 Select Random Rows from MS Access


Dim adapter As New OleDbDataAdapter("SELECT * from myTable", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Web.HttpContext.Current.Request.MapPath("data.mdb") + ";")

Dim data As New DataSet
adapter.Fill(data)
Dim table As DataTable = data.Tables(0)
If table.Rows.Count = 0 Then
Throw New Exception("The query return 0 rows")
End If
table.Columns.Add("Random", GetType(Double))
Dim random As New Random
For Each row As DataRow In table.Rows
row("Random") = random.NextDouble
Next

Dim dv As DataView = table.DefaultView
dv.Sort = "Random ASC"
Dim sResult As String

'the first column
sResult = dv.Item(0).Row(0).ToString()

Permalink: Select Random Rows from MS Access