Trouble with randomizing getting to Excel

[Guest]

I've got a query that randomizes the order of entries. When I run it in
Access it works.

But I try to access it from Excel, and no matter whether I use the automatic
system that Excel has for updating database info or if I code it in VBA and
literally open the recordset, the "random" numbers ALWAYS are the same for a
given set of data (that should be reordered every time). So even though the
query has data in a different order each time when viewed in Access, the same
query when viewed through Excel is always in the same order.

I thought that perhaps accessing the query from Excel wasn't actually
running the query, but pulling existing data (which I think can't be true,
but work with me here...) but even if I run the query a couple times in
Access and they try again from Excel, the results are the same as before
running the query in Access. In other words, even if the data were static,
changing that static data doesn't solve the problem.

I've used both the following ways to access the query from Excel:

Set lRecords = lAccess.CurrentDb.OpenRecordset("Display Required")

Set lRecords = lAccess.CurrentDb.QueryDefs("Display Required").OpenRecordset

where lAccess is an Access application object. The SQL for the "Display
Required" is:

SELECT [Select Required].Field1, [Select Required].Field2, [Select
Required].Field3, [Select Required].Field4, Rnd([ID]) AS Sort
FROM [Select Required]
ORDER BY Rnd([ID]);

Again, this works in Access, just not when bringing the results to Excel.

I've even tried following the Excel OpenRecordset lines with

lRecords.Sort = "Rnd([ID])"

which is redundant, since that sort is already built into the query, but I'm
trying everything I can think of; still doesn't work.

Doing manual cut and paste from the correctly-working Access view is not a
suitable workaround.

Any ideas what's going wrong here?

Thanks!
BnB

 

[Tom Ellison]

Dear BnB:

A few alternatives come to mind:

- When run from Access, it uses the built in Access functions. Perhaps it
uses built-in Excel functions when run from Excel. Look up an equivalent
Excel function and try that.

- Automate it so Excel uses Access to run the query.

- Just run it from Access.

In order to use Access built in functions, Access has to be loaded and
running, and the query engine must know to use Access to resolve them. From
within other Office applications, it is possible the engine will use that
application's built in functions instead, due to similarities between Office
products. I believe that is intentional.

For help with how to run Excel, you may wish to consult those newsgroups
specifically for Excel. It is not unlikely some Excel expert knows how to
get Excel to correctly resolve an Access query, or how to modify the query.

Tom Ellison

I've got a query that randomizes the order of entries. When I run it in
Access it works.

But I try to access it from Excel, and no matter whether I use the
automatic
system that Excel has for updating database info or if I code it in VBA
and
literally open the recordset, the "random" numbers ALWAYS are the same for
a
given set of data (that should be reordered every time). So even though
the
query has data in a different order each time when viewed in Access, the
same
query when viewed through Excel is always in the same order.

I thought that perhaps accessing the query from Excel wasn't actually
running the query, but pulling existing data (which I think can't be true,
but work with me here...) but even if I run the query a couple times in
Access and they try again from Excel, the results are the same as before
running the query in Access. In other words, even if the data were static,
changing that static data doesn't solve the problem.

I've used both the following ways to access the query from Excel:

Set lRecords = lAccess.CurrentDb.OpenRecordset("Display Required")

Set lRecords = lAccess.CurrentDb.QueryDefs("Display
Required").OpenRecordset

where lAccess is an Access application object. The SQL for the "Display
Required" is:

SELECT [Select Required].Field1, [Select Required].Field2, [Select
Required].Field3, [Select Required].Field4, Rnd([ID]) AS Sort
FROM [Select Required]
ORDER BY Rnd([ID]);

Again, this works in Access, just not when bringing the results to Excel.

I've even tried following the Excel OpenRecordset lines with

lRecords.Sort = "Rnd([ID])"

which is redundant, since that sort is already built into the query, but
I'm
trying everything I can think of; still doesn't work.

Doing manual cut and paste from the correctly-working Access view is not a
suitable workaround.

Any ideas what's going wrong here?

Thanks!
BnB

 

[Guest]

Thanks... already tried posting over there, no responses. I'm running the
routines as "<access application object>.<routine>" which I assume means it's
using the Access library. I could be wrong.

I thought of a workaround. Instead of using a select query, I'll try a
make-table query. Then there's a hard table as a result, which seems like it
should be less ambiguous. In theory. Haven't tried it yet.

Dear BnB:

A few alternatives come to mind:

- When run from Access, it uses the built in Access functions. Perhaps it
uses built-in Excel functions when run from Excel. Look up an equivalent
Excel function and try that.

- Automate it so Excel uses Access to run the query.

- Just run it from Access.

In order to use Access built in functions, Access has to be loaded and
running, and the query engine must know to use Access to resolve them. From
within other Office applications, it is possible the engine will use that
application's built in functions instead, due to similarities between Office
products. I believe that is intentional.

For help with how to run Excel, you may wish to consult those newsgroups
specifically for Excel. It is not unlikely some Excel expert knows how to
get Excel to correctly resolve an Access query, or how to modify the query.

Tom Ellison

I've got a query that randomizes the order of entries. When I run it in
Access it works.

But I try to access it from Excel, and no matter whether I use the
automatic
system that Excel has for updating database info or if I code it in VBA
and
literally open the recordset, the "random" numbers ALWAYS are the same for
a
given set of data (that should be reordered every time). So even though
the
query has data in a different order each time when viewed in Access, the
same
query when viewed through Excel is always in the same order.

I thought that perhaps accessing the query from Excel wasn't actually
running the query, but pulling existing data (which I think can't be true,
but work with me here...) but even if I run the query a couple times in
Access and they try again from Excel, the results are the same as before
running the query in Access. In other words, even if the data were static,
changing that static data doesn't solve the problem.

I've used both the following ways to access the query from Excel:

Set lRecords = lAccess.CurrentDb.OpenRecordset("Display Required")

Set lRecords = lAccess.CurrentDb.QueryDefs("Display
Required").OpenRecordset

where lAccess is an Access application object. The SQL for the "Display
Required" is:

SELECT [Select Required].Field1, [Select Required].Field2, [Select
Required].Field3, [Select Required].Field4, Rnd([ID]) AS Sort
FROM [Select Required]
ORDER BY Rnd([ID]);

Again, this works in Access, just not when bringing the results to Excel.

I've even tried following the Excel OpenRecordset lines with

lRecords.Sort = "Rnd([ID])"

which is redundant, since that sort is already built into the query, but
I'm
trying everything I can think of; still doesn't work.

Doing manual cut and paste from the correctly-working Access view is not a
suitable workaround.

Any ideas what's going wrong here?

Thanks!
BnB

 

[Guest]

OK, here's some more info. I tried the make-table approach; it doesn't work
either.

The problem seems to come down to the Rnd() function. Everything is being
done in Access; the sql and code that create the table are all done in
Access. All that happens in Excel is a <Access application>.Run command.

If I run the routine as a maketable out of the Immediate pane in Access, I
get a new table every time. If I run the same routine in a simple subroutine
that looks like this:

Public Sub Test()
Dim lAccess As New Access.Application
Dim lPath As String

Randomize

lPath = ThisWorkbook.Path
lAccess.OpenCurrentDatabase lPath & <database name>
lAccess.Run "RandomizeRequired", 50

lAccess.Quit

Set lAccess = Nothing

End Sub

and then run "Test" out of the immediate pane repeatedly, it makes the same
table over and over. I thought maybe it was because I needed to add the
Randomize statement; I did that both in the Access code and, as you can see,
in the test routine. That didn't solve it.

So the bottom line is that when the Access routine is run out of Excel, the
Rnd() function which should be operating in Access generates the exact same
sequence every time. When run out of Access, it generates a new sequence
every time.

Any ideas on how to fix this?
BnB

I've got a query that randomizes the order of entries. When I run it in
Access it works.

But I try to access it from Excel, and no matter whether I use the automatic
system that Excel has for updating database info or if I code it in VBA and
literally open the recordset, the "random" numbers ALWAYS are the same for a
given set of data (that should be reordered every time). So even though the
query has data in a different order each time when viewed in Access, the same
query when viewed through Excel is always in the same order.

I thought that perhaps accessing the query from Excel wasn't actually
running the query, but pulling existing data (which I think can't be true,
but work with me here...) but even if I run the query a couple times in
Access and they try again from Excel, the results are the same as before
running the query in Access. In other words, even if the data were static,
changing that static data doesn't solve the problem.

I've used both the following ways to access the query from Excel:

Set lRecords = lAccess.CurrentDb.OpenRecordset("Display Required")

Set lRecords = lAccess.CurrentDb.QueryDefs("Display Required").OpenRecordset

where lAccess is an Access application object. The SQL for the "Display
Required" is:

SELECT [Select Required].Field1, [Select Required].Field2, [Select
Required].Field3, [Select Required].Field4, Rnd([ID]) AS Sort
FROM [Select Required]
ORDER BY Rnd([ID]);

Again, this works in Access, just not when bringing the results to Excel.

I've even tried following the Excel OpenRecordset lines with

lRecords.Sort = "Rnd([ID])"

which is redundant, since that sort is already built into the query, but I'm
trying everything I can think of; still doesn't work.

Doing manual cut and paste from the correctly-working Access view is not a
suitable workaround.

Any ideas what's going wrong here?

Thanks!
BnB