RE: Multi user database

This looks like it may work. I'll give it a try but it may take a while to
decipher all of this information. Thanks for your time and attention to this
problem.

"Nyx37" wrote:

What about an alternative? Setting up the FE's to work in a disconnected
manner and push updates to the backend. If I understand your posts there is
just one table to deal with. in the backend table add a field called
"LockedOn" set it as a Date type. This will help prevent update issues. On
the frontends set up temporary tables that have the same data structure as
the backend except the LockOn field.
On your main form set up a listbox that is populated with the records for
editing and a refesh button that updates it. In the listbox have a field for
"Is Locked" as boolean (Yes/No) and set that field up something like this:
IIf([LockedOn]>DateAdd("h",-8,Now()),True,False). From this form have five
buttons: Refresh, Add, Edit, Update, and Close. The edit button gets the the
unique identifier from the listbox and checks the LockedOn field to see if
it's being edited. Then pulls the date into the temporary table and runs an
update query to set the LockedOn field in the backend. Then it opens a
working form for editing/ Adding linked to the temp table. When the working
form closes you set it to push the record back up to the backend as an update
or insert (clearing the LockedOn field) and clear the temp table. this way
the network traffic is minimal and the data integity is mantained.
-Lionel

"TOMAC" wrote:

No not really. I really want it the other way around. I need to append new
records from tgable c to table a or b. The problem is that I don't want to
keep running this append query on a timer, I really would like to know when
the other user updates table c then run the append query.

"SteveM" wrote:

Is there a field in your local tables that could uniquely identify them from
those that exist in table C and that once appended would match those in table
C?

If so, you could just run an Append query that only appends records that do
not exist in table C already.

Steve

"TOMAC" wrote:

I agree. Ideally I'd love to have the users share the master table (which we
did originally) but our connectivity is not allowing this in a timely manner.
Right now I can determine when the master table is updated, I just can't use
the afterupdate function to run my macro that updates my local copy of the
master table. How would I compare the new count to the previous count?

"SteveM" wrote:

I still think you are going about it the wrong way. If users are connected to
the database they should 'share' the data not update to/from local copies.

If you really want to continue the way you are going; you could use a form's
OnTimer event to trigger a function that returns the count of records in the
source table DCount() or the last record ID DLast() and compare it to the
last value found (stored in a public variable)...this would not be my best
advice though.

Steve

"TOMAC" wrote:

Both front ends have a subform on their data entry tool that identifies "Open
calls". I had the master table linked to both front ends as you mentioned but
these front ends also have a subform that basically shows all calls that were
entered that don't have close date/times. This open call area is sourced from
queries that used the linked table. The problem is that when user A goes to
add a record, user B's front end freezes up.

So I am trying to localize each front end by using a copy of the master
table as their data source and writing to the master copy when they add a
record. The problem is that I can't tell when the other user adds a record so
I can refresh my local table.

"SteveM" wrote:

Why do you have tables A & B?
In the scenario you have described, you don't need tables A & B at all. At
least not as local tables....unless your users work offline (disconnected
from network) at any time?

What you should do is, import table C into your front end as a 'linked'
table. Then whoever has a copy of this front-end (if connected to network and
to back-end database) will be working with that table directly - no need for
updates etc...

Steve

"TOMAC" wrote:

I've spoken to a few people on this and no one seems to know how to do this.
I'm sure someone in this forum will be able to solve this puzzle.

We have network problems and we have 2 users that need to enter data into an
access database through table linking. Response times are very slow and we
need to come up with a way to localize the data entry but maintain a central
data file. Here's the scenario.

I have 2 users that enter the same type of data using their own access front
end tools. Machine A saves data to table A in their database and machine B
saves data to table B in their database. Both are linked to a table in the
master database (table C).When user A clicks to save their data, the data
gets saved in table A and an append query adds a copy of that record to the
master table C. When user B clicks to save their data, the data gets saved in
table B and an append query adds a copy of that record to the master table C.

User A needs to know when user B has added a record to the linked table C
and vice versa, so that they can run a query to update their local tables A
and B with this new record. I have added a field on the data entry screens
that keeps a running track of the maximum id number from table C so that I
know when a new record has been added. However, I need to run the query to
update the local tables A and B when this number changes.

Does anyone have any suggestions?

.