Changing Query Defs in VBA

"strive4peace" wrote in message
...
on using currentdb.tabledefs.refresh --
"Not unless one deleted a table ..."

in my experience, I have found that to not be true ... for instance, if a
series of action queries are run and each depends on the previous one's
results, using the refresh solves the problem of not seeing changes just made.
This could be something that is 'fixed' in later versions of Access, but I am
a creature of habit so now I always do it ... or maybe all I needed was
DoEvents, which I sometimes put after ... hmmm... something to try, thanks
Gunny.

I would not expect refreshing the tabledefs collection to have anything at all
to do with *data* changes. Only should matter if I have changed, added, or
deleted table structures.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi, Crystal.

I think you're right that DoEvents afterwards will allow the operation to
complete before the next query executes. That or a flush cache operation so
that everything is written to disk before the next operation. The extra
time it takes to refresh the collection may be all the delay that was needed
for the record changes from the first action query to be completed,
coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well for me.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"strive4peace" wrote in message
...
on using currentdb.tabledefs.refresh --
"Not unless one deleted a table ..."

in my experience, I have found that to not be true ... for instance, if a
series of action queries are run and each depends on the previous one's
results, using the refresh solves the problem of not seeing changes just
made. This could be something that is 'fixed' in later versions of
Access, but I am a creature of habit so now I always do it ... or maybe
all I needed was DoEvents, which I sometimes put after ... hmmm...
something to try, thanks Gunny.


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*



'69 Camaro wrote:
Hi, Crystal.

after you replace the SQL for a query...

CurrentDb.QueryDefs.Refresh

A collection should be refreshed after an object is added or deleted from
that collection (i.e., a change in the collection), not when one of the
properties of an object in that collection is changed.

after each action query executes, if subsequent queries will need to use
the data in the table(s) you just changed...

currentdb.tabledefs.refresh

Not unless one deleted a table, so that the succeeding code doesn't
assume the table is gone when it may still be in the TableDefs
collection, or one creates a table, to guarantee that the TableDefs
collection includes the new table. A Make-Table query (i.e., "SELECT *
INTO tblArchiveData FROM tblData;") is the only action query that creates
a table. The rest of the action queries (delete, update, and append)
only change records, not the TableDefs collection, so refreshing this
collection in those cases does nothing except slow performance.

HTH.
Gunny

Hi gunny,

Thank you -- well, I am definitely going to stop doing the unnecessary
steps

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



'69 Camaro wrote:
Hi, Crystal.

I think you're right that DoEvents afterwards will allow the operation to
complete before the next query executes. That or a flush cache operation so
that everything is written to disk before the next operation. The extra
time it takes to refresh the collection may be all the delay that was needed
for the record changes from the first action query to be completed,
coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well for me.

HTH.
Gunny

Hi, Crystal.

A single DoEvents after a record-intensive action query works well for me.

I should add that this is only when another action query or other operation
follows that requires the data from the first action query to be written to
disk before it executes, and only if it's a long-running action query (the
first action query, that is). So far it's been uncommon that I've needed
DoEvents, but it's happened, so I know what error messages one receives when
those expected record changes haven't been made in time for the next
operation. It's usually when I get those error messages that I realize I
should have put the DoEvents between the operations.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"'69 Camaro" AM wrote in
message ...
Hi, Crystal.

I think you're right that DoEvents afterwards will allow the operation to
complete before the next query executes. That or a flush cache operation
so that everything is written to disk before the next operation. The
extra time it takes to refresh the collection may be all the delay that
was needed for the record changes from the first action query to be
completed, coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well for me.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"strive4peace" wrote in message
...
on using currentdb.tabledefs.refresh --
"Not unless one deleted a table ..."

in my experience, I have found that to not be true ... for instance, if a
series of action queries are run and each depends on the previous one's
results, using the refresh solves the problem of not seeing changes just
made. This could be something that is 'fixed' in later versions of
Access, but I am a creature of habit so now I always do it ... or maybe
all I needed was DoEvents, which I sometimes put after ... hmmm...
something to try, thanks Gunny.


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*



'69 Camaro wrote:
Hi, Crystal.

after you replace the SQL for a query...

CurrentDb.QueryDefs.Refresh

A collection should be refreshed after an object is added or deleted
from that collection (i.e., a change in the collection), not when one of
the properties of an object in that collection is changed.

after each action query executes, if subsequent queries will need to
use the data in the table(s) you just changed...

currentdb.tabledefs.refresh

Not unless one deleted a table, so that the succeeding code doesn't
assume the table is gone when it may still be in the TableDefs
collection, or one creates a table, to guarantee that the TableDefs
collection includes the new table. A Make-Table query (i.e., "SELECT *
INTO tblArchiveData FROM tblData;") is the only action query that
creates a table. The rest of the action queries (delete, update, and
append) only change records, not the TableDefs collection, so refreshing
this collection in those cases does nothing except slow performance.

HTH.
Gunny

Thanks, Rick -- that was what I initially thought too, but putting the
statement in there took care of problems -- and Gunny explained why ...

to summarize:

TableDefs.Refresh is needed when a table is added or deleted, or the
structure is modified.

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*



Rick Brandt wrote:
"strive4peace" wrote in message
...
on using currentdb.tabledefs.refresh --
"Not unless one deleted a table ..."

in my experience, I have found that to not be true ... for instance, if a
series of action queries are run and each depends on the previous one's
results, using the refresh solves the problem of not seeing changes just made.
This could be something that is 'fixed' in later versions of Access, but I am
a creature of habit so now I always do it ... or maybe all I needed was
DoEvents, which I sometimes put after ... hmmm... something to try, thanks
Gunny.

I would not expect refreshing the tabledefs collection to have anything at all
to do with *data* changes. Only should matter if I have changed, added, or
deleted table structures.

thanks, Gunny

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*



'69 Camaro wrote:
Hi, Crystal.

A single DoEvents after a record-intensive action query works well for me.

I should add that this is only when another action query or other operation
follows that requires the data from the first action query to be written to
disk before it executes, and only if it's a long-running action query (the
first action query, that is). So far it's been uncommon that I've needed
DoEvents, but it's happened, so I know what error messages one receives when
those expected record changes haven't been made in time for the next
operation. It's usually when I get those error messages that I realize I
should have put the DoEvents between the operations.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"'69 Camaro" AM wrote in
message ...
Hi, Crystal.

I think you're right that DoEvents afterwards will allow the operation to
complete before the next query executes. That or a flush cache operation
so that everything is written to disk before the next operation. The
extra time it takes to refresh the collection may be all the delay that
was needed for the record changes from the first action query to be
completed, coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well for me.

HTH.
Gunny

strive4peace wrote in
:

... but when you start talking RecordSource, throw in variable
grouping fields along with alot of records ... there is not
usually a question -- performance is much better if the SQL for
the query is replaced just before the report is rendered than if
the RecordSource for the report is assigned the SQL -- even though
the SQL was just written in both cases!
Why? I do not know.

I have seen no such performance differences in reports. The thing
that most speeds of report performance, in my experience, is
removing tasks performed in the report formatting process. This
includes *not* having "Page [Page] in [Pages]" expressions, which
cannot be renderd on page 1 until the last page of the report has
been formatted. The data retrieval and grouping stages of report
rendering take up much less time than that.

So, we can agree to disagree. I don't see any real-world performance
improvement using saved QueryDefs as reporting recordsources,
except, as I already said, for extremely large data sets and with
very complicated join structures.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"'69 Camaro" AM
wrote in :

I think you're right that DoEvents afterwards will allow the
operation to complete before the next query executes. That or a
flush cache operation so that everything is written to disk before
the next operation. The extra time it takes to refresh the
collection may be all the delay that was needed for the record
changes from the first action query to be completed,
coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well
for me.

I don't understand what you're saying here. DoEvents doesn't have
anything to do with the internal Jet operations -- all it does is
release control to the OS, which can allow screen painting and other
operations to happen asynchronously (when they'd usually have to
wait).

Two action queries executed one after the other *must* in *all*
circumstances have synchronously, i.e., the 2nd can't begin until
the 1st has finished. Anything else would be completely undesirable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

You're welcome, Crystal. Any time.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"strive4peace" wrote in message
...
thanks, Gunny

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*



'69 Camaro wrote:
Hi, Crystal.

A single DoEvents after a record-intensive action query works well for
me.

I should add that this is only when another action query or other
operation follows that requires the data from the first action query to
be written to disk before it executes, and only if it's a long-running
action query (the first action query, that is). So far it's been
uncommon that I've needed DoEvents, but it's happened, so I know what
error messages one receives when those expected record changes haven't
been made in time for the next operation. It's usually when I get those
error messages that I realize I should have put the DoEvents between the
operations.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"'69 Camaro" AM wrote
in message ...
Hi, Crystal.

I think you're right that DoEvents afterwards will allow the operation
to complete before the next query executes. That or a flush cache
operation so that everything is written to disk before the next
operation. The extra time it takes to refresh the collection may be all
the delay that was needed for the record changes from the first action
query to be completed, coincidentally curing the incomplete action
query.

A single DoEvents after a record-intensive action query works well for
me.

HTH.
Gunny

Hi, David.

I don't understand what you're saying here. DoEvents doesn't have
anything to do with the internal Jet operations -- all it does is
release control to the OS, which can allow screen painting and other
operations to happen asynchronously (when they'd usually have to
wait).

.. . . And the completion of the write to disk of the data changes before the
next query begins. And yes, it's possible that this is a coincidence that
there's just enough time delay during the DoEvents operations to appear to
cure the problem, too. ;-)

Two action queries executed one after the other *must* in *all*
circumstances have synchronously, i.e., the 2nd can't begin until
the 1st has finished. Anything else would be completely undesirable.

I've encountered occasions (rarely, mind you) where the last chunk of record
changes hadn't been written to disk yet and the next query errored because
it couldn't find the data that I was expecting to be there already. That's
the problem with a desktop database engine as opposed to a client/server
database engine. The client/server does these operations in memory and has
a snapshot of the data that includes the results of the previous data
manipulations and uses that snapshot for subsequent operations which read
from the snapshot, not from the disk, while the database engine writes to
disk the earlier transactions. (I'm not sure I'm explaining this very
clearly. Sorry.) The desktop database engine doesn't have a snapshot to
read from and always reads what's already been written to disk. The lack of
ability to do these "will be done" operations in memory is one of the
reasons one sees lightning speed in client/server database engines and Jet
seems to take its sweet time.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"David W. Fenton" wrote in message
...
"'69 Camaro" AM
wrote in :

I think you're right that DoEvents afterwards will allow the
operation to complete before the next query executes. That or a
flush cache operation so that everything is written to disk before
the next operation. The extra time it takes to refresh the
collection may be all the delay that was needed for the record
changes from the first action query to be completed,
coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well
for me.

I don't understand what you're saying here. DoEvents doesn't have
anything to do with the internal Jet operations -- all it does is
release control to the OS, which can allow screen painting and other
operations to happen asynchronously (when they'd usually have to
wait).

Two action queries executed one after the other *must* in *all*
circumstances have synchronously, i.e., the 2nd can't begin until
the 1st has finished. Anything else would be completely undesirable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/