Adding data to a recordset

[Karl Irvin]

While adding data to a record set, I use something like

rst![Plot1] = some variable
rst![Plot2] = some variable
......
rst![Plot99] = some variable


The rst![Plot1] , rst![Plot2] etc. are manually typed in and it gets
tedious typing a lot of these.

If there some way to use a loop to setup the field names? Something like

For x = 1 to 99

rst![Plotx] = some variable(x)

next x

[Arno R]

Re: Adding data to a recordset

You need someting like
For x = 1 to 99
rst("Plot" & x) = some variable(x)
next x

Arno R


"Karl Irvin" <88karl3200@com cast.net> schreef in bericht news:qNOdnb6RIs yue8HZnZ2dnUVZ_ v-dnZ2d@comcast.c om...[color=blue]
> While adding data to a record set, I use something like
>
> rst![Plot1] = some variable
> rst![Plot2] = some variable
> .....
> rst![Plot99] = some variable
>
>
> The rst![Plot1] , rst![Plot2] etc. are manually typed in and it gets
> tedious typing a lot of these.
>
> If there some way to use a loop to setup the field names? Something like
>
> For x = 1 to 99
>
> rst![Plotx] = some variable(x)
>
> next x
>
>[/color]

[Lyle Fairfield]

Re: Adding data to a recordset

Most clever developers would not use a recordset for updating data.

If they had to they would use a modern version of Access and ADO as in

Recordset.Updat e aFields, aValues

where aFields is a variant array of field names or ordinals and aValues
is a variant array of, surprise, values.

eg Air Code

Dim aFields(20) as Variant
Dim aValues as Variant
Dim Iterator as Long

For z = 0 to 19
aFields(z) = z
Next z

rst.Update aFields, Array(12, "John", 20.66 ... etc )

Unfortunately the norm for CDMA is the archaic and clumsy DAO.

[Bob Quintal]

Re: Adding data to a recordset

"Lyle Fairfield" <lylefairfield@ aim.com> wrote in
news:1146949861 .307932.192200@ e56g2000cwe.goo glegroups.com:
[color=blue]
> Most clever developers would not use a recordset for updating
> data.
>[/color]
Now you are starting to sound like our good friend Steve.
[color=blue]
> If they had to they would use a modern version of Access and
> ADO as in
>
> Recordset.Updat e aFields, aValues
>
> where aFields is a variant array of field names or ordinals
> and aValues is a variant array of, surprise, values.
>
> eg Air Code
>
> Dim aFields(20) as Variant
> Dim aValues as Variant
> Dim Iterator as Long
>
> For z = 0 to 19
> aFields(z) = z
> Next z
>
> rst.Update aFields, Array(12, "John", 20.66 ... etc )[/color]

you can do something very similar in DAO, like
for z =0 to 19
rst.fields(z) = myArray(z)
next z[color=blue]
>
> Unfortunately the norm for CDMA is the archaic and clumsy DAO.
>[/color]
It's not clumsy at all. You are just a Grumpy Old Man.

--
Bob Quintal

PA is y I've altered my email address.

[Lyle Fairfield]

Re: Adding data to a recordset

Bob Quintal <rquintal@sympa tico.ca> wrote in news:Xns97BBB65 FB2F5DBQuintal@
207.35.177.135:
[color=blue][color=green]
>> For z = 0 to 19
>> aFields(z) = z
>> Next z
>>
>> rst.Update aFields, Array(12, "John", 20.66 ... etc )[/color]
>
> you can do something very similar in DAO, like
> for z =0 to 19
> rst.fields(z) = myArray(z)
> next z[/color]

That's a lot similar ... UH HUH!

--
Lyle Fairfield

[DFS]

Re: Adding data to a recordset

Lyle Fairfield wrote:[color=blue]
> Most clever developers would not use a recordset for updating data.[/color]

A clever developer like me would gladly and repeatedly use the most modern
version of Access and a DAO recordset for adding or updating data if it
provides an advantage over an INSERT or UPDATE.

What advantage, you say? The only one that really matters: because I want
to.


[color=blue]
> If they had to they would use a modern version of Access and ADO as in
>
> Recordset.Updat e aFields, aValues
>
> where aFields is a variant array of field names or ordinals and
> aValues is a variant array of, surprise, values.
>
> eg Air Code
>
> Dim aFields(20) as Variant
> Dim aValues as Variant
> Dim Iterator as Long
>
> For z = 0 to 19
> aFields(z) = z
> Next z
>
> rst.Update aFields, Array(12, "John", 20.66 ... etc )[/color]
[color=blue]
> Unfortunately the norm for CDMA is the archaic and clumsy DAO.[/color]

Versus the less-archaic but still clumsy ADO.

[Lyle Fairfield]

Re: Adding data to a recordset

Absolutely! I'm sure you're happy that Microsoft Insiders revealed
today that MS will soon announce that a new DB connection technology
has superseded ADO.Net. According to unnamed sources it originates from
a need for vast amounts of memory and, as a result is modeled after
Star Trek Deep Space Nine. The connection components themselves will be
known as ODO; MS will offer certification in ODO entitling graduates to
use the moniker ODD after their names.
When asked about the inevitable question, "Why?" Bill Gates
wrinkled his nose and explained that the existence of ADO.Net no longer
seemed to justify DAO proponents in not learning or using ADO. "We
need to look to the future", he said, his gaze drifting off the sky
beyond his window. "We've hooked the Ferengi who use DAO for
another five years this way, because now they can explain their
peculiar clinging to this archaic, obsolete, and thoroughly ugly
technology by pointing out how pointless it would be to learn either
ADO or ADO.Net when both will be replaced soon."

[Bob Quintal]

Re: Adding data to a recordset

So, if I understand, you are saying that Bill Gates is out to
Microshaft us again?

Q

'"Lyle Fairfield" <lylefairfield@ aim.com> wrote in
news:1147021923 .978233.135880@ e56g2000cwe.goo glegroups.com:
[color=blue]
> Absolutely! I'm sure you're happy that Microsoft Insiders
> revealed today that MS will soon announce that a new DB
> connection technology has superseded ADO.Net. According to
> unnamed sources it originates from a need for vast amounts of
> memory and, as a result is modeled after Star Trek Deep Space
> Nine. The connection components themselves will be known as
> ODO; MS will offer certification in ODO entitling graduates to
> use the moniker ODD after their names.
> When asked about the inevitable question, "Why?" Bill Gates
> wrinkled his nose and explained that the existence of ADO.Net
> no longer seemed to justify DAO proponents in not learning or
> using ADO. "We need to look to the future", he said, his gaze
> drifting off the sky beyond his window. "We've hooked the
> Ferengi who use DAO for another five years this way, because
> now they can explain their peculiar clinging to this archaic,
> obsolete, and thoroughly ugly technology by pointing out how
> pointless it would be to learn either ADO or ADO.Net when both
> will be replaced soon."
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

[Lyle Fairfield]

Re: Adding data to a recordset

It's a continuum! Only way we can get out is through a worm hole. Where
is Captain Jainway when we need her? (Maybe you need Seven of Nine
more?)

[Larry Linson]

Re: Adding data to a recordset

"Lyle Fairfield" <lylefairfield@ aim.com> wrote
[color=blue]
> That's a lot similar ... UH HUH![/color]

"Grump, grump, grump," he said, trying to figure out how he can claim that
classic ADO and ADO.NET share more than the letters "ADO" in their name. :-)

[Lyle Fairfield]

Re: Adding data to a recordset

Having learned and used both, why would he care?
ADO.Net has its place. So has ADO. These places are specified in
current MS documentation.
DAO has its place too, in the background work of Access (and probably
other MS technologies that interact with databases.)
But in scripting/coding technology, except for a very few isoated
instances, DAO is clumsy and archaic. The process of DAO recordset
manipulation which we see questioned and answered in CDMA daily is
naive. The correct answer to almost all DAO recordset questions is,
"Don't."

[Larry Linson]

Re: Adding data to a recordset

"Lyle Fairfield" <lylefairfield@ aim.com> wrote
[color=blue]
> But in scripting/coding technology, except
> for a very few isoated instances, DAO is
> clumsy and archaic.[/color]

Care to give a few examples of "clumsy and archaic" versus "not so clumsy
and archaic"? Start from the premise that most of us here are not of the
"newer is better" school of thought, so "newer" isn't going to be a
convincing argument for us.
[color=blue]
> The process of DAO recordset manipulation
> which we see questioned and answered in
> CDMA daily is naive. The correct answer to
> almost all DAO recordset questions is,
> "Don't."[/color]

"Naive" isn't something I associate with a technology, but with a person's
dealing with the world around them. I am convinced that I have correctly
answered many, many DAO questions over the years, and I can't remember a
single time that my answer was "Don't." Perhaps you could clarify what you
mean by the process being "naive".

As the native language of Jet, Microsoft says that DAO is faster. (Though I
rarely access data via DAO, or ADO, for _speed_, but only because the
circumstances dictate using code.) And, if I remember correctly, VBA/DAO is
more complete than ADOX for Jet.

Larry

[Lyle Fairfield]

Re: Adding data to a recordset

Larry Linson wrote:[color=blue]
> Care to give a few examples of "clumsy and archaic" versus "not so clumsy
> and archaic[/color]

The most obvious is the requirement to release in code pointers to DAO
recordsets and databases as in
SET rcs = Nothing
SET db = Nothing
This anomaly of DAO, which can only be classified as a fundamental
failure, has caused tens of thousands of Access errors and accounted
for millions of lines of extraneous and redundant code.
We could go on from there to many areas; one is the inconvenience of
Updates which require Edit directives, then the reassignment of Field
values, and then Update directives. We could name the lack of
disconnected recordsets had not I originated and posted a solution for
this problem (using Transactions) many years ago.
[color=blue]
> "Naive" isn't something I associate with a technology, but with a person's
> dealing with the world around them. I am convinced that I have correctly
> answered many, many DAO questions over the years, and I can't remember a
> single time that my answer was "Don't." Perhaps you could clarify what you
> mean by the process being "naive".[/color]

In my opinion it marks a responder and his/her response as naive,
(inexperienced, raw or unsophisticated ) when the manipulation of a DAO
Recordset is recommended as a solution. Almost always an SQL solution
is to be preferred. As I have pointed out in the once in a million
times where the use of a Recordset may be justified an ADO Recordset is
likely to be more facile than a DAO Recordset.
[color=blue]
> As the native language of Jet, Microsoft says that DAO is faster. (Though I
> rarely access data via DAO, or ADO, for _speed_, but only because the
> circumstances dictate using code.) And, if I remember correctly, VBA/DAO is
> more complete than ADOX for Jet.[/color]

I agree that DAO has a minimal speed advantage over ADO. I believe that
this difference is so small that it will not be noticeable in almost
all cases.

As I have never championed or discussed ADOX to any extent I do not
understand why you would introduce that topic. I do not know of any
problem in JET/Access that can be solved with ADOX that cannot be
solved with ADO and the JET 4.0 SQL. If someone does I should be happy
to hear it.

I have, of course, contributed many DAO solutions to this newsgroup
over many years. Some of them presented or championed new, original
uses for its objects and methods. I am not denigrating a technology
which I do not know I am pointing out that a new and better technology
exists. The failure of many of the regulars here to progress past DAO
3.5/JET 3.5 puzzles me. Is it an innate conservatism? Is it because
they are so successful with DAO that they have no time to learn
anything new? Is it because they are successful, satisfied and fat with
DAO and do not want to learn anything new? Is it because they are
intolerant of the dissonance that might be caused by using two similar
technologies?

I will not be posting further to this thread.

[Larry Linson]

Re: Adding data to a recordset

"Lyle Fairfield" <lylefairfield@ aim.com> wrote
[color=blue][color=green]
>> Care to give a few examples of "clumsy and
>> archaic" versus "not so clumsy and archaic[/color]
>
> The most obvious is the requirement to
> release in code pointers to DAO
> recordsets and databases as in
> SET rcs = Nothing
> SET db = Nothing
> This anomaly of DAO, which can only be
> classified as a fundamental failure, has caused
> tens of thousands of Access errors and accounted
> for millions of lines of extraneous and redundant code.[/color]

IIUC, what you describe is a "bug" that Microsoft has either been unable to
find and fix, or chose not to fix. I've not found it to be a problem to
include it in those instances where the circumstances dictated using DAO.
[color=blue]
> We could go on from there to many areas;
> one is the inconvenience of Updates which
> require Edit directives, then the reassignment
> of Field values, and then Update directives.[/color]

Once you know what is required, this isn't a problem. I suppose it is a
problem, if you choose for it to be, though.
[color=blue]
> We could name the lack of disconnected
> recordsets had not I originated and posted a
> solution for this problem (using Transactions)
> many years ago.[/color]

Well, if I ever find myself needing to use disconnected recordsets (seems to
be a function of doing more data access in code than I prefer to do), I'll
remember and Google your solution. In any case, once you solved it, didn't
it cease to be a problem.
[color=blue][color=green]
>> "Naive" isn't something I associate with
>> a technology, but with a person's dealing
>> with the world around them. I am convinced
>> that I have correctly answered many, many
>> DAO questions over the years, and I can't
>> remember a single time that my answer was
>> "Don't." Perhaps you could clarify what you
>> mean by the process being "naive".[/color]
>
> In my opinion it marks a responder and his/her
> response as naive, (inexperienced, raw or unsophis-
> ticated) when the manipulation of a DAO
> Recordset is recommended as a solution. Almost
> always an SQL solution is to be preferred.[/color]

I don't recall responding with "Don't" but I have often suggested an SQL or
Query solution -- most often, however, that SQL or Query had to be executed
some way, and that way was with DAO.
[color=blue]
> As I have pointed out in the once in a million times
> where the use of a Recordset may be justified an
> ADO Recordset is likely to be more facile than
> a DAO Recordset.[/color]

I translate this to mean "You like it better."
[color=blue]
> As I have never championed or discussed ADOX to any extent I do not
> understand why you would introduce that topic. I do not know of any
> problem in JET/Access that can be solved with ADOX that cannot be
> solved with ADO and the JET 4.0 SQL. If someone does I should be happy
> to hear it.[/color]

I bring it up because it is part of ADO, for creating data objects, and is
less full-featured than the similar DAO functions.
[color=blue]
> ... The failure of many of the regulars here to progress
> past DAO 3.5/JET 3.5 puzzles me. Is it an innate conser-
> vatism? Is it because they are so successful with DAO
> that they have no time to learn anything new? Is it because
> they are successful, satisfied and fat with DAO and do
> not want to learn anything new? Is it because they are
> intolerant of the dissonance that might be caused by
> using two similar technologies?[/color]

If those developers have not progressed beyond 3.5, then they must also have
not progressed beyond Access 97. While there are many who think that Access
97 was a good release, perhaps the best-ever release, I think most of the
regulars here have moved on over time and now use later versions, which, per
se, means they are not using DAO 3.5 nor Jet 3.5.

My initial evaluation of ADO (which was hyped as better because of its
"simpler object model") was that all the same information was required to
accomplish a given I/O operation, but was just arranged in different
statements. I haven't changed that view, but I didn't have any objection to
the object model of DAO. About the time ADO came out, my work changed so
that instead of most of it being Access clients to servers, most was
Access - Jet (and the largest client-server system on which I worked since
was an ADP that used ADO -- far more than was needful, because the original
author had not realized that it was necessary to define a primary key on SQL
Server tables to use bound forms). The ADO in that application did not
change my opinion that investing time and effort in learning it would
benefit my MDBs.

Clearly, you have a preference for ADO and I have a preference for DAO.
Both, used appropriately, can satisfy application needs, so this is an area
where we can agree to disagree, civilly and politely, I think.
[color=blue]
> I will not be posting further to this thread.[/color]

Thanks for giving me the last word. Thanks also for the many correct, even
elegant solutions you have posted here in many areas.

Larry Linson
Microsoft Access MVP

[Bob Quintal]

Re: Adding data to a recordset

"Lyle Fairfield" <lylefairfield@ aim.com> wrote in
news:1147030989 .100269.325740@ u72g2000cwu.goo glegroups.com:
[color=blue]
> It's a continuum! Only way we can get out is through a worm
> hole. Where is Captain Jainway when we need her? (Maybe you
> need Seven of Nine more?)
>[/color]
I have a need for her twin sister, Two of Thirty-Eight,Double-Dee.



--
Bob Quintal

PA is y I've altered my email address.