G
Guest
I have posted a related issue here and in the Excel daoquery thread before,
but I have narrowed down the issue and, with apologies in advance, am
reissuing a new question thread. I am posting in both places in the hopes
that someone has some insight.
I have a test case that randomizes the order of records using Rnd().
Originally this was used in a select query, but in an effort to stabilize the
debugging environment I'm now using it in a maketable query so that viewing
the result each time doesn't re-execute the select and perturb the results.
The original table (called Test) is very simple: two fields, an autonumber
ID field and a number field called Test which I populated with values 1-6 in
six records. So in essence, the value of the Test field equals the value of
the ID field.
I then wrote a VBA routine in Access to populate a new table called Result
from the original Test table; all it does is scramble the order of the
records randomly. The code for the routine is:
Public Sub TestRoutine()
Dim lSQL As String
On Error Resume Next
DoCmd.DeleteObject acTable, "Result"
On Error GoTo 0
lSQL = "SELECT [Test].*,Rnd([ID]) AS Sort INTO [Result] FROM [Test]
ORDER BY Rnd([ID])"
DBEngine(0)(0).Execute lSQL, dbFailOnError
End Sub
I can execute this from the Access VBA IDE Immediate pane and I get a
different table each time; in other words, executing from Access works as
expected.
I then try to run this routine from Excel VBA. You can't just run in the
Immediate pane because of all the overhead hooking up to Access, so I have a
very simple routine called Test that I wrote in Excel VBA:
Public Sub Test()
Dim lAccess As New Access.Application
Dim lPath As String
lPath = ThisWorkbook.Path
lAccess.OpenCurrentDatabase lPath & "\Test.mdb"
lAccess.Run "TestRoutine"
lAccess.Quit
Set lAccess = Nothing
End Sub
All this does is run the same TestRoutine sub that I was running from the
Access Immediate pane. I can now run this Excel Test sub in the Excel
Immediate pane.
Remember that when I ran this from Access, it correctly generated a new
random-ordered table each time. But when I run from Excel, it generates the
same table each time, in the same order: 4,5,2,3,1,6. The problem is that the
Rnd() function generates the same number every time. For example, the Rnd()
function associated with the 4th original record (which ends up at the top in
the new table) is ALWAYS 0.01401764154.
So even though Rnd() is only being executed in Access as part of an SQL
statement in a routine in Access, calling that routine in Excel makes it run
differently. The Help file in Excel describes its Rnd() function as having
the same semantics as defined in Access.
I've tried using Randomize to reseed the RNG in both Access and Excel; that
doesn't help.
By the way, this problem also happens (in fact my original problem happened)
when simply defining a query with Rnd() in it and using the database
import/update feature in Excel -- in other words, no VBA was used at all, it
was simply executing a query (transparently) and putting the results into
Excel. The query, when executed in Access, worked; when updating through
Excel returned identical results each time. I only went to VBA in an attempt
to control the process explicitly as a workaround, which didn't work.
Any idea how to get Rnd() to generate random sequences in this situation?
Thanks! I'm tearing my hair out here.
but I have narrowed down the issue and, with apologies in advance, am
reissuing a new question thread. I am posting in both places in the hopes
that someone has some insight.
I have a test case that randomizes the order of records using Rnd().
Originally this was used in a select query, but in an effort to stabilize the
debugging environment I'm now using it in a maketable query so that viewing
the result each time doesn't re-execute the select and perturb the results.
The original table (called Test) is very simple: two fields, an autonumber
ID field and a number field called Test which I populated with values 1-6 in
six records. So in essence, the value of the Test field equals the value of
the ID field.
I then wrote a VBA routine in Access to populate a new table called Result
from the original Test table; all it does is scramble the order of the
records randomly. The code for the routine is:
Public Sub TestRoutine()
Dim lSQL As String
On Error Resume Next
DoCmd.DeleteObject acTable, "Result"
On Error GoTo 0
lSQL = "SELECT [Test].*,Rnd([ID]) AS Sort INTO [Result] FROM [Test]
ORDER BY Rnd([ID])"
DBEngine(0)(0).Execute lSQL, dbFailOnError
End Sub
I can execute this from the Access VBA IDE Immediate pane and I get a
different table each time; in other words, executing from Access works as
expected.
I then try to run this routine from Excel VBA. You can't just run in the
Immediate pane because of all the overhead hooking up to Access, so I have a
very simple routine called Test that I wrote in Excel VBA:
Public Sub Test()
Dim lAccess As New Access.Application
Dim lPath As String
lPath = ThisWorkbook.Path
lAccess.OpenCurrentDatabase lPath & "\Test.mdb"
lAccess.Run "TestRoutine"
lAccess.Quit
Set lAccess = Nothing
End Sub
All this does is run the same TestRoutine sub that I was running from the
Access Immediate pane. I can now run this Excel Test sub in the Excel
Immediate pane.
Remember that when I ran this from Access, it correctly generated a new
random-ordered table each time. But when I run from Excel, it generates the
same table each time, in the same order: 4,5,2,3,1,6. The problem is that the
Rnd() function generates the same number every time. For example, the Rnd()
function associated with the 4th original record (which ends up at the top in
the new table) is ALWAYS 0.01401764154.
So even though Rnd() is only being executed in Access as part of an SQL
statement in a routine in Access, calling that routine in Excel makes it run
differently. The Help file in Excel describes its Rnd() function as having
the same semantics as defined in Access.
I've tried using Randomize to reseed the RNG in both Access and Excel; that
doesn't help.
By the way, this problem also happens (in fact my original problem happened)
when simply defining a query with Rnd() in it and using the database
import/update feature in Excel -- in other words, no VBA was used at all, it
was simply executing a query (transparently) and putting the results into
Excel. The query, when executed in Access, worked; when updating through
Excel returned identical results each time. I only went to VBA in an attempt
to control the process explicitly as a workaround, which didn't work.
Any idea how to get Rnd() to generate random sequences in this situation?
Thanks! I'm tearing my hair out here.
