Re: Copying a table to an array

Thanks for the additional information. It has allowed me to complete my
tests, with rather surprising results, referred to in my reply to Mike
Painter.

The table sorting solution, you suggested, unfortunately hit another snag.
The MS Help entry for ‘OpenRecordset’ indicates that the Source parameter can
be “… a table name, query name or SQL statement,” but goes on to say, “For
table-type Recordset objects in Microsoft Jet databases the source can only
be a table name,” – which effectively precludes ordering. I have tried it
by inserting a query with the ordering parameter set to ascending
(effectively an SQL statement, I assume) but a run-time error results, when
the code insists that it cannot find the query. Presumably it is looking for
a table with the query name.

This does not matter too much because the alternative strategy, of first
copying the source table into an ordered working table, using a simple append
query, works well, without introducing more than a few of lines of extra VBA
or much of a run-time overhead.

In the mean time, I am very grateful for your assistance. If nothing else,
I now understand the problem more fully and have undoubtedly expanded my
Access skills. The biggest surprise has been that, contrary to my original
expectation, repeatedly referencing the table with queries does indeed appear
to be significantly faster than first copying the table into an array and
then interrogating the latter. Still, I have come to accept that Access has
plenty of surprises yet to spring.

--
Peter Hallett


"Jon Lewis @btinternet.com>" wrote:

The Name argument of the OpenRecordset method can be an SQL statement
incorporating an ORDER BY clause. So open and sort your recordset before
using GetRows.
I don't think that you can guarantee to maintain the sort order of the rows
in a table. You normally sort the rows when you need to.

HTH







"Peter Hallett" <PeterHallett@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9C224359-255A-482D-B99B-8B7C1216319D@xxxxxxxxxxxxxxxx
Thanks for the suggestion. I implemented the GetRows solution, which,
after
a bit of effort, now works well. It has, however, thrown up another
problem
of its own, which is proving troublesome.

GetRows reads the table in row order. This is fine if the table has been
constructed from scratch but, if records are subsequently added, they are
so
at the bottom of the table, meaning that sort order is generally lost as
the
table is amended. The latter can, of course, be manually sorted but this
only seems to affect the display rather than the basic structure of the
table. If the latter is then copied, the copy appears in the original
unsorted order and it would seem that this is also the order in which it
is
read into the array by GetRows.

There is clearly a need to resort the table, in order of the selected
field
value, immediately before it is read by GetRows and then, ideally, to save
the table in its resorted form. I cannot immediately see how to do this,
however. Again, ages spent with MS Help has not proved very productive.

One solution is to copy the base table to a working table, using an append
query. This can ensure that the records are added in the desired order
but I
suspect that there is an easier way of doing it.

--
Peter Hallett


"Jon Lewis @btinternet.com>" wrote:

Try the GetRows method, of either a DAO or ADO type recordset.

HTH
"Peter Hallett" <PeterHallett@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:105C4A94-B95F-4AC5-9948-1211C3065B8F@xxxxxxxxxxxxxxxx
It would be very useful to be able to copy a table directly into a
two-dimensional array, using VBA. It can be done, of course, with a
series
of queries but the result, particularly for a large table, can look
like a
dog's breakfast. Is there a simpler way of doing it? MS Help does not
appear to be of much help.
--
Peter Hallett






.

Relevant Pages

  • Re: design: single or multiple queries
    ... same query by varying the selection conditions and sort order. ... or move to MySQL or SQL Server. ... How does the query OrderBy property relate to the Sort specs shown ...
    (microsoft.public.access.queries)
  • Re: Last record search not working
    ... It may have been the compact and repair which ... and repair was always going to happen one day, ... lost the natural sort order and gave this problem. ... correct place in a complex set of subqueries: sometimes the query ...
    (microsoft.public.access.queries)
  • Re: sorting columns in a client-side page
    ... Each keyword represents a sort order ... In order to avoid SQL injection, ... the switch, or if it doesn't, you get the default order, which might ... Then when you get ready to do the query, ...
    (comp.lang.php)
  • Re: sorting columns in a client-side page
    ...  Each keyword represents a sort order ... the switch, or if it doesn't, you get the default order, which might ... Then when you get ready to do the query, ...
    (comp.lang.php)
  • Re: Subform changes record order after edits
    ... Did you specify a sort order in the query? ... If you don't specify a sort order, ... >I have continuous form subform "sfrmProductComponents on a mainform ...
    (microsoft.public.access.formscoding)
Loading