How do I automatically requery a table when there have been changes.

[p]

Ok there are multiple people using the same database over a network.
When someone updates a row (data only not table structure) I want the
changes to be reflected in the opened access database of another user
who may be 10 feet or 1000 feet away.

I have accomplished this by putting in a timer trigger which requerys
all opened data every x seconds but this is a band-aid solution as the
database sometimes does nothing for hours and the network activity is
redundent.

I am using a SQL Server EXPRESS 2005 backend with access 2003 as a
front end. I cannot pay for the upgrade to SQL Server normal - so is
there a way I can achieve these goals?

 

[p]

sorry i meant to say that they are all using the same backend. They
have generic client side frontends

 

[Guest]

You can add a command button to the form that requeries the recordset on
demand. I recommend doing this instead of trying to use a timer event
procedure. You can also experiment with the Refresh Interval setting (Tools >
Options | Advanced tab), but I think this only refreshes existing records
(I'm not positive on that).

In a multi-user application, it is best to allow a user to open a bound form
to only one (parent) record. This can be done by creating an unbound search
form that allows the user to locate the record that they want to edit. Then,
when they double-click on the selected record, a new form opens that has only
this one record in it's recordset (this applies to a parent record; there may
be a subform with many related records). Also, I'm not sure that a full
version of SQL Server would solve this issue in any case.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[p]

There is no scope for a user to refresh manually. I know they would
only have to click a button but orders are orders.

I'm starting to think I could just timestamp modifications and do a
requery on the form when the local timestamp doesnt concur with the
server timestamp. Is this a possibility?

 

[Guest]

I'm starting to think I could just timestamp modifications and do a

requery on the form when the local timestamp doesnt concur with the
server timestamp. Is this a possibility?

I suppose, if you are storing a local copy of the data...

I suggest, instead, that you design your forms to allow a user to only open
a record for editing to one (parent) record in the recordset. That way, when
they close the form and open another record for editing, they will
automatically be getting the latest and greatest data. You really don't want
to bog down a multi-user application by allowing users to open forms bound to
large recordsets.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[p]

my apologies for leading to your misinterpretation:

This is an access solution which assists in conferenced telephone
meetings. One form (A) shows the relevent tasks for a weekly period for
5 employees and there is another form (B) which people can edit or add
data. I have (A) refreshing at intervals of 5 seconds which is
sufficient for up to date data to be displayed however (B) shows a
datasheet view of tasks and sometimes does not have the same data as
(A) because while (A) is requering (B) is not. Can I compare the
recordsets of (A) with that of (B) and if there is a difference requery
(B)?