Multiple Users Operating on the Same Table

[thefonz37]

So I've got this problem where I have a database where multiple users could
potentially be operating on tables at the same time - updating tables,
deleting records, editing them, the whole 9. Before, I could have solved
this problem by creating database replicas and synchronizing later, but since
sychronization has gone buh-bye in Access 2007, I'm not sure how to solve
this problem. Anyone have any advice?

 

[Jeff Boyce]

Perhaps I'm mis-interpreting your description.

It sounds like one of the potential activities is the users modifying the
table structures. You must have very smart users!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[thefonz37]

I have an autoexec macro set up to prompt the users to modify a recordset
based on their user name (they're approving and modifying productive hours
reported by employees).

The autoexec creates a table based on the current user name for requests to
approve (so supervisors only need to approve their own employees) and then
opens a form based on that table. The problem is that if someone opens the
database, that table is created, but if someone else opens up the database at
the same time, it recreates the table, which overwrites the table the first
person created and the first user's data is lost.

 

[John W. Vinson]

So I've got this problem where I have a database where multiple users could
potentially be operating on tables at the same time - updating tables,
deleting records, editing them, the whole 9. Before, I could have solved
this problem by creating database replicas and synchronizing later, but since
sychronization has gone buh-bye in Access 2007, I'm not sure how to solve
this problem. Anyone have any advice?

If the users are just editing data in the tables (rather than changing the
STRUCTURE of the tables) - and if the users are all on the same fast, stable
LAN - then they should be using a split application, with the tables in a
shared backend database, and each user having a "frontend" with forms,
reports, queries etc. linked to the backend. Replication is required if the
users are not on the same network; but it may be an overly complex solution to
a simple problem!

 

[Jeff Boyce]

You've described a "how", as in how you've decided to solve a business need.

I may be reading too much into your description ... it sounds like the
business need is to have a unique set of requests to approve.

You could accomplish that many ways, but one option would be to use a
stationary, permanent table (not alterable by users) to hold the data, and
include a [ForWhom] field to identify the supervisor. Then you'd just need
to have a query that adds (appends) records to that table, and a query that
removes (that supervisor's) records after processing.

Then again, I may have misinterpreted your description!

Good Luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[thefonz37]

Thanks Jeff and John, you've both given me some ideas on how to solve the
problem. I was hoping to not have to redesign the pieces I've already
created, but I'm thinking that will be inevitable now. I appreciate the
thoughts, though, and I'll have to decide which implementation best suits
what I need to do.

 

[David W. Fenton]

If the users are just editing data in the tables (rather than
changing the STRUCTURE of the tables) - and if the users are all
on the same fast, stable LAN - then they should be using a split
application, with the tables in a shared backend database, and
each user having a "frontend" with forms, reports, queries etc.
linked to the backend. Replication is required if the users are
not on the same network; but it may be an overly complex solution
to a simple problem!

I would hesitate to suggest Replication first for that. Instead, I'd
recommend Windows Terminal Server before Replication. Today I only
recommend replication where:

1. there is no possibility of a Terminal Server, OR

2. users need to edit data on laptops in the field, without an
Internet connection.

The 2nd scenario is ideal for the simplest form of replication when
the laptop users return to the office on a regular basis, and can
synch across a wired LAN. Indeed, it's very easy to implement in
that scenario, and quite reliable.

 

[Dale Fye]

The thing I seem to have read into your post that John touched on, but
didn't hit very hard, is that it looks like your multiple users are all
using the same application. It isn't clear, but it does not sound like you
have split your database into a frontend (one on each users machine) and a
backend (where all the data is stored). This is extremely easy to implement
(Access has actually provided the tools for doing it), and is relatively
easy to maintain (see Tony Toew's website for his front end updater).

I strongly recommend that you start out by making this change.

Dale