On Oct 30, 10:24*am, b...@barrk.net (Blackwater) wrote:
There's an old database system called "PICK" - still in use in
various forms such as 'OpenQM' - wherein you can SELECT or READ
directly into an array variable. For example :
* *"SELECT * FROM DemoTable TO ArrayVar"
- stuffs ArrayVar with every record in DemoTable in a format :
* *ArrayVar[0][0...nFieldsInDemoTable]
* *ArrayVar[1][0...nFieldsInDemoTable]
* *ArrayVar[2][0...nFieldsInDemoTable]
* *.
* *.
* *ArrayVar[n][0...nFieldsInDemoTable]
(actually PICK is a 'multivalue' DB/OS, so there could be n-dimensions
to each individual field above and beyond this basic structure, ie :
ArrayVar[17][5][222][8][etc...] *)
Anyway, this way of handling a SELECT is very powerful and handy,
allowing you to immediately access every selected record and its
individual fields in code as an ordinary array of variants. If
you processed the array, you could then write it back to 'DemoTable'
without much hassle.
Now the big question is whether Access can be made to do something
like this in an unclumsy manner. I know you can SELECT into a new
table ... but then you're stuck using the one-at-a-time back-n-forth
approach of accessing/manipulating each record, so you may as well
not SELECT into a new table at all. The 'option' here is to SELECT
in the ordinary way and then code a loop of MoveNexts and copy each
field into an array ... but it's a lot cleaner if SELECT can do it
all by itself.
Any info helpful.
I sympathize, but VBA doesn't provide what you're looking for. If all
you're doing is lookups, the domain functions like DLOOKUP are easy to
use. VBA only offers scalar processing on arrays, so you'd be doing
one at a atime, back and forth processing on them, anyway.
The recordset MOVE method allows you to move a specified number of
records forward or back. Recordsets have the AbsolutePosition
property, which lets you go to a specific ordinal record number, if
the provider supports it. You can reference fields by their ordinal
position rather than name, if that helps.