Repost: OpenForm Does Not Update Embedded Query

S

SteveM

This is a repost to percolate the unsolved problem to the top:

***********************************************************
I have a Form with a Query source. The associated Table content is
updated with outputs from an Optimization routine.

When I FIRST open the form using code or even from a command button on
a switchbox, the underlying query is not updated to reflect the
optimization results. However if I close the form and open it again
from the switchboard, the query updates properly. This behavior is
not absolute. Sometimes the query will update properly when opened
the first time.
**********************************************************

Update: I've isolated the problem. The embedded query in the Form
does execute. However, an underlying Table that is updated by the
Optimizer is not refreshed until the Form is opened a second time. I
found some code and inserted it after the Optimization call but before
the OpenForm method to see if it would refresh the table:

CurrentDb.TableDefs.Refresh
DoEvents

That does not work. Asking again for ideas.

Thanks Again Too,

SteveM
 
D

Douglas J. Steele

All CurrentDb.TableDefs.Refresh will do is ensure that the TableDefs
collection has the most up-to-date definitions for the structure of the
table: it has nothing to do with the data in the table.

Try

Me.Requery
 
D

Douglas J. Steele

All CurrentDb.TableDefs.Refresh will do is ensure that the TableDefs
collection has the most up-to-date definitions for the structure of the
table: it has nothing to do with the data in the table.

Try

Me.Requery
 
V

vanderghast

Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.



So you probably need either a Me.Refresh, either a Me.Requery. Note that the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.



Vanderghast, Access MVP
 
V

vanderghast

Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.



So you probably need either a Me.Refresh, either a Me.Requery. Note that the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.



Vanderghast, Access MVP
 
S

SteveM

Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.

So you probably need either a Me.Refresh, either a Me.Requery. Note that the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.

Vanderghast, Access MVP
Click to expand...

Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM
 
S

SteveM

Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.

So you probably need either a Me.Refresh, either a Me.Requery. Note that the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.

Vanderghast, Access MVP
Click to expand...

Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM
 
V

vanderghast

You should not call Me.Refresh neither Me.Requery in the procedure answering
to the Open event since the data is already 'fresh' and up to date at that
point. That is just plain useless and a lost of time, and immobilisation of
ressources.

When you say:


The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.


I assume you refer to a procedure of yours. If your form, Solution, is aware
about WHEN that "Optimizer" is run, say because the form Solution call it
(from its VBA code), then add the Me.Refresh or Me.Requery after the call:



Sub SomeSubroutinename()
...
Call Optimizer( probablySomeArgument )
Me.Requery
End Sub




If your form is not automatically aware about when that "Optimizer" is run,
then maybe you have access to the VBA code of that Optimizer (or which
launch that Optimizer), and then, in that code, add code that will requery
the form Solution, if the form Solution is open.


If the "Optimizer" is not VBA code, or if you don't have access to it, and
if it doesn't have any EVENT which is usable to warn YOUR code that the
Optimizer has just run some update, then the only option left (that I see at
the moment) is to use a form timer for Solution, and, in the code handling
the timer, check if the form is dirty (if it is, the user is entering some
data) "do nothing", ELSE, refresh the form:

if Me.Dirty then
else
Me.Refresh
end if


That code can be in the procedure handling the onTimer event of the form
Solution, and you can use a timer interval of 1000 (1000 milliseconds, or
one second), or as it fits for your solution. That is called polling, and is
not considered to be very user friendly, since each 1000 msec, it will
refresh your form (probably consuming network ressources) Solution.


About Me.Refresh, it is not a requery, and what it does, really, is not
documented, probably it pokes its buffers to test if they are still valid,
or something like that. It does not rebuilt the 'bookmarks' and does not
test the records to see if they belong, or not, to those to be usable by the
form.

You can look to query as if they were just method. An update query necessary
updates the tables, or it fails (because of error, like any procedure would
stop on an error). Generally, query update are embedded into a transaction
so you can accept the updates to many records AS IF it was just one single
update made in ONE single step, or you can reject the updates (as the User
Interface does, when DoCmd.SetWarnings is set to true).





Vanderghast, Access MVP



Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by
other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.

So you probably need either a Me.Refresh, either a Me.Requery. Note that
the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.

Vanderghast, Access MVP
Click to expand...

Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM
 
V

vanderghast

You should not call Me.Refresh neither Me.Requery in the procedure answering
to the Open event since the data is already 'fresh' and up to date at that
point. That is just plain useless and a lost of time, and immobilisation of
ressources.

When you say:


The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.


I assume you refer to a procedure of yours. If your form, Solution, is aware
about WHEN that "Optimizer" is run, say because the form Solution call it
(from its VBA code), then add the Me.Refresh or Me.Requery after the call:



Sub SomeSubroutinename()
...
Call Optimizer( probablySomeArgument )
Me.Requery
End Sub




If your form is not automatically aware about when that "Optimizer" is run,
then maybe you have access to the VBA code of that Optimizer (or which
launch that Optimizer), and then, in that code, add code that will requery
the form Solution, if the form Solution is open.


If the "Optimizer" is not VBA code, or if you don't have access to it, and
if it doesn't have any EVENT which is usable to warn YOUR code that the
Optimizer has just run some update, then the only option left (that I see at
the moment) is to use a form timer for Solution, and, in the code handling
the timer, check if the form is dirty (if it is, the user is entering some
data) "do nothing", ELSE, refresh the form:

if Me.Dirty then
else
Me.Refresh
end if


That code can be in the procedure handling the onTimer event of the form
Solution, and you can use a timer interval of 1000 (1000 milliseconds, or
one second), or as it fits for your solution. That is called polling, and is
not considered to be very user friendly, since each 1000 msec, it will
refresh your form (probably consuming network ressources) Solution.


About Me.Refresh, it is not a requery, and what it does, really, is not
documented, probably it pokes its buffers to test if they are still valid,
or something like that. It does not rebuilt the 'bookmarks' and does not
test the records to see if they belong, or not, to those to be usable by the
form.

You can look to query as if they were just method. An update query necessary
updates the tables, or it fails (because of error, like any procedure would
stop on an error). Generally, query update are embedded into a transaction
so you can accept the updates to many records AS IF it was just one single
update made in ONE single step, or you can reject the updates (as the User
Interface does, when DoCmd.SetWarnings is set to true).





Vanderghast, Access MVP



Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by
other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.

So you probably need either a Me.Refresh, either a Me.Requery. Note that
the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.

Vanderghast, Access MVP
Click to expand...

Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM
 
S

SteveM

You should not call Me.Refresh neither Me.Requery in the procedure answering
to the Open event since the data is already 'fresh' and up to date at that
point. That is just plain useless and a lost of time, and immobilisation of
ressources.

When you say:

        The Optimizer repopulates a column in a table call Solution.
        But the changes are not posted to Solution until I open the Form a
        second time.

I assume you refer to a procedure of yours. If your form, Solution, is aware
about WHEN that "Optimizer"  is run, say because the form Solution callit
(from its VBA code), then add the Me.Refresh or Me.Requery after the call:

    Sub SomeSubroutinename()
        ...
        Call Optimizer(   probablySomeArgument  )
        Me.Requery
    End Sub

If your form is not automatically aware about when that "Optimizer" is run,
then maybe you have access to the VBA code of that Optimizer (or which
launch that Optimizer), and then, in that code, add code that will requery
the form Solution, if the form Solution is open.

If the "Optimizer"  is not VBA code, or if you don't have access to it,and
if it doesn't have any EVENT  which is usable to warn YOUR code that the
Optimizer has just run some update, then the only option left (that I seeat
the moment) is to use a form timer for Solution, and, in the code handling
the timer, check if the form is dirty (if it is, the user is entering some
data) "do nothing", ELSE, refresh the form:

    if Me.Dirty then
    else
        Me.Refresh
    end if

That code can be in the procedure handling the onTimer event of the form
Solution, and you can use a timer interval of 1000 (1000 milliseconds,  or
one second), or as it fits for your solution. That is called polling, andis
not considered to be very user friendly, since each 1000 msec, it will
refresh your form (probably consuming network ressources) Solution.

About Me.Refresh, it is not a requery, and what it does, really, is not
documented, probably it pokes its buffers to test if they are still valid,
or something like that. It does not rebuilt the 'bookmarks' and does not
test the records to see if they belong, or not, to those to be usable by the
form.

You can look to query as if they were just method. An update query necessary
updates the tables, or it fails (because of error, like any procedure would
stop on an error). Generally, query update are embedded into a transaction
so you can accept the updates to many records AS IF it was just one single
update made in ONE single step, or you can reject the  updates (as the User
Interface does, when DoCmd.SetWarnings is set to true).

Vanderghast, Access MVP









Thanks for the help men.  But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form.  So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh.  I've tried different coding possibilities but they all
fail.  The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.  So how to post changes to the Solution Table if the
Optimizer does not?  Does Requery apply to Tables or only to Queries?
How about Refresh?  Do these launch recursive updates?  I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial.  Appreciate your iterative
patience.

SteveM
Click to expand...

v, Thanks for the extended remarks. But I was not clear about about
my design space. I have:

1) A Switchboard that I created from scratch
2) The Switchboard contains a Command Button with code that calls the
Optimizer and then Opens a PivotTable Form when the Optimization is
complete
3) The Optimizer is a DLL that Empties a TABLE called Solution and
Refills it with the Optimization solution. This is done outside of
the Access application
4) The data source of the PivotTable Form is a query. One of the
query sources is the Solution table that was filled by the Optimizer
5) It is the Solution table that is not being refreshed before the
code opens the PivotTable Form the first time

So I'm back to the question of where to Me.Requery to refresh the
Solution table either directly or indirectly by opening frmPivot1.

Apologize for taking you 3 times around the block.

SteveM
 
S

SteveM

You should not call Me.Refresh neither Me.Requery in the procedure answering
to the Open event since the data is already 'fresh' and up to date at that
point. That is just plain useless and a lost of time, and immobilisation of
ressources.

When you say:

        The Optimizer repopulates a column in a table call Solution.
        But the changes are not posted to Solution until I open the Form a
        second time.

I assume you refer to a procedure of yours. If your form, Solution, is aware
about WHEN that "Optimizer"  is run, say because the form Solution callit
(from its VBA code), then add the Me.Refresh or Me.Requery after the call:

    Sub SomeSubroutinename()
        ...
        Call Optimizer(   probablySomeArgument  )
        Me.Requery
    End Sub

If your form is not automatically aware about when that "Optimizer" is run,
then maybe you have access to the VBA code of that Optimizer (or which
launch that Optimizer), and then, in that code, add code that will requery
the form Solution, if the form Solution is open.

If the "Optimizer"  is not VBA code, or if you don't have access to it,and
if it doesn't have any EVENT  which is usable to warn YOUR code that the
Optimizer has just run some update, then the only option left (that I seeat
the moment) is to use a form timer for Solution, and, in the code handling
the timer, check if the form is dirty (if it is, the user is entering some
data) "do nothing", ELSE, refresh the form:

    if Me.Dirty then
    else
        Me.Refresh
    end if

That code can be in the procedure handling the onTimer event of the form
Solution, and you can use a timer interval of 1000 (1000 milliseconds,  or
one second), or as it fits for your solution. That is called polling, andis
not considered to be very user friendly, since each 1000 msec, it will
refresh your form (probably consuming network ressources) Solution.

About Me.Refresh, it is not a requery, and what it does, really, is not
documented, probably it pokes its buffers to test if they are still valid,
or something like that. It does not rebuilt the 'bookmarks' and does not
test the records to see if they belong, or not, to those to be usable by the
form.

You can look to query as if they were just method. An update query necessary
updates the tables, or it fails (because of error, like any procedure would
stop on an error). Generally, query update are embedded into a transaction
so you can accept the updates to many records AS IF it was just one single
update made in ONE single step, or you can reject the  updates (as the User
Interface does, when DoCmd.SetWarnings is set to true).

Vanderghast, Access MVP









Thanks for the help men.  But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form.  So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh.  I've tried different coding possibilities but they all
fail.  The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.  So how to post changes to the Solution Table if the
Optimizer does not?  Does Requery apply to Tables or only to Queries?
How about Refresh?  Do these launch recursive updates?  I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial.  Appreciate your iterative
patience.

SteveM
Click to expand...

v, Thanks for the extended remarks. But I was not clear about about
my design space. I have:

1) A Switchboard that I created from scratch
2) The Switchboard contains a Command Button with code that calls the
Optimizer and then Opens a PivotTable Form when the Optimization is
complete
3) The Optimizer is a DLL that Empties a TABLE called Solution and
Refills it with the Optimization solution. This is done outside of
the Access application
4) The data source of the PivotTable Form is a query. One of the
query sources is the Solution table that was filled by the Optimizer
5) It is the Solution table that is not being refreshed before the
code opens the PivotTable Form the first time

So I'm back to the question of where to Me.Requery to refresh the
Solution table either directly or indirectly by opening frmPivot1.

Apologize for taking you 3 times around the block.

SteveM
 
D

david

Does the optimiser disconnect (and flush it's data), or does it remain
connected to a shared table, and wait for the normal delayed write
to write the data to the table?

Does the optimiser block until it is complete, using a synchronous
write, or does it block until it has triggered an asynchronous write,
or does it start a new thread and just return?


(david)

You should not call Me.Refresh neither Me.Requery in the procedure
answering
to the Open event since the data is already 'fresh' and up to date at that
point. That is just plain useless and a lost of time, and immobilisation
of
ressources.

When you say:

The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.

I assume you refer to a procedure of yours. If your form, Solution, is
aware
about WHEN that "Optimizer" is run, say because the form Solution call it
(from its VBA code), then add the Me.Refresh or Me.Requery after the call:

Sub SomeSubroutinename()
...
Call Optimizer( probablySomeArgument )
Me.Requery
End Sub

If your form is not automatically aware about when that "Optimizer" is
run,
then maybe you have access to the VBA code of that Optimizer (or which
launch that Optimizer), and then, in that code, add code that will requery
the form Solution, if the form Solution is open.

If the "Optimizer" is not VBA code, or if you don't have access to it, and
if it doesn't have any EVENT which is usable to warn YOUR code that the
Optimizer has just run some update, then the only option left (that I see
at
the moment) is to use a form timer for Solution, and, in the code handling
the timer, check if the form is dirty (if it is, the user is entering some
data) "do nothing", ELSE, refresh the form:

if Me.Dirty then
else
Me.Refresh
end if

That code can be in the procedure handling the onTimer event of the form
Solution, and you can use a timer interval of 1000 (1000 milliseconds, or
one second), or as it fits for your solution. That is called polling, and
is
not considered to be very user friendly, since each 1000 msec, it will
refresh your form (probably consuming network ressources) Solution.

About Me.Refresh, it is not a requery, and what it does, really, is not
documented, probably it pokes its buffers to test if they are still valid,
or something like that. It does not rebuilt the 'bookmarks' and does not
test the records to see if they belong, or not, to those to be usable by
the
form.

You can look to query as if they were just method. An update query
necessary
updates the tables, or it fails (because of error, like any procedure
would
stop on an error). Generally, query update are embedded into a transaction
so you can accept the updates to many records AS IF it was just one single
update made in ONE single step, or you can reject the updates (as the User
Interface does, when DoCmd.SetWarnings is set to true).

Vanderghast, Access MVP









Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM
Click to expand...

v, Thanks for the extended remarks. But I was not clear about about
my design space. I have:

1) A Switchboard that I created from scratch
2) The Switchboard contains a Command Button with code that calls the
Optimizer and then Opens a PivotTable Form when the Optimization is
complete
3) The Optimizer is a DLL that Empties a TABLE called Solution and
Refills it with the Optimization solution. This is done outside of
the Access application
4) The data source of the PivotTable Form is a query. One of the
query sources is the Solution table that was filled by the Optimizer
5) It is the Solution table that is not being refreshed before the
code opens the PivotTable Form the first time

So I'm back to the question of where to Me.Requery to refresh the
Solution table either directly or indirectly by opening frmPivot1.

Apologize for taking you 3 times around the block.

SteveM
 
D

david

Does the optimiser disconnect (and flush it's data), or does it remain
connected to a shared table, and wait for the normal delayed write
to write the data to the table?

Does the optimiser block until it is complete, using a synchronous
write, or does it block until it has triggered an asynchronous write,
or does it start a new thread and just return?


(david)

You should not call Me.Refresh neither Me.Requery in the procedure
answering
to the Open event since the data is already 'fresh' and up to date at that
point. That is just plain useless and a lost of time, and immobilisation
of
ressources.

When you say:

The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.

I assume you refer to a procedure of yours. If your form, Solution, is
aware
about WHEN that "Optimizer" is run, say because the form Solution call it
(from its VBA code), then add the Me.Refresh or Me.Requery after the call:

Sub SomeSubroutinename()
...
Call Optimizer( probablySomeArgument )
Me.Requery
End Sub

If your form is not automatically aware about when that "Optimizer" is
run,
then maybe you have access to the VBA code of that Optimizer (or which
launch that Optimizer), and then, in that code, add code that will requery
the form Solution, if the form Solution is open.

If the "Optimizer" is not VBA code, or if you don't have access to it, and
if it doesn't have any EVENT which is usable to warn YOUR code that the
Optimizer has just run some update, then the only option left (that I see
at
the moment) is to use a form timer for Solution, and, in the code handling
the timer, check if the form is dirty (if it is, the user is entering some
data) "do nothing", ELSE, refresh the form:

if Me.Dirty then
else
Me.Refresh
end if

That code can be in the procedure handling the onTimer event of the form
Solution, and you can use a timer interval of 1000 (1000 milliseconds, or
one second), or as it fits for your solution. That is called polling, and
is
not considered to be very user friendly, since each 1000 msec, it will
refresh your form (probably consuming network ressources) Solution.

About Me.Refresh, it is not a requery, and what it does, really, is not
documented, probably it pokes its buffers to test if they are still valid,
or something like that. It does not rebuilt the 'bookmarks' and does not
test the records to see if they belong, or not, to those to be usable by
the
form.

You can look to query as if they were just method. An update query
necessary
updates the tables, or it fails (because of error, like any procedure
would
stop on an error). Generally, query update are embedded into a transaction
so you can accept the updates to many records AS IF it was just one single
update made in ONE single step, or you can reject the updates (as the User
Interface does, when DoCmd.SetWarnings is set to true).

Vanderghast, Access MVP









Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM
Click to expand...

v, Thanks for the extended remarks. But I was not clear about about
my design space. I have:

1) A Switchboard that I created from scratch
2) The Switchboard contains a Command Button with code that calls the
Optimizer and then Opens a PivotTable Form when the Optimization is
complete
3) The Optimizer is a DLL that Empties a TABLE called Solution and
Refills it with the Optimization solution. This is done outside of
the Access application
4) The data source of the PivotTable Form is a query. One of the
query sources is the Solution table that was filled by the Optimizer
5) It is the Solution table that is not being refreshed before the
code opens the PivotTable Form the first time

So I'm back to the question of where to Me.Requery to refresh the
Solution table either directly or indirectly by opening frmPivot1.

Apologize for taking you 3 times around the block.

SteveM
 
S

SteveM

Does the optimiser disconnect (and flush it's data), or does it remain
connected to a shared table, and wait for the normal delayed write
to write the data to the table?

Does the optimiser block until it is complete, using a synchronous
write, or does it block until it has triggered an asynchronous write,
or does it start a new thread and just return?

(david)






















v,  Thanks for the extended remarks.  But I was not clear about about
my design space.  I have:

1) A Switchboard that I created from scratch
2) The Switchboard contains a Command Button with code that calls the
Optimizer and then Opens a PivotTable Form when the Optimization is
complete
3) The Optimizer is a DLL that Empties a TABLE called Solution and
Refills it with the Optimization solution.  This is done outside of
the Access application
4) The data source of the PivotTable Form is a query.  One of the
query sources is the Solution table that was filled by the Optimizer
5) It is the Solution table that is not being refreshed before the
code opens the PivotTable Form the first time

So I'm back to the question of where to Me.Requery to refresh the
Solution table either directly or indirectly by opening frmPivot1.

Apologize for taking you 3 times around the block.

SteveM
Click to expand...

David,

Thanks for the note.

The Optimizer is a Mathematical Programming Model Management System.
There is a stand-alone interface I use to develop a model. It has
it's own language. With it, I can read and write from/to data base
files. One command is "EXPORT REFILL TO DATABASE("TableName") and it
does just that. It empties an existing Access table and then refills
it without opening the Access App. How it actually interacts with the
data base is not documented.

I can embed the formulation code inside of Access VBA with an Object
Library of the Modeling System. But the Access data exchange
operations reside with the Model Manager even inside of VBA. This
single nit I have to pick is not having to open the Form twice to see
the updated data. I'm a modeler who programs, not a programmer. So
please forgive my naivete if I'm missing something trivial.

SteveM
 
S

SteveM

Does the optimiser disconnect (and flush it's data), or does it remain
connected to a shared table, and wait for the normal delayed write
to write the data to the table?

Does the optimiser block until it is complete, using a synchronous
write, or does it block until it has triggered an asynchronous write,
or does it start a new thread and just return?

(david)






















v,  Thanks for the extended remarks.  But I was not clear about about
my design space.  I have:

1) A Switchboard that I created from scratch
2) The Switchboard contains a Command Button with code that calls the
Optimizer and then Opens a PivotTable Form when the Optimization is
complete
3) The Optimizer is a DLL that Empties a TABLE called Solution and
Refills it with the Optimization solution.  This is done outside of
the Access application
4) The data source of the PivotTable Form is a query.  One of the
query sources is the Solution table that was filled by the Optimizer
5) It is the Solution table that is not being refreshed before the
code opens the PivotTable Form the first time

So I'm back to the question of where to Me.Requery to refresh the
Solution table either directly or indirectly by opening frmPivot1.

Apologize for taking you 3 times around the block.

SteveM
Click to expand...

David,

Thanks for the note.

The Optimizer is a Mathematical Programming Model Management System.
There is a stand-alone interface I use to develop a model. It has
it's own language. With it, I can read and write from/to data base
files. One command is "EXPORT REFILL TO DATABASE("TableName") and it
does just that. It empties an existing Access table and then refills
it without opening the Access App. How it actually interacts with the
data base is not documented.

I can embed the formulation code inside of Access VBA with an Object
Library of the Modeling System. But the Access data exchange
operations reside with the Model Manager even inside of VBA. This
single nit I have to pick is not having to open the Form twice to see
the updated data. I'm a modeler who programs, not a programmer. So
please forgive my naivete if I'm missing something trivial.

SteveM
 
S

SteveM

David,

Thanks for the note.

The Optimizer is  a Mathematical Programming Model Management System.
There is a stand-alone interface I use to develop a model.  It has
it's own language.  With it, I can read and write from/to data base
files.  One command is "EXPORT REFILL TO DATABASE("TableName") and it
does just that.  It empties an existing Access table and then refills
it without opening the Access App.  How it actually interacts with the
data base is not documented.

I can embed the formulation code inside of Access VBA with an Object
Library of the Modeling System.  But the Access data exchange
operations reside with the Model Manager even inside of VBA.  This
single nit I have to pick is not having to open the Form twice to see
the updated data.  I'm a modeler who programs, not a programmer.  So
please forgive my naivete if I'm missing something trivial.

SteveM
Click to expand...

If anybody has anything but schadenfreude about my troubles, here's a
twist. I inserted a pause using the Timer function between the
Optimization completion and the opening of the Pivot Form that uses
the Solution table. The problem goes away if I insert a delay of at
least 4 seconds. So evidently Access needs to clean up after the
Optimizer has left. But 4 seconds is a long time.

Can I force Access to do its business immediately with a command/
method?

SteveM
 
S

SteveM

David,

Thanks for the note.

The Optimizer is  a Mathematical Programming Model Management System.
There is a stand-alone interface I use to develop a model.  It has
it's own language.  With it, I can read and write from/to data base
files.  One command is "EXPORT REFILL TO DATABASE("TableName") and it
does just that.  It empties an existing Access table and then refills
it without opening the Access App.  How it actually interacts with the
data base is not documented.

I can embed the formulation code inside of Access VBA with an Object
Library of the Modeling System.  But the Access data exchange
operations reside with the Model Manager even inside of VBA.  This
single nit I have to pick is not having to open the Form twice to see
the updated data.  I'm a modeler who programs, not a programmer.  So
please forgive my naivete if I'm missing something trivial.

SteveM
Click to expand...

If anybody has anything but schadenfreude about my troubles, here's a
twist. I inserted a pause using the Timer function between the
Optimization completion and the opening of the Pivot Form that uses
the Solution table. The problem goes away if I insert a delay of at
least 4 seconds. So evidently Access needs to clean up after the
Optimizer has left. But 4 seconds is a long time.

Can I force Access to do its business immediately with a command/
method?

SteveM
 
G

Graham Mandeno

Hi Steve
If anybody has anything but schadenfreude about my troubles, here's a
twist. I inserted a pause using the Timer function between the
Optimization completion and the opening of the Pivot Form that uses
the Solution table. The problem goes away if I insert a delay of at
least 4 seconds. So evidently Access needs to clean up after the
Optimizer has left. But 4 seconds is a long time.

Can I force Access to do its business immediately with a command/
method?

SteveM
Click to expand...

In place of your timer, try using:

DBEngine.Idle dbRefreshCache

Also, if you have any control over the function of the Optimizer, make sure
that it is using a transaction to update the database. CommitTrans will
flush pending writes and release locks.
 
G

Graham Mandeno

Hi Steve
If anybody has anything but schadenfreude about my troubles, here's a
twist. I inserted a pause using the Timer function between the
Optimization completion and the opening of the Pivot Form that uses
the Solution table. The problem goes away if I insert a delay of at
least 4 seconds. So evidently Access needs to clean up after the
Optimizer has left. But 4 seconds is a long time.

Can I force Access to do its business immediately with a command/
method?

SteveM
Click to expand...

In place of your timer, try using:

DBEngine.Idle dbRefreshCache

Also, if you have any control over the function of the Optimizer, make sure
that it is using a transaction to update the database. CommitTrans will
flush pending writes and release locks.
 
S

SteveM

Hi Steve
If anybody has anything but schadenfreude about my troubles, here's a
twist.  I inserted a pause using the Timer function between the
Optimization completion and the opening of the Pivot Form that uses
the Solution table.  The problem goes away if I insert a delay of at
least 4 seconds.  So evidently Access needs to clean up after the
Optimizer has left.  But 4 seconds is a long time.
Click to expand...
Can I force Access to do its business immediately with a command/
method?
Click to expand...
SteveM
Click to expand...

In place of your timer, try using:

    DBEngine.Idle dbRefreshCache

Also, if you have any control over the function of the Optimizer, make sure
that it is using a transaction to update the database.  CommitTrans will
flush pending writes and release locks.

--
Good Luck  :)

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...

Graham,

Thanks a ton. Unfortunately:

DBEngine.Idle dbRefreshCache

Does not work. The Optimizer does have a "Use Transactions" switch
and it is set to On.

So the Schadenfreude...

SteveM
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenForm Does Not Update Embedded Query 4
OpenForm ignores query 5
Requery a parameter query 1
VB updates in form field not updated in tables 1
Macro runs only once from switchboard 2
Updatable ... not 7
Update query not working 2
UPDATE question 1

Top