query not refreshing properly

[dpimental]

I have summary query (using standard deviation, avg & count functions. Call it sumquery). It is based upon another query (call it listquery) that lists individuals, groups they belong into and individual mean scores. The listquery is based on a table that gets updated prior to my running this process.

I am selecting from my sumquery and doing a select into an output table (call it outtable). But when I look at the outtable after the process is run, it doesn't seem to refelect all the data. It appears that the sumquery was not refreshed after the source of listquery was updated.

Do I need to refresh this query some how. Do I need to run it first or open it or cycle through it before it gets refreshed?

David Pimental
(US, Oh)

 

[ddiamond]

Are you sure that you are updating your source table before running your queries? Queries will always reflect your source data as of the time the query was run.

 

[dpimental]

Actually, I just moved the process to run the sumtable to the end of the function I am running, and it worked. There must be some sort of timing issue going on?



David Pimental
(US, Oh)

 

[Golom]

There can be timing issues. When you (for example) do an INSERT into a table the database engine doesn't act instantaneously and it is possible that your connection to the database isn't updated to see the changes that have been made.

In DAO you may look at the DBEngine.Idle method that forces the database to update it's status. A similar construct is available in the ADO/JRO environment.

 

[ddiamond]

JRO?

 

[Golom]

Microsoft Jet and Replication Objects x.x Library.

It's a part of the ADO environment that addresses JET-specific functionality.

 

[dpimental]

That information is very valuable. I know where I work, we are working with a very large, application that runs multiple processes on many tables and queries and we often run into issues with objects changing and the engine not realizing it. So, we often put in pauses or loops looking for the change. We havn't figure out a satisfactory solution.

David Pimental
(US, Oh)