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] 4 8900
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 ***
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.
"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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: M Wells |
last post by:
Hi All,
Sorry if the subject line is too obscure -- I couldn't think of a way
of describing this request.
I have a table that contains approximately 1 million records.
I want to be able to be able to select the top x records out of this
table matching variable criteria.
|
by: booner |
last post by:
I have a form that when it loads I would like to highlight the values (from
a DB) that have been selected in a multiple selection list (<select
multiple="true">.
function onLoad()
{
document.forms.elements.value = "<value from
DB>";
}
|
by: Chris Belcher |
last post by:
While I'm sure this is simple I just can't figure it out.
Table A (assignments) is on the One side of a One to Many relationship
With Table B (assignees)There are many assignees assigned the one task.
I have the relationship set to Join all records in Table and only the
records in Table B that Match.
Currently I have a form that...
|
by: Ramesh |
last post by:
hi,
I am selecting fields from three table for manupulating
data and i want to display total number of records
selected. But i am always getting -1 value, eventhough
1000 of records are selected. Below is my code. here
strSelectSQL value is
strSelectSQL = "Select emp.Empno, emp.FirstName,
emp.LastName, emp.DB,...
|
by: Catch_22 |
last post by:
Hi,
I have a stored procedure that has to extract the child records for
particular parent records.
The issue is that in some cases I do not want to extract all the child
records only a certain number of them.
Firstly I identify all the parent records that have the requird number
of child records and insert them into the result table.
| |
by: larry |
last post by:
I am working on a DB for family data, and in this application the data
spans variable amount of rows in multiple tables (one for the adults
data, one for "family", one for the kids, another for employment
schedule, and another for businesses, etc.). I was thinking on
selecting the entire family (IDs of all the related records) and
storing...
|
by: megahurtz |
last post by:
I need to put together an SQL statement and I can't think of how to make it work properly. The scenario is that I have news items in a database that have a launch time and can optionally have an expire time set. I want to select all records that are above the launch time and below their expire time (if it is set). What I have so far is:
...
|
by: Rich P |
last post by:
Now your question makes a lot more sense. It sounds like you want to be
able to drag a group of records - say - from a subform and drop it into
some control for further processing. This kind of functionality would
definitely be convenient.
A guy who is a big time authnor of Access books has published a few
articles describing techniques...
|
by: hollyquinn |
last post by:
Hi I am working with a web application where I am selecting values from a SQL Server 2005 database and then loading the values into different controls on my page. Most of the values load with no problem, except for those columns where the value of the selected row in the column could be a null. I get the following error message:
The value for...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |