Comparing data storage

[hbeer444]

I am trying to diod some research on the best places to store data when building a msaccess database application. I have found that yiou can store data in several formats & in different places - each having different advantages as far as scope, speed, useability, memory use, access, etc.

I am wondering anyone has a comparitive table where you can analyze all these differences to determine which type is the best for a certain useage.

The storeage I am talking about is:

scaler variables
nonscaler variable arrays
dictionary objects
collections
properties
controls on forms
tags
recordsets
tables
custom objects

etc

Any leads would be really appreciated!

 

[wiklendt]

i guess that would depend on what you want to do. as a somewhat novice myself, i would say the best place to STORE data is in a table, as for the other options you gave, this is my understanding of each (as a novice, remember!):

forms are used for data entry and data display, not for storage - they display/enter data already in a table or send data TO a table;

tags aren't so much data storage as they are for form control categorisation to ease VBA control of *things*;

recordsets draw on data you've already stored in tables (or organised from tables into queries(?));

properties don't store data but shape how it is displayed and/or manipulated;

...as for the others in the list, i have no experience with those, so cannot say.

of course, when the gurus reply they may be able to tell you about the other options, and would most likely also correct anything i've incorrectly stated BUT i'm pretty confident that TABLES would be your only REAL option - everything else seems to me to be there to better allow you to MANIPULATE your tabled-data, rather than store it.

 

[gemma-the-husky]

i would think the right home for data storage is in a table

alternatives for some things might be

arrays for limited fixed ranges of data - which could be set up internally, read from a table, or even from a text file

the registry is also useful for storing some things.

 

[hbeer444]

yes - I forgot the registry
and of course a text file

I don't necessarily mean storing a lot of data - maybe even one letter or integer!

I was hoping someone has tabled all the advantages of each!!!

Anyone?

 

[wiklendt]

yes - I forgot the registry
and of course a text file

I don't necessarily mean storing a lot of data - maybe even one letter or integer!

I was hoping someone has tabled all the advantages of each!!!

Anyone?

again, it would depend on what you are doing. storing a small amount of data in a table inside access would be EASIER than trying to write code to parse a text file or access the registry.

if you could tell us what you are actually doing and what kind of data you have and also what your expectations or desires are, then we might be able to help.

edit: also you have not said if you require this to work from a server or other systems outside of access.

 

[Rabbie]

yes - I forgot the registry
and of course a text file

I don't necessarily mean storing a lot of data - maybe even one letter or integer!

I was hoping someone has tabled all the advantages of each!!!

Anyone?

It looks like you haven't fully come to grips with how Access works. Data is stored in tables. Queries are used to select data for use in a particular case. Forms are used to input or display data. reports are used to display data.

Of course this applies to data you are storing on a permanent basis. If you are referring to temporary working storage in a VBA routine then it would depend on what you are storing.

More information might help us to help you

 

[The_Doc_Man]

Sounds like a homework assignment.

We don't usually do someone else's homework.

 

[hbeer444]

Sounds like a homework assignment...

LOL

No, I am learning it all on my own

I just know that I am often looking at holding a little bit of information and have used dictionary items, tags, custom database properties, arrays etc but have often wondered say - should I use an array this time, or should I use a dictionary object - maybe even the user db.properties - maybe I'll just put the info in a form openarg so the form can access it when its opened.

It would be nice to see all the disadvantages, advantages all on one table or article to easily refer to it when making these decisions.

I may have to make one up myself - just for my reference. It'll take me some studying first as I only have sporadic experience with VBA over the last 15 years. If I do, I'll have to post it somehow for added input - and maybe even for the benefit of others.

 

[gemma-the-husky]

well its all an art rather than a science. data should go in tables - but to control and manipulate a program, then its more a matter of finding a simple way to do it, - with no hard and fast rules.

so eg if you want to process controls on a form in a certain way, then one of these is often appropriate

a) have a standard control naming convention, so you can determine what type of control you have directly from the name, in which case you can loop thorugh the controls, and process certain named controls in a certain way

or
b) use the tag property of the control to store some info about the control, and react to the tag value



to try and answer your question - its never really processor speed that matters, since a few clock ticks are rarely significant. what matters is getting a structure that you can manipulate it in as easy and efficient a way as possible.

recursive code is a good example. once you understand it, recursion can be used to express in a few lines of code something that can only be done with great difficulty any other way. eg take a tree processing algorithm with recursion - does the whole thing in a handful of lines - now try it without using recursion, and see how hard it is, and how big the code gets

but recursion can be a double edged sword - two many recursive levels can really run slow, and even run out of stack space. you can see this simply with a fibonacci number generator. the recursive code is small, and elegant - but runs increasingly slower for even modest values of n (where n is the nth number in the sequence)


study sorting algorithms, and you will see some of the issues involved. different algorithms are more effective depending on the amount of data to be sorted. and some methods cant be used in access vba, because there is no pointer data type

 

[The_Doc_Man]

OK, I'll be nice.

1. scalar variables & nonscalar variable arrays - basically, using memory to hold data for later application.

PRO: Very fast to find/use. Flexible. Easy to understand (since you designed the memory layout yourself).
CON: Frequently in Access, you want your information stored in a table. There is a hard barrier between variables and tables. It is extraordinarily tedious or outright difficult to bridge that gap. Can be hard on system resources when requirements get big. You have LOTS more disk than you have RAM, but to use variables, you need RAM.
VERY BIG CON: Memory resident variables can vanish on you if you have an unhandled exception in the module that defined the variables.

2. dictionary objects - not even sure how you could control this except by defining some metadata within a project. The object browser could see the metadata.
PRO: not familiar enough with this to address it much.
CON: very difficult to define in a useful way.

3. collections - Office Automation uses collections a lot. A BIG lot. But they are somewhat combersome. Collections of objects that you define can be a useful thing. If the objects are intrinsic to Office, not always as useful as a user-defined thing.
PRO: When a collection is exposed properly, more than one type of program can get to it.
CON: Definitional rules of what goes into a collection can often get in the way. Certain properties are forced by the nature of collections.

4. properties - Objects have properties that can be quite arbitrary in nature. Formats can be anything.
PRO: Where a user-defined object has a set of useful properties, it becomes possible to use the objects to organize the entities and their properties. If the problem is modeling, object properties are the way to go.
CON: Takes a non-trivial amount of setup to get things working right. Also subject to vanishing when the objects are an a context that can vanish due to mis-handled exceptions.

5. controls on forms - This can be just about anything you want it to be, but it is limited in some ways.
PRO: Lots of variability in what is defined. Easy to define a method for converting to long-term storage. Very visual in nature.
CON: Limited by screen space. Content must be saved before the display closes down. I.e. ephemeral.

6. tags - an "Other" property of controls, text oriented.
PRO: Ubiquitous. Capable of retaining useful information of various sorts. Flexible. Easy to get with VBA code.
CON: Limited in number, one per control or form object. Without VBA, very hard to get to.

7. recordsets & tables - THE #1 place to store things long term. Requires a table definition (and one or more address definitions.)
PRO: Capacity is extremely high. Flexibility is pretty good, too.
CON: Not quite as easy to random-access as memory. Easy to screw up the design.

8. custom objects - see discussion of properties.

9. registry - provides lots of space and subroutines exist to visit the registry from Access.
PRO: Elements are uniquely findable.
CON: Interface puts the "U" in Ugly. Danger is always part of getting into the registry, particularly given the bad behavior that can be triggered by hitting the wrong registry key

10. external file - the ultimate in flexibility and capacity.
PRO: You don't even have the Access 2 Gb limit on size.
CON: For really big files, it will take a long time to process. More likely to be linear than random in terms of file addressing.

The above is not meant to be exhaustive or definitive. Just opinions and random thoughts.

 

[hbeer444]

Super - thanks Doc

Now I can hand it in to my teacher.

just kidding (I am sure that teachers are smart enough now adays to search the answers online to see if someone is copying)

I did some reading also on the earlier post re- recursion - wow - but a little over my head right now - will take another look at it.

I do have to hold data for a family tree type set of info, I might have to relook at it as I now hold that data in a multi-dimensional array holding each item & parent info & row col info. Not real pretty but it works.

I have been using the db.properties which I like because it is always retained until its changed - even between sessions!

Very helpful though.

I also did a test of accessing variables & found that it is far quicker to access a scaler variable than a property. I gues 1/1000 second longer, only makes a noticeable difference when you are doing at least 500 of them at a time though.

I was also wondering about memory space. If a person had a 100 variables or properties stored, will it slow down the system the same?

Maybe these things really are not worth being concerned about, however I know the books say - don't make global vars unless you need them, and use object vars to refer to objects as much as you can etc etc.

Thanks for the work you put into your answer - much appreciated.

 

[wiklendt]

I do have to hold data for a family tree type set of info, I might have to relook at it as I now hold that data in a multi-dimensional array holding each item & parent info & row col info. Not real pretty but it works.

have you looked into self-joins?

 

[hbeer444]

Thanks

I will do some research on self joins!

 

[The_Doc_Man]

I was also wondering about memory space. If a person had a 100 variables or properties stored, will it slow down the system the same?

Typically, not as much. Properties are associated with a thing, so there is overhead in finding the right thing for the reference. And that thing will have other infrastructure. Using Scalars, memory contains one item per scalar, even though that item might not be tiny. I believe that all memory-resident items are built as what are called Variant data type and then personalized to become Single, Double, Currency, etc. So they are fixed in size and predictable in location. Could be wrong, though.

 

[gemma-the-husky]

it certainly used to be true that it was more efficent to use variables whose size coincided with the intrinsic word size of the cpu - terminology might be suspect, but i think the idea is correct. all means a single fetch gets the data, i think.

 

[hbeer444]

Thanks all

your responses have been helpful

 

[georgedwilkinson]

Post #17 reported as spam.