what is the difference between a 2dimentional array and a recordse

  • Thread starter Thread starter george
  • Start date Start date
G

george

Hi to all,

if I want to temporarily store data in order to use them later can I load
them in either one of them?

George
 
A 2 dimensional array is a variable used to hold values that roughly equate
to a row/columns such as you might find in an Excel worksheet. The array is
entirely temporary in that it is not saved once it has gone out of scope. If
you Dim it and load it within a function, the data is lost as soon as the
function finishing processing. Same thing for a SUB. If you declare the array
at the module level it is available until the module goes out of scope. If
you declare it at the global level its available as long as Access is running.

A recordset is literally the set of records being worked with whether in
code or displayed on a form. There is also the Recordset object which is a
VBA object through which you can manipulate records in the database.

What are you trying to accomplish? While you can certainly load information
from a database into an array, the typical approach is access the records as
you need them. There are other possibilities as well.
 
Basicaly a 2D array is a bunch of data in memory of a single PC (no
multi-users, no predefined mechanic to save on permanent medium the changes,
no index) while a recordset is a collection of 'bookmarks' (keys) while
allowing you to reach data of one 'row' of data, eventually with 'write'
capability (or not), caching the data (from the hard-disk where the data is
stored) or not.

You also have to consider how your 2D array will be filled with the data,
probably more work, for you, than 'defining' the recordset. And if you
already know that you only want some subset of your data, the recordset
already offer you the possibility to use a WHERE clause, so you don't have
to carry all the data, through the cable, most of it rejected because not
matching what you need.


You will also have to sort, and filter, by code to be supplied by you, if
you use 2D array. And inserting new 'rows' at the right place, moving all
other rows down, or up if there are DELETING possibilities.


So, if you data is static (fix, unmodifiable), single user, 2D array can be
ok, but for anything else, recordset... or even better, table, can be much
better.


Vanderghast, Access MVP
 
Thanks David, take care.

David H said:
A 2 dimensional array is a variable used to hold values that roughly equate
to a row/columns such as you might find in an Excel worksheet. The array is
entirely temporary in that it is not saved once it has gone out of scope. If
you Dim it and load it within a function, the data is lost as soon as the
function finishing processing. Same thing for a SUB. If you declare the array
at the module level it is available until the module goes out of scope. If
you declare it at the global level its available as long as Access is running.

A recordset is literally the set of records being worked with whether in
code or displayed on a form. There is also the Recordset object which is a
VBA object through which you can manipulate records in the database.

What are you trying to accomplish? While you can certainly load information
from a database into an array, the typical approach is access the records as
you need them. There are other possibilities as well.
Click to expand...
 
Mr Vanderghast thank you for your time and efford.
I have already loaded my data into a 2dimentional array, avar(1 to 10, 1 to
4) and now I need to permanently move these data into a 4 field table. Is
there a standard way to do this or do I need to go back and work out a method
to load the data into a recordset and then move them from there permanently
into the second table?
My data now, as I said before, is already loaded into the array so it would
be easier for me to start from an array if there is a way.

thanks, george
 
There is a way from recordset TO an array, see the method GetRows, but from
an array to a recordset... you will have to loop over the lines of you array
and append one record in the recordset, at a time. You can also append one
record at a time in a table (witout the help of a recordset, that may be
faster) with an sql statement like:

INSERT INTO tableName (listOfFields) VALUES(listOfValues)


like:

INSERT INTO table1(f1, f2, f3) VALUES( 122.3, "George", #10/10/2010
10:10:10# )


That probably means you would create the statement as a string an execute
it:


DoCmd.RunSQL "INSERT INTO table1(f1, f2, f3) VALUES(
FORMS!tempForm!control1, FORMS!tempForm!Control2, FORMS!tempForm!control3) "

as example where using a temporary form (invisible if you don't want the end
user seeing it) makes the formulation easier, since you don't need to worry
about special delimiters.



Vanderghast, Access MVP
 
Back
Top