Live record counts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have 2 MS Access front end guis we use to enter data into an MS Access
master table, stored in a llinked MS Access database on a shared network
drive. I want to be able to run a procedure/macro whenever the record count
in the master table increases. I can tell when I add a record to this table
becasue I hit the save record button. However, I need to run the same
procedure/macro when the other user updates the table too without exiting the
data entry gui.

Does anyone have any idea how I can tell if the master table has been
updated by the other user?

Not sure if this is the proper place to post this but thanks in advance to
anyone who can point me i teh right direction!
 
We have 2 MS Access front end guis we use to enter data into an MS Access
master table, stored in a llinked MS Access database on a shared network
drive. I want to be able to run a procedure/macro whenever the record count
in the master table increases. I can tell when I add a record to this table
becasue I hit the save record button. However, I need to run the same
procedure/macro when the other user updates the table too without exiting the
data entry gui.

Does anyone have any idea how I can tell if the master table has been
updated by the other user?

Not sure if this is the proper place to post this but thanks in advance to
anyone who can point me i teh right direction!
Click to expand...

In other databases, a trigger would be used for this kind of thing. But since
Access isn't a real client/server database, it doesn't support triggers.
 
For what your trying to do, will the timer event in your version of the
front-end provide the timelyness you require to run your process. In other
words, does it really need to be in real time, or just every xx minutes?

SteveD
 
TOMAC said:
We have 2 MS Access front end guis we use to enter data into an MS Access
master table, stored in a llinked MS Access database on a shared network
drive. I want to be able to run a procedure/macro whenever the record count
in the master table increases. I can tell when I add a record to this table
becasue I hit the save record button. However, I need to run the same
procedure/macro when the other user updates the table too without exiting the
data entry gui.

Does anyone have any idea how I can tell if the master table has been
updated by the other user?

Not sure if this is the proper place to post this but thanks in advance to
anyone who can point me i teh right direction!
Click to expand...


Without triggers, you would have to query the table to see
to see if the number of records has changed. If there is a
point where you need this information, then do it there. If
you want to monitor this asynchronously from your normal
activities, use a form's Timer event.

There are several ways to check the number of records in a
table, but this is pretty straightforward:

Static RecCnt
Dim NewCnt As Long
Dim rs As Recordset

Set rs = Currentb>OpenRecordset("SELECT Count(*) " _
& "FROM thetable")
NewCnt = rsFields(0)
rs.Close : Set rs = Nothing

If RecCnt <> NewCnt Then
' record added or deleted
' do something
RecCnt = NewCnt
End If
 
I can use the timer event, however, I'm trying to avoid as much "contact"
with the linked tables as possible on account of slow network response times.
I figured that if I could detact a change in the master table, I could run a
query and build a local updated copy of the master table at that time. This
way I would have up to date data whenever a change is made.
 
Thanks Marshall. I'm not very adept at vba but I assume that the code you
provided would be applied to the "on current" event of a new text field on my
data entry form?
 
been thinking about this..... I'm not sure your purpose, thus it may change
the direction of the answer. For example, if there are two front ends and
you mentioned a save button, what if you attach your process to the button
(on each F-end) or to the after update on the form.

If the purpose is to copy the new records to another table or database, this
can be done with a query when you close the form or at some other determined
event. One way is to have/use a field to indicate if has/has not been copied
for the criteria of the query, then change the status of that field.
Another way is for a primary key, so once appended to thenew table, it will
not be appended again.

If you are updating fields of the new record, can you do this when the
record is added?

Maybe not the best idea, but ....
A question to those smarter that I -- can a new (third) database be created
on the server, where a form opens with a timer event that performs the
required function. This database would be opened at the beginning of the day
and left open. the timer event would perform the required steps at a pre-set
interval? What's the downside to this?

I may be getting carried away, but sometimes that happens.
 
Both f-ends "a" and "b" write to table "c" on the master database. When a
record is added to table "c", both f-ends see this record in a subform their
data entry screens which queries table "c". In order to avoid the network as
much as possible, I would like to create a local table "c" on each f-end and
use that to view the added records. This is where my problem is, as instead
of always linking to table "c" on the master, I'd like to be able to run
these make table queries only when the master table "c" is updated by my self
(which I can tell by saving a record) or by the other f-end.

BTW, thanks for all your help on this.
 
I can't tell where you would want to do that. If you need
the information whenever you navigate to a record regardless
of what you do (or don't do) on each record, then I guess
that might be appropriate. However, that's probably
querying far too often.

You said elsewhere that the goal is to minimize network
traffic. Generally, the most effective way to do that is to
make sure that your forms only load one record and that your
combo and list boxes only retieve the bare minimal number of
records.

Trying to synchronize a local copy of a table in the Current
event can generate a LOT of unnecessary network traffic.
 
Back
Top