Problem with query that uses SQL Server view instead of table

[Guest]

Hi,

Several users and myself have observed an annoying problem in my Access 2003
app, where a combo box is not consistently being refreshed after new records
are added. This seems to occur when the combo box's query is based on a SQL
Server 2005 Express view, rather than the multiple related tables. It occurs
both in Jet queries (based on the linked view) and in pass-through queries.

For example, one combo box displays a list of contacts. Users can add a
contact by clicking a button and entering it in a pop-up screen. When the
screen closes the combo box is requeried to display the new contact.

The behavior seems semi-random, possibly time-sensitive - when doing this
ten times in a row, it will typically refresh correctly one to three times,
then the next one to four additions will not be shown, then the following
addition brings the query up to date and displays all records in including
the latest, then new additions stop appearing again, etc.

The behavior is not limited to the combo box. Opening the underlying query
directly shows the same problem.

Why would this occur with views, not table-based queries? Is there a way to
fix this in the linked view's definition in Access, or in data cache settings
somewhere, or is it a SQL Server problem?

Thanks!

 

[Guest]

I have saved a trace in SQL Server profiler and it seems that Access is not
the problem. The same queries are issued each time by the client application,
but the number of rows returned by SQL Server sometimes includes the newer
records, sometimes not. Every time the query is issued between 1 and 29
seconds after the top of each minute, the view-based query runs correctly.
Every time the query is issued between 30 and 59 seconds of each minute, the
view-based query does not return newly added records.

(I will repost this in a SQL Server usergroup)