Re: Newbie: VB-ADO help

Thanx for your time and valuable advice Ralph!

Two main comments:
1) As i said, i could have done all this in memory, but the number of
"records" (tuples) **could** be huge, so there is a chance memory will not
be enough. The next, simplest, thing would be, I believe, a "simple" DB
table in Access.

2) I Do have some SQL knowledge and because the calculations are a bit
complicated (= complex querries) I want to avoid that road.
It would require a lot of typing to give you just one example of the types
of calculations.I'll just say this, I have to find the records that
(Date1,X,Y) satisfy specific conditions , calculate the standard deviation,
and add it to the field StDev, in all the records that were chosen.
Repetition = bad relational design , but i dont care, i just need 1 table
for the temporary **space**. I am not attaching a DB to my project.
Its also a matter of "hard-headedness"!... :) I love the simplicity and
convinience of the solution in mind (assuming its possible!...)

Now, regarding the Autonum solution, or i can actually add a field (e.g.
MyCount ) which takes its Values from a counter and gets incremented during
the first loop (where i fill up the values of Date1, X and Y).
After i finish , I call Order with respect to that field, as you suggested.
Then, can I be SURE that the absolute positions would be the right ones
always ????
In other words :

rsCalculations.Sort = "MyIndex"
rsCalculations.MoveFirst

Loop 1 to 20
rsCalculations..MoveNext
End Loop

' At this point the pointer is on record with MyIndex=20 ?????

Thanx again!!
_Steve



"Ralph" <nt_consulting64@xxxxxxxxx> wrote in message
news:Oc2dndZbuK5ZOfvZnZ2dnUVZ_vudnZ2d@xxxxxxxxxxxxxxx
Comments inline..


"steve" <try@xxxxxxxx> wrote in message
news:502d3$44667064$cf706daf$3482@xxxxxxxxxxxx
Hi,

I have the following problem which works but, apparently, its very bad
programming practice.

- I create a table (Access) to store some calculation results because
they
are too numerous for memory storage.
The table has aome fields which will be filled up after the *initial
calculations* so for now they are empty :

Date1 | X | Y | MyFunc1 |
MyFunc2
| ....
19990203 23 8
19990323 31 12
.... ... ...
etc.

The table is created through a couple of nested loops with:

' LOOP 1
'LOOP 2

' Store results in recordset
rsCalculations.AddNew
rsCalculations.Fields("Date1").Value = date
rsCalculations.Fields("X").Value = x
rsCalculations.Fields("Y").Value = y
rsCalculations.Update

'END LOOP 1 and LOOP 2

-Now I have to loop again for a second set of calculations (dependent on
X
and Y) and calculate values for the fields MyFunc1 and MyFunc2.

I loop again and use :

.......

rsCalculations.MoveFirst

loop A

rsCalculations.AbsolutePosition = ((i + (j * jumpDistance)))
rsCalculations.Fields("MyFunc1").Value = myCalcValue

rsCalculations.Update

end loop A

It seems to be working but i keep reading in books, MSDN and the net,
that
this is NOT a good idea since the record number might change.
I am treating my DB Table as a spread***/datagrid and the ORDER in
which
i write to it (and see it when i open the table with Access) is important
for my calculations.


That is correct. It is NOT a good idea.

It would appear that even if you don't wish it to be true, you have done
enough testing to validate it for yourself.


I do not want to deal with SQL and the like. i use a
table purely for space reasons and convinience in calculating.
Is there a better , sure way to do this? MSDN mentions bookmarks without
similar examples.


That's too bad. As you have two choices - learn a smattering of SQL or
abandon using a Relational Database. It is that simple.

If you want to continue using MSAccess then investigate AutoNum fields.
This
will attach an unique sequential number to each record, you can then Order
On this field or correct the Max(), etc. But then that means learning a
bit
of SQL.


How can i guarantee that the order in which i write to the Table is going
to
be preserved and the visual records will be equal to the record numbers?
("line 3" equals record 3, etc.)

If I insert a new field with an increasing counter to guarantee the
order,
then i would have to serially scan ALL records and verify the counter
value
before doing my calculations. This would be too slow.


Not necessarily if you used SQL to query for specific records in a
specific
order.


If i deal with SQL then the SQL statement have to be complicated (for my
level) because my looping structure is complicated. In addition i would
have
to create a second recordset for the calculations and at the end match
the
two into a *final* table.

I am running out of ideas and I would appreciate your educated
suggestions.


Thanx in advance!
-Steve


If you want to continue using MSAccess then investigate AutoNum fields.
This
will attach an unique sequential number to each record, you can then Order
On this field or correct the Max(), etc. But then that means learning a
bit
of SQL.

How many of these things do you have, maybe some kind of simple in memory
linked-list, or other collection would do you just fine.

Your making it harder than it is by continuing to fight against the
stream.

Difficult to give advice as you need to decide - use a Relational
Database,
use some kind of ISAM, or roll 'r own data collection?

-ralph







.