raising events from the Jet Database

[Ray]

Hi Guys,

I have a Jet database on a central server.
The database is accessed by 2 or more instances of the same application on different PCs.

If the database is edited/updated by 1 instance of the application on a PC how can I tell the other PCs to refresh their screens/recordsets.
Is there a way I can make the Jet database fire an event that my application(s) can use to re-populate their data?

Ray.



--



---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Anon-E-Moose]

If the database is edited/updated by 1 instance of the application on
a PC how can I tell the other PCs to refresh their screens/recordsets.
Is there a way I can make the Jet database fire an event that my
application(s) can use to re-populate their data?

I don't think there is an event you can catch.

Maybe you can check the last modified time... or poll a table with
information about the last transaction?

 

[Ray]

Very hard to pole the database sensibly, to much will load the network, to
little will miss transactions.
I'm thinking I might have to build some kind of application layer over the
database.
One thing is for sure... it's getting very headachey!

 

[Tim Ferguson]

If the database is edited/updated by 1 instance of the application on
a PC how can I tell the other PCs to refresh their screens/recordsets.
Is there a way I can make the Jet database fire an event that my
application(s) can use to re-populate their data?

Access itself will do this when (a) the second user opens a record that
is already open or (b) when someone tries to save a record that is opened
by someone else. There are lock-types you can define for recordsets
behind forms. I have to confess that I find the whole business of record
sharing and locking very confusing, so someone may be along here quite
soon with a better answer.

If you have to have a form open for a long time with a big recordset,
then you can use a Form_Timer to call regular Me.Requery methods -- but
it probably won't be very popular if someone is editing the form when it
goes off!

A better approach is just to minimise the record sharing in the first
place, and this is generally kinder on your network too. Always limit
your forms to a single record, and none of this SELECT * stuff. Close
recordsets as soon as you can; use snapshots rather than dynasets; use
DSUM rather than pulling a whole bunch of records and adding them up. And
so on.

HTH


Tim F

 

[Ray]

what's in my head is this exactly...
3 instances of a grid on 3 diff machines databound to the same database on a
server. When 1 person changes the grid to update the database the other 2
grids are refreshed.


--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Charlie Smith]

If the database is edited/updated by 1 instance of the application on
Ray,

The best answer to this that I have seen is to add a "last updated"
field to your tables and make it a date/time field. Whenever you go
to update a record, compare that field with the one you have on hand
and raise an error if they are different. I know this sounds
disconnected, but that is the idea.

In a network environment with only a few users (the only place you
should be using Access tables anyway) you will be surprised at how
seldom write collisions occur. I know that there are other opinions
on this, but I try to do every call to the database with a stored
procedure. It reduces the amount of data passed over the wire and
allows the type of collision checking described above to be handled at
the server (less network traffic).

Hope this helps,
Charlie

 

[Cor Ligthert]

Ray,

Have a look at optimistic concurrency and pessimistic concurrency because
that is the basic of your question.

With the recordset (or better ADO) it is easier possible to make an
pesimistic concurrency (and Tim is talking about a recordset), with a
dataset (ADONET) this is almost impossible in an efficient way.

Charlie is exactly writting why optimistic concurrency is mostly the best
choise, however not forever.

See this link for deeper information


http://msdn.microsoft.com/library/d...s/cpguide/html/cpconoptimisticconcurrency.asp

I hope this helps?

Cor