SELECTing Records Into an Array Variable ???

**Rich P** · Oct 30 '08, 05:55 PM

Re: SELECTing Records Into an Array Variable ???

Arrays and Recordsets are pretty much the same thing. A recordset is
just an array with a ton of properties. Sql is way more efficient than
Recordsets for processing table data. How clean sql is kind of depends
on one's level of proficiency with the language. Less experienced sql
users write lengthy queries where someone who has been using sql for a
few years with usually write very short sql queries which perform a ton
more operations.

What is your goal with the arrays?

Rich

*** Sent via Developersdex http://www.developersdex.com ***

**brucedodds@comcast.net** · Oct 30 '08, 08:35 PM

Re: SELECTing Records Into an Array Variable ???

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...nFieldsInDe moTable]
ArrayVar[1][0...nFieldsInDe moTable]
ArrayVar[2][0...nFieldsInDe moTable]
.
.
ArrayVar[n][0...nFieldsInDe moTable]
>
(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 AbsolutePositio n
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.

**lyle fairfield** · Oct 30 '08, 10:45 PM

Re: SELECTing Records Into an Array Variable ???

"the big question is whether Access can be made to do something like
this in an unclumsy manner"
Can Access do anything in an un-clumsy manner?

You want to use VBA arrays? Is this a form of self-punishment? You
want to screw up an application? Over-heat your CPU? What?

OK if you gotta you gotta, Have you checked out GetRows()?

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...nFieldsInDe moTable]
ArrayVar[1][0...nFieldsInDe moTable]
ArrayVar[2][0...nFieldsInDe moTable]
.
.
ArrayVar[n][0...nFieldsInDe moTable]
>
(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.

**brucedodds@comcast.net** · Oct 31 '08, 12:55 PM

Re: SELECTing Records Into an Array Variable ???

On Oct 30, 6:39 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :

"the big question is whether Access can be made to do something like
this in an unclumsy manner"
Can Access do anything in an un-clumsy manner?
>
You want to use VBA arrays? Is this a form of self-punishment? You
want to screw up an application? Over-heat your CPU? What?
>
OK if you gotta you gotta, Have you checked out GetRows()?
>
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...nFieldsInDe moTable]
ArrayVar[1][0...nFieldsInDe moTable]
ArrayVar[2][0...nFieldsInDe moTable]
.
.
ArrayVar[n][0...nFieldsInDe moTable]

>

(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.- Hide quoted text -

>
- Show quoted text -

Wow, that's wild. Never knew you could do that.

SELECTing Records Into an Array Variable ???

SELECTing Records Into an Array Variable ???

Comment

Comment

Comment

Comment