Access 2003.
On a form, I press a button that updates a table using ADODB .Seek
and .Update.
Then I press a second button that does DoCmd.OpenQuery.
The query SOMETIMES doesn't reflect the change I've just made to the
underlying table.
The query ALWAYS gives correct results when it's run a second time, or
when I switch it from Datasheet View to Design View and back.

Waiting a few seconds between pressing the two buttons doesn't help.

I've tried adding an ADODB.Command and using it to surround my VB
update code with BEGIN TRANSACTION and COMMIT. No effect.

Ideas for diagnosing this puzzling behavior?

Composer <composer (AT) uwclub (DOT) net> wrote in
news:f4f8b46b-bf5b-44b7-a88c-9f77e8e27ee3 (AT) t5g2000prd (DOT) googlegroups.com
:

Why are you using ADO?

And Seek and Update? Why not just execute a SQL statement to do the
updates, rather than paging through the recordset to find the
records and changing them one at a time?

This is a good reason not to use two entirely different data
interfaces -- the connection objects are different and won't
necessarily .

Stop using ADO.

If you really need to walk the recordset and make the changes
record-by-record (there are situations where that's the case, but
they really are very rare), use DAO. Better still, convert the
sequential process into a set-based process that uses a single SQL
UPDATE statement to make all the changes in a batch, using DAO (and
run with CurrentDB.Execute). After you've dont that, execute your
query with CurrentDB.Execute. This will insure that the same
connections are used in both cases, and this should insure that
there are no discrepancies between the versions of the data you're
operating on.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Composer wrote:
You're running into Jet's lazy-write optimization. Here's the workarounds:

single connection:
http://support.microsoft.com/?kbid=240317

two connections:
http://support.microsoft.com/kb/200300

Thanks to both of you.

Bob, the KB articles enlightened me. I wish I could have used the
techniques they describe, but I have no control over the way Access
executes a query, so I can't use JRO.JetEngine.RefreshCache and
therefore I can't guarantee that the "reader" will pick up the latest
changes.

David, I originally had what seemed to be a good reason for using Seek
and Update (one of the table fields was a text field that may have
contained funny characters). But now the table's fields are such that
I can write a CurrentDb.Execute statement with confidence. This seems
to have done the trick.

By the way, David, I never "walked the recordset". Using Seek with
the primary key and adSeekFirstEq is probably just as efficient as
CurrentDb.Execute.

My education continues...

Composer <composer (AT) uwclub (DOT) net> wrote in
news:230532b2-12ee-4492-ad33-b4f995476e74 (AT) k10g2000yqa (DOT) googlegroups.co
m:

I would not disagree that it *can* be, given the right conditions.
But as a general approach, it is almost never going to be faster
than a batch update. The more records you're updating, the slower
it's going to be to do it with a recordset.

As to "never 'walked the recordset'" I don't get how you can say
that. You're opening a recordset and navigating through it with
Seek. That's "walking the recordset" even if you're finding only one
record -- a walk of only one step is still a walk. But if you're
updating only one record, it's even more inexplicable why you'd use
a recordset with Seek instead of a SQL UPDATE.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Composer <composer (AT) uwclub (DOT) net> wrote in
news:230532b2-12ee-4492-ad33-b4f995476e74 (AT) k10g2000yqa (DOT) googlegroups.co
m:

BTW, there is just never reason to ever use JRO within Access. Those
KB articles are the unfortunate detritus of the ADO wars, and now
that ADO is dead in Access, you should use the native methods for
the same thing. The equivalent so far as I can see to
JRO.JetEngine.RefreshCache is DBEngine.Idle(dbRefreshCache), and
it's the obvious method to control Jet, since you don't have to add
a reference or use late binding to do it (though if you don't have a
DAO reference, you'll want to use 8 as the literal value for the
dbRefreshCache constant, which is defined in the DAO object
library).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

What do you mean, ADO is dead? I'm using Access 2003 and ADO works
fine. I have no reason to change Access versions, as my clients are
all on Access 2000 or 2003, so if you're telling me that Microsoft has
dropped support for ADO in later versions of Access, then it's
irrelevant to me, and if you're not telling me that, then why say "ADO
is dead"?

Would DAO allow me to create a recordset without an underlying table,
as in Set rs = New ADODB.Recordset ?

Would DAO allow me to store open recordsets in a Collection?

These are honest questions. I don't know the answers. I'm not
especially opinionated or argumentative. I just turn to newsgroups
where I have learned that many people try to help each other. Thanks
for any help.

By the way, Seek does not "walk the recordset", because it finds what
it needs in the Index. Indexes are held separately from the data, are
very concise for their purpose, and may be tree-shaped if the table is
large, to minimize "walking".

And batch updates were never a possibility for my app. One user
presses one button and I have to react to it.

Composer wrote:

ADO of course is as dead as DAO. ADO.Net is the new paradigm. Additionally,
DAO is single-threaded, so if your application is multi-threaded (such as a
web application), you need to use ADO. Otherwise, DAO is more tightly
integrated with Jet and therefore is the data access library of choce for
most people creating single-threaded apps - better performance and more
functionality. There are many things you can do with DAO that you will never
be able to do with ADO (Stephen Lebans used to have a great article on his
site detailing these, but he has retired from Access support so I can't seem
to find the article on his site anymore. If you're really interested, you
can try using the Wayback Machine at www.archive.org to find cached versions
of his old site www.trigeminal.com and see if you can get the article that
way.). However, the reverse is also true. See below

No. Neither will it allow disconnected recordsets, i.e., a recordset that is
opened using a connection to a data source and then is subsequently
disconnected. Very useful when you have to minimize connection time to the
backend, such as when there is a large number of concurrent users. ADO also
has quite a bit of builtin functionality to minimize concurrency issues that
DAO does not offer.
If you mean recordsets that are disconnected from their data source, then
no. Otherwise, why not?

Composer <composer (AT) uwclub (DOT) net> wrote in
news:60aba744-a0f6-4396-b4b6-c0b4c26bc4b5 (AT) t13g2000yqm (DOT) googlegroups.co
m:

Not in further development. No updates for the future. It will
probably be entirely dropped by MS sometime soon.

But DAO would likely work better, as it's the native data interface
for Jet/ACE data. Of course, if your data is not in Jet/ACE format,
then that's a different story, but in this case, the issue was using
JRO to do something you can do with DAO already, and can't do with
ADO at all.

Classic ADO is a dead-end technology that will see no further
development and will likely be deprecated in some near future
version of Access. And it was never at any point a good choice for
interacting with Jet/ACE data. ADO was a bad idea for that from the
beginning and many people recognized that and never used it. They
were vindicated in the A2003 timeframe, and especially with the
release of A2007, when it became clear that the new life of the Jet
database engine (in the form of the ACE) was going to mean that DAO
had a future, while Classic ADO never would (because MS was putting
its efforts into ADO.NET, which is not and probably never will be
compatible with Access).

Disconnected recordsets are something that ADO offers that DAO does
not. It's not a very common need, though, and certainly not
applicable to the current question, which was how to refresh the
Jet/ACE database engine's cache.

I suspect so, though the collection would be provided by VBA, not by
DAO.

Classic ADO is dead as a technology with a future. It's retained in
Access primarily, I think, because of ADPs, which entirely depend on
it for communicating with SQL Server. If you're not using ADPs,
there is really no reason to use ADO except for the handful of
things it can do which ADO cannot (and it really is a handful).

Who said that SEEK "walks the recordset"?

There is hardly ever a justification for using SEEK because there is
almost never a case where a recordset with a restrictive WHERE
clause is not a better choice.

I really don't know what the hell you're talking about. You replied
to one of my posts from over a month ago and are bringing up issues
not addressed in my post. So I really don't have a clue what the
hell you're talking about with this SEEK tangent.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:iaabbj$br1$1 (AT) news (DOT) eternal-september.org:

DAO is *not* dead -- it is in continual development in tandem with
the Jet/ACE database engine.

Web applications should not be using a Jet/ACE datastore, precisely
because of this issue. However, Michael Kaplan always used to say
that when access via ADO, Jet was "threadsafe." I don't know what
that means, but it sounds like a nice thing.

For an Access application with a Jet/ACE back end, it's a no-brainer
to use DAO. The only exception would be if you need to use one of
the very few features of ADO that do not exist in DAO (disconnected
recordsets would be one of them; UserRoster another).

If you're using a different back end, then whether to choose DAO or
ADO depends on whether your using ODBC linked tables or interacting
with your data unbound. In the former case DAO is the most effective
interface (because it's ODBC, which is handled entirely via Jet;
keep in mind that ODBCDirect is dead in A2007/A2010), while in the
latter, you would choose between DAO and ADO based on which
interface gives you the best featureset for that particular database
engine.

In the present case, the issue was not even ADO but JRO, and JRO was
being recommended to do something that DAO can already do. JRO
shouldn't even really exist -- it's just one of the ugly
stepchildren that resulted from MS's misguided ADO-everywhere
campaign with Access. It provides almost no features that are not
already available with DAO and native Access, and those that it does
provide that aren't in DAO really ought to be in DAO (for instance,
there is no excuse for MS implementing programmatic initiation of an
indirect synch of replicated databases in JRO and not in DAO; it was
just churlishness on MS's part to do so, as they tried to kill DAO
in order to favor the less-suitable for Jet purposes ADO).

The number of things ADO can do with Jet/ACE that DAO can't is
actually pretty small. Of those things, very, very few of them are
essential or even very useful.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/