Copying a table to an array

  • Thread starter Thread starter Peter Hallett
  • Start date Start date
P

Peter Hallett

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 said:
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.
Click to expand...

You can open a recordset and loop through it.
What can you do with an array that can't be done a lot faster with a query?
For small tables it is academic but even then find the first value = "Blue
Widget" with .findfirst will be a lot faster than looping through the array.
 
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.
 
Yes, it was a bit of toss-up whether to operate directly on the table or copy
it into an array first. The application, however, entails multiple
interpolations between the table values in order to calculate a number of
results. Since this requires repeated reference to row and column
co-ordinates, it just seemed easier to load the table to an array before
starting the analysis. I could, though, be wrong.

The main requirement is for a general purpose routine that does not require
code changes when the table is amended or extended and one advantage of the
GetRows technique is that it automatically resizes the array when the table
is changed. (UBound can be used to determine the new size) but I am quite
prepared to accept that a query-based method could also be made to work.
 
I have a very complex set of queries that ends up with a recordset and an
associated form.
One of those queries involves a Cartesian set which may give duplicate
results.
AB and BA are duplicates in this case and I must eliminate all of them
before running he next query.
Code was a deal breaker. Even on sample data it was taking many seconds to
loop through everything. My queries took less than a second to open the
form.
I'd suggest you look at a SQL solution
 
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
 
Your assurance that a solution based on direct table interrogation, using
SQL, would be faster than first copying the table to an array, prompted me to
recast the code accordingly.

You are indeed right. Much to my surprise, the resulting VBA runs
significantly more quickly than does the array searching alternative, which
is particularly gratifying because I prefer to work with queries. The one
question that remains is whether the result would still be swifter if the
table were located in a back-end database at the other end of a LAN. I
suppose that the only way of finding out is to ‘suck it and see’. Meanwhile,
please accept my thanks for your help. Developing a solution is one thing
but being confident that it is appropriate is quite another.
 
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.
 
I thought I had posted this recently, but maybe that was on a different thread. You need
to have a reference set to the ADO library, but it works pretty well.

I have this function set to return a single row or the whole table (or query). Pass a -1 as
the lRec value and you get everything. Pass 0 to n to get any other specific row.

Danny


Function GetQueryValues(ByVal sQuery As String, ByVal lRec As Long) As Variant
On Error Resume Next

Dim varValues() As Variant
Dim varOutput() As Variant
Dim intFields As Integer
Dim intCurrent As Integer

Dim rs As ADODB.Recordset

Set rs = CurrentProject.Connection.Execute(sQuery)

If Not rs.BOF And Not rs.EOF Then
varValues() = rs.GetRows

If lRec = -1 Then
varOutput() = varValues()
Else
If lRec > UBound(varValues, 2) Then lRec = UBound(varValues, 2)
ReDim varOutput(UBound(varValues))
For intFields = 0 To UBound(varValues)
varOutput(intFields) = varValues(intFields, lRec)
Next
End If
End If
Set rs = Nothing

GetQueryValues = varOutput

End Function
 
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.
Click to expand...

That restriction applies only to dbOpenTable as the query type. If you use
dbOpenDynaset it works fine with queries.
 
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,

Looks to me as if you are making this more complicated than it needs
to be. You probably don't need a query, just use this :-

Dim rstRSet As Recordset
Dim varArray As Variant

Set rstRSet = CurrentDB.OpenRecordset("SELECT * FROM YourTable
ORDER BY YourSortField")
varArray = rstRSet.GetRows(10000)

where YourTable is the name of your table.
YourSortField is the name of the field you are sorting on.
The value 10000 is a number which is greater than the number of
records you are likely to return.
Note that the Set rstRSet--- line should all be on the same line in
your code.

If you do not need all the fields in the table returned then you can
either substitute the table name for a query name which returns the
fields you want or you can include the field names in the SQL
definition, something like :-

("SELECT Field1, Field2, Field3 FROM YourTable..etc

Don't forget that you will need square brackets aroung the table and
field names if they have spaces or non-alphanumeric characters in
them, i.e. [Field 2]

HTH

Peter Hibbs.


On Fri, 6 Feb 2009 12:06:05 -0800, Peter Hallett <Peter
 
Peter,

You were quite right. I was rather trying to do it standing up in a
hammock. Your solution, however, is neat and works perfectly. Many thanks.

I have to admit that I have tried to avoid SQL, wherever possible, generally
hoping that queries would do the job for me but, as this case has made clear,
“It ain’t necessarily so.†I have therefore decided to mend my ways and have
just downloaded a 90-page SQL tutorial from the Web. I’ll make a large pot
of coffee and study it. In the mean time, many thanks for your help


Peter Hibbs said:
Peter,

Looks to me as if you are making this more complicated than it needs
to be. You probably don't need a query, just use this :-

Dim rstRSet As Recordset
Dim varArray As Variant

Set rstRSet = CurrentDB.OpenRecordset("SELECT * FROM YourTable
ORDER BY YourSortField")
varArray = rstRSet.GetRows(10000)

where YourTable is the name of your table.
YourSortField is the name of the field you are sorting on.
The value 10000 is a number which is greater than the number of
records you are likely to return.
Note that the Set rstRSet--- line should all be on the same line in
your code.

If you do not need all the fields in the table returned then you can
either substitute the table name for a query name which returns the
fields you want or you can include the field names in the SQL
definition, something like :-

("SELECT Field1, Field2, Field3 FROM YourTable..etc

Don't forget that you will need square brackets aroung the table and
field names if they have spaces or non-alphanumeric characters in
them, i.e. [Field 2]

HTH

Peter Hibbs.


On Fri, 6 Feb 2009 12:06:05 -0800, Peter Hallett <Peter
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.
Click to expand...
Click to expand...
 
Peter,

My pleasure, the simplest answers are usually the best.

Peter Hibbs.

Peter,

You were quite right. I was rather trying to do it standing up in a
hammock. Your solution, however, is neat and works perfectly. Many thanks.

I have to admit that I have tried to avoid SQL, wherever possible, generally
hoping that queries would do the job for me but, as this case has made clear,
“It ain’t necessarily so.” I have therefore decided to mend my ways and have
just downloaded a 90-page SQL tutorial from the Web. I’ll make a large pot
of coffee and study it. In the mean time, many thanks for your help


Peter Hibbs said:
Peter,

Looks to me as if you are making this more complicated than it needs
to be. You probably don't need a query, just use this :-

Dim rstRSet As Recordset
Dim varArray As Variant

Set rstRSet = CurrentDB.OpenRecordset("SELECT * FROM YourTable
ORDER BY YourSortField")
varArray = rstRSet.GetRows(10000)

where YourTable is the name of your table.
YourSortField is the name of the field you are sorting on.
The value 10000 is a number which is greater than the number of
records you are likely to return.
Note that the Set rstRSet--- line should all be on the same line in
your code.

If you do not need all the fields in the table returned then you can
either substitute the table name for a query name which returns the
fields you want or you can include the field names in the SQL
definition, something like :-

("SELECT Field1, Field2, Field3 FROM YourTable..etc

Don't forget that you will need square brackets aroung the table and
field names if they have spaces or non-alphanumeric characters in
them, i.e. [Field 2]

HTH

Peter Hibbs.


On Fri, 6 Feb 2009 12:06:05 -0800, Peter Hallett <Peter
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.
Click to expand...
Click to expand...
Click to expand...
 
I have to admit that I have tried to avoid SQL, wherever possible, generally
hoping that queries would do the job for me but, as this case has made clear,
“It ain’t necessarily so.” I have therefore decided to mend my ways and have
just downloaded a 90-page SQL tutorial from the Web. I’ll make a large pot
of coffee and study it. In the mean time, many thanks for your help
Click to expand...

Just bear in mind - queries ARE SQL already. The query design grid isn't the
query; it's just a tool to help you construct SQL, which is the real query. A
good learning tool is to build a query in the grid and then choose SQL view to
see how the query you entered reads in SQL.
 
Jon,

I am grateful for your additional clarification not least because it
confirms my previous understanding. You will understand my surprise,
therefore, when the OpenRecordset command, that formed the subject of our
previous discussion, failed to accept a query, demanding SQL instead.

I rely heavily on queries, not only because they are quick and powerful but
also because, by avoiding an awful lot of verbiage, they tend to produce much
more concise code. There are also instances where their use can avoid
significant difficulties. The syntax of DLookup, for example, is
DLookup(expression,domain,criteria), as you well know, but using this to look
up a name in a table can lead to unexpected difficulties. If the name
(criterion) happens to contain an apostrophe, as in O’Leary for instance,
then getting the DLookup to work properly can be tricky. However, if the
domain and criteria be replaced by a query, the thing works fine. As a
result I always now use the syntax DLookup(expression, query). It was
therefore a considerable disappointment to discover a situation where this
sort of approach simply did not work.
 
Back
Top