Why would AccessDataSource return different results to query in Access?

Question

Hi, I have a query to return random distinct rows from an Access database. Here is the query:

SELECT * FROM 
(SELECT DISTINCT m.MemberID, m.Title, m.FullName, m.Address, 
        m.Phone, m.EmailAddress, m.WebsiteAddress FROM Members AS m INNER JOIN MembersForType AS t ON m.MemberID = t.MemberID WHERE 
(Category = 'MemberType1' OR Category = 'MemberType2')) as Members 
ORDER BY RND(members.MemberID) DESC

When I run this in Access it returns the rows in different order every time, as per the random sort order. When I run it through my web app however the rows return in the same order every time. Here is how I call it in my code-behind:

private void BindData()
{
    using (AccessDataSource ds = new AccessDataSource("~/App_Data/mydb.mdb", GetSQLStatement()))
    {
        ds.DataSourceMode = SqlDataSourceMode.DataReader;
        ds.CacheDuration = 0;
        ds.CacheExpirationPolicy = DataSourceCacheExpiry.Absolute;
        ds.EnableCaching = false;            
        listing.DataSource = ds.Select(new DataSourceSelectArguments());
        listing.DataBind();
        if (listing.Items.Count == 0)
            noResults.Visible = true;
        else
            noResults.Visible = false;
    }
}

I added in all that stuff about caching because I thought maybe the query was being cached but the result was the same. I put a breakpoint in the code to make sure the query was the same as above and it was.

Any ideas? This is driving me nuts.

Answer 1

I would move the RND into the inner SELECT

SELECT * FROM 
    (SELECT DISTINCT m.MemberID, RND(m.MemberID) as SortOrder, m.Title, 
    m.FullName, m.Address, m.Phone, m.EmailAddress, m.WebsiteAddress 
    FROM Members AS m 
    INNER JOIN MembersForType AS t ON m.MemberID = t.MemberID 
    WHERE 
        (Category = 'MemberType1' OR Category = 'MemberType2')) as Members 
ORDER BY 
    Members.SortOrder DESC

Answer 2

When executing the ACE/Jet RND function against a new connection the same seed value is used each time. When using MS Access you are using the same connection each time, which explains why you get a different value each time.

Consider these VBA examples: the first uses a new connection on each iteration:

Sub TestDiff()

  Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  With con
    .ConnectionString = _
        "Provider=MSDataShape;Data " & _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Tempo\Test_Access2007.accdb"
    .CursorLocation = 3

    Dim i As Long
    For i = 0 To 2

      .Open
      Debug.Print .Execute("SELECT RND FROM OneRowTable;")(0)
      .Close
    Next
  End With

End Sub

Output:

 0.705547511577606 
 0.705547511577606 
 0.705547511577606

Note the same value each time.

The second example uses the same connection on each iteration (the .Open and .Close statements are relocated outside the loop):

Sub TestSame()

  Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  With con
    .ConnectionString = _
        "Provider=MSDataShape;Data " & _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Tempo\Test_Access2007.accdb"
    .CursorLocation = 3

    .Open

    Dim i As Long
    For i = 0 To 2
      Debug.Print .Execute("SELECT RND FROM OneRowTable;")(0)
    Next

    .Close

  End With

End Sub

Output:

 0.705547511577606 
 0.533424019813538 
 0.579518616199493

Note different values each time.

In VBA code you can use the Randomize keyword to seed the Rnd() function but I don't think this can be done in ACE/Jet. One workaround is to use the least significant decimal portion of the ACE/Jet the NOW() niladic function e.g. something like:

SELECT CDBL(NOW()) - ROUND(CDBL(NOW()), 4) FROM OneRowTable