Multi user database

[TOMAC]

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?

 

[TOMAC]

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]

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]

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.

 

[Nyx37]

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

 

[Tony Toews [MVP]]

We have network problems

Any idea what kind of 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.

Is the network quite fast when only one user is in? But real slow for
the second user?

Are the workstations on Windows Vista?

Are you aware of the Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[TOMAC]

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.

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

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.

 

[TOMAC]

Any idea what kind of network problems?

TOMAC : Well I'm no expert but we went from a local server to a WAAS server
(data caching) located 400 miles away. I suspect that this had a lot to do
with our response time issues.

Is the network quite fast when only one user is in? But real slow for
the second user?

TOMAC : Yes - When the first user adds or edits a record, the second user's
app just freezes until the first user has completed their work. Even though
there are no record locks in place.

Are the workstations on Windows Vista?

TOMAC - NO

Are you aware of the Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

TOMAC - I just looked at this site and I will implement any of these
suggestions that speed my db up. Thanks Tony!

 

[Tony Toews [MVP]]

TOMAC : Well I'm no expert but we went from a local server to a WAAS server
(data caching) located 400 miles away. I suspect that this had a lot to do
with our response time issues.

That would do it. Any kind of direct WAN connection is *NOT*
recommended. Thus I'd suggest using Terminal Server or converting
your app to SQL Server. TS would be a lot easier to implement.

TOMAC : Yes - When the first user adds or edits a record, the second user's
app just freezes until the first user has completed their work. Even though
there are no record locks in place.

TOMAC - NO
TOMAC - I just looked at this site and I will implement any of these
suggestions that speed my db up. Thanks Tony!

Hopefully some of those suggestions will help with the second user in
being slow.

However what also concerns me is the data caching on the WAAS server.
This kind of stuff may work well on Word or Excel files which are
updated all at once. But Access/Jet MDBs have many, many updates to
it's pages throughout the MDB file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/