Work tables

[Peter Kinsman]

I may have already tried to get an answer to this, but not worded the
question correctly.

Is there a preferred way of allowing multiple users to update an Access Bill
of Materials database.
I have tried adding a "UserName" field to the work table.
I have thought of copying the layout of the work table to a table whose name
includes the UserName.
I wondered whether to use a copy of the front end database whose name
includes the UserName.

Any suggestions please?

Many thanks

Peter Kinsman

 

[John W. Vinson]

I may have already tried to get an answer to this, but not worded the
question correctly.

Is there a preferred way of allowing multiple users to update an Access Bill
of Materials database.
I have tried adding a "UserName" field to the work table.
I have thought of copying the layout of the work table to a table whose name
includes the UserName.
I wondered whether to use a copy of the front end database whose name
includes the UserName.

Any suggestions please?

Many thanks

Peter Kinsman

I don't understand the problem. Access databases are multiuser out of the box;
there is no need to add a user name field to make them so. What benefit do you
expect to obtain from including the username? Just a log of who's making the
change? What obstacle are you trying to overcome?

 

[Peter Kinsman]

John

Maybe I am doing this the wrong way, but I create or update a bill of
materials by adding records to a temporary table, which is used by the query
which is the RecordSource of a subform which displays the structure.
Obviously I need to prevent another user from updating the same structure,
but essentially all users will be using the same work table.
Please correct me if I am wrong, but I have just realized that if I add a
filter to the query, to select just the records that apply to the user's
structure, then the whole thing will become multi-user. Why has it taken
two and a half years to work that out?

Many thanks

Peter

 

[John W. Vinson]

John

Maybe I am doing this the wrong way, but I create or update a bill of
materials by adding records to a temporary table, which is used by the query
which is the RecordSource of a subform which displays the structure.
Obviously I need to prevent another user from updating the same structure,
but essentially all users will be using the same work table.
Please correct me if I am wrong, but I have just realized that if I add a
filter to the query, to select just the records that apply to the user's
structure, then the whole thing will become multi-user. Why has it taken
two and a half years to work that out?

That's one way to do it - but *why use a temp table AT ALL!?*

What can you do with a temp table that you cannot do with a Select Query?

If you do need an individual temp table then I'd really suggest NOT putting
all the users' data in a single table. Instead, use a split application
(shared tables in a single backend and individual "frontend" databases on each
user's desktop), with the temp table in each individual user's frontend. No
contention and no interference.

 

[Peter Kinsman]

Sorry - missed the most important bit.
I have already split front end and permanent tables into separate databases,
but the client is running Terminal Server, so all users use the same
database.

Peter

 

[John W. Vinson]

I have already split front end and permanent tables into separate databases,
but the client is running Terminal Server, so all users use the same
database.

Sorry... but that is *the worst possible approach*.

You're getting the disadvantages of the split architecture (temp tables,
performance problems) *and* all the disadvantages of a shared database
(contention, bloat, corruption risk), and you're getting NO advantages.

My clients who use terminal server (Citrix, but the same applies to WTS) have
each user connecting to a private folder containing *their own copy* of the
frontend, which they open exclusively. Each folder has a .mde file; the master
..mdb file is kept secure and only administrators can use it to create new .mde
files.