Re: Copying a table to an array

John,

Thanks for your help, but I am stuck on this one. I am afraid that it is a
syntax that I have never used before and I can’t get it to work. I have
presumably misunderstood something.

The problem, as you will have gathered, is to create a recordset from a
table to enable data from the latter to be read into an array with GetRows.
To reiterate the difficulty this has created, if the table is not presorted
in a defined order on a specified field the subsequent array is improperly
populated and any subsequent search does not work correctly. There appears
to be no way of guaranteeing that the table will remain ordered and the
exercise has therefore to be repeated prior to each interrogation.

The relevant code fragment, which I have modified to comply with what I
understood to be your suggestion is:-

Dim dbsDBase As Database
Dim rstRSet As Recordset
Dim varArray As Variant
Const conRows = N

Set dbsDBase = OpenDatabase("C:\Applications\DBase.mdb")
Set rstRSet = dbsDBase.OpenRecordset("qry_Get_Data", dbOpenDynaset)
varArray = rstRSet.GetRows(conRows)

The query “qry_Get_Data” selects fields from a table of prices in DBase.mdb,
sorting the latter in ascending item order. conRows is set to some number
guaranteed to exceed the maximum number of rows ever likely to be encountered.

Unfortunately, although the compiler is perfectly happy with this code, it
results in the same run-time error as before. The Set rstRSet command is
rejected with the message that the query cannot be found. As in the previous
case, I have to assume that it is being sought as a table rather than as a
query, which appears to be the same Microsoft Jet Database limitation as
before.

A solution is not vital because I have a relatively simple alternative
presorting routine using an append query but it would be nice to know where I
have gone wrong.

As to the results of the original exercise, they are in some ways
predictable but not in others. Interrogating the price table directly with
queries is surprisingly fast, as already suggested that it would be. The
only advantage in loading the table to an array before searching the latter
for prices is when a significant number of prices is to be returned on any
one search. For individual items, it is difficult to see much difference
between a DLookup and an array interrogation. The difference does though
become noticeable when the table is in a back-end database at the other end
of a network. In that case, predictably, looking up multiple items is
markedly faster from a front-end array than are individual visits to the
back-end, for the obvious reason that the table is only imported up the
network once for each interrogation session. However, the latter does itself
impose a significant run-time overhead and therefore, unless the number of
look-ups per visit is quite large, the time difference still tends to be
rather modest. It looks like a ‘horses-for-courses’ situation. ‘You pays
yer money and you takes yer choice.’

As for the code, the GetRows solution does provide a rather more elegant and
concise solution which is also inherently less dependent on changes in the
table structure. If the problem mentioned earlier can be resolved it might
therefore turn out to be the winner – if only by a short head.



--
Peter Hallett


"John W. Vinson" wrote:

On Tue, 3 Feb 2009 04:02:01 -0800, Peter Hallett
<PeterHallett@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.

That restriction applies only to dbOpenTable as the query type. If you use
dbOpenDynaset it works fine with queries.
--

John W. Vinson [MVP]

.

Loading