Working with arrays

[supercool]

I agree with tehNellie's points on difference bewteen array and recordset.
but actually, there are also similarity . we could not tell which one is better in general, it really depends on the situation encountered.
e.g only--- there is one table
we have frequent comparision and manipulation of non-fix rows and columns, this time using array ( assign every data field to array element, you could have multi-dimensional arrray) would be more flexible as our modification is more towards dynamic. VBA only provide very limited number of functions for recordset to users. Once issue complexity exceeds certain threshold( everybody might have their own threshold value towards the complexity due to experiences, mood, understanding and etc....

but if we just want to simply update every cell of each record( i use cell to represent each data field...), then recordset might be a wise choice.

I would say the recordset is like a sub-collection of the array( do not misunderstand, here i am just talking about the implication, not logical belongingness). equivalent to say, is
the place we could use recordset, we could also use array( though performance may differ), the place we use array, we might not be able to use recordset ( pls correct me if wrong. )

To move load from point A to point B, submarine and car both may be viable. the determining/dominant factor might be the weight of load, the requirement of delivery date, the cost, the weather, the tax.....

Again thanks tehNellie for his/her car and submarine anaglogy, it inspires me thinking those mess above...

 

[ajetrumpet]

Hello again everyone,

Please keep the topic of this thread about arrays, and any knowledge you have of them. I am sensing that this is turning into a "WaterCooler" candidate. Lets not let it get that way, OK? So far, this is a great reference for anyone who wants to learn about the idea. If the posts get out of hand, I just might have to delete the thread.

Thanks!!

 

[Banana]

^

 

[supercool]

also confused about the message above..????

 

[tehNellie]

I think the recordset issue is just getting in the way to be perfectly honest. To some degree, yes they are similar, you could also compare an array to an Excel Worksheet if you really wanted to, functionally they dont do the same thing, but they share a similar structure (though you can create more than 2 dimension in an array, though that does start to get wierd).

All an array is, is a "fancy" variable that can hold more than one value of, normally, related data.

I've used the name split tool because I think it's a pretty good example of just where an array can be exceedingly useful. It's not "competing" with using a recordset or declaring a class object, arguably it's complementing their functionality and enabling you to vastly simplify your code at times when you don't necessarily know how many variables you'll need, or necessarily what their values will be.

To use another example, I query Active Directory every night and pull all the user information into a SQL server database. To make it easy for my users to sort by, for example, OU I take the Distinguished name out of AD, via a recordset, pass it to an array using split, discard the user CN and the domain information and format the OU details so they make sense to the end user (the DN ou information is "backwards" as is). without using an Array I hate to think what the code would look like. With an array and a couple of loops the whole routine is about 10 lines of code. (i'm sure someone will tell me I can just include [ou] in the query now)

 

[ajetrumpet]

also confused about the message above..????

If it was out of place, then just consider it a reminder, even though you may not need it.

 

[supercool]

If it was out of place, then just consider it a reminder, even though you may not need it.

lost completely, pls let me know what u mean .
reminder??????

 

[ajetrumpet]

Nevermind,

It was just a reference for people who like to carry on about "other" topics when a technical thread has absorbed all the knowledge it was meant to obtain. Or rather, when it has "burned out", kind of TV shows, when they lose their popularity or ratings.

End of Story.

If you have more useful info about arrays, it is more than welcome here.

 

[ajetrumpet]

Here is webpage that I just ran across. The coding is beginner level, but I thought it might be a good reference for anyone wondering what arrays are all about, why they exist, and when you might want or need to use them...

http://www.programmingmsaccess.com/Samples/VBAProcs/VBAProcsToProcessArrays.htm

 

[WayneRyan]

Adam,

I'm surprised that Doc or some of our other "old-timers" (me too!) haven't chimed in
on this thread.

Without a database engine, arrays are mandatory. It used to be the only way to collectively
gather info for summation/min/max purposes. Several of the older programming languages such
as APL and LISP had very elaborate array functions. Using them led to issues such as should
you use a Swap Sort, Bubble Sort, or whatever?

In the current DB world, if I have any collection of data that needs manipulation ... I put
it in a table. Built-in functions will let you Avg/Min/Max/Group or whatever you need to do
with it. There's no reason to re-invent this "new" wheel.

The only reason that I'd see to use an array, other than a very simplistic one-dimensional one,
would be to make something memory-resident with the sole purpose of brute force speed.

From an earlier post, I wouldn't compare an array to a recordset. A recordset is a complex
structure with capabilities FAR beyond a VBA array: Field names, datatypes, ...

A VBA array is more like an unbound form ... roll your own ... you define it and there is very,
very little support (UBound maybe?) to assist you. You define how you use it.

btw,
Not Dissing arrays, for s/w developers such as those fine JET folks, they are still a valuable
tool.

By The Other Way,
Aren't collections just "sophisticated" arrays? Nice of them to add --> Collection.(ItemName)

just some thoughts,
Wayne

 

[supercool]

the thread absorb all the knowledge..?
sorry, i am not quite sure how to find the sign or criteria when the thread was found out to absorb all the knowledge it needed.
I just mean to discuss and share info (if anybody think useful) with all those keen and needed buddies.
If you find info provided by me or other forum users is fallcious or unhelpful, please be forgiving, we didnt mean to fool anybody......

I guess most people's objectives of coming to this forum are to learn, to share and to discuss.
voltaire's motto "Liberty of thought is the life of the soul"

THanks,

 

[Banana]

n the current DB world, if I have any collection of data that needs manipulation ... I put it in a table. Built-in functions will let you Avg/Min/Max/Group or whatever you need to do with it. There's no reason to re-invent this "new" wheel.

Agreed, wouldn't you use array when dealing with raw data (say, an export from Excel for example?)

The only reason that I'd see to use an array, other than a very simplistic one-dimensional one, would be to make something memory-resident with the sole purpose of brute force speed.

I thought that if you create a recordset, it's memory-only and just as fast? (maybe not as fast as it's more complex than array but surely faster than a table, no?)

Aren't collections just "sophisticated" arrays? Nice of them to add --> Collection.(ItemName)

I said collections were useful with objects because you can treat members of collection as objects and access their properties, while doing this in array would be much more slower. The other major advantage is the keys; if you want to hold together a collection, but are never sure what object you need from a given collection, it's much easier to dynamically retrieve a object out of a collection than either looping through the array or holding a pointer to each element of array (which would defeat the purpose of an array). But if it's just raw data, then yes, collection is just a fancy array with bells and whistles.

just some thoughts,
Wayne

and much appreciated!

 

[WayneRyan]

Banana,

Everything we deal with is arrays --> recordsets, result sets, rows, records, whatever.
They're all arrays.

Some arrays, such as recordsets are very elaborate. They support indexes, .Find/.Seek methods,
.RecordCount and .Field properties. Just an array of records with a lot of logic behind it.

Collections such as TableDefs, QueryDefs are also arrays. They also support many properties
and methods for interacting with their individual entries.

We use the above a lot (and appreciate their programmer's labors), but the moment that you
use the following:

Dim arySomeNumbers As Integer(100)

You're on your own!! There are only very limited methods; addition, multiplication, etc.
that you have available. No "table-type" functions available unless you define them in
code.

If I'm collecting raw data and have no "pre-determined" mold to put it into, I'll still table
it while I figure out where it really goes. I still wouldn't see a reason to store it in a
traditional VBA "typed" array.

My exception is that at times Ill use a recordset to collect something like a table's column
names from sysObjects and sysColumns and do a strColumns(i) thing. But I'll still only
iterate through the list gen'ing stuff like SQL code fragments.

Wayne

 

[Banana]

You are absolutely spot on about recordsets, collections and the like being elaborate arrays with methods and properties not otherwise available to the VBA's vanilla array.

In fact, if we were inclined, we could even create our super-duper array. Want a recordset with each child record associated? That can be done with some elbow grease!

But do you have time for it?

Several times, I've came across something that I think to myself, "Wow! This [insert property, method, data type, whatever] looks totally cool and powerful! Maybe I can use it!", but I need to remind myself, "Just because I [b[can[/b] do it, doesn't mean I ough do it this way."

Rabbie has signature that is quite apropos: "The best solution is the simplest solution that meets all requirements."