Work tables "Peter Kinsman" <peter[ at ]seguecomputing.co.uk> 11/8/2008 11:52:03 AM 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

Re: Work tables John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 11/8/2008 8:47:51 PM On Sat, 8 Nov 2008 11:52:03 -0000, "Peter Kinsman" <peter[ at ]seguecomputing.co.uk> wrote: [Quoted Text] >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? -- John W. Vinson [MVP]

Re: Work tables "Peter Kinsman" <peter[ at ]seguecomputing.co.uk> 11/9/2008 7:16:21 AM 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" <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> wrote in message news:6lubh49ngb3ovttsuv8d6rebg18aphppmc[ at ]4ax.com... [Quoted Text] > On Sat, 8 Nov 2008 11:52:03 -0000, "Peter Kinsman" > <peter[ at ]seguecomputing.co.uk> wrote: > >>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? > > -- > > John W. Vinson [MVP]

Re: Work tables John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 11/10/2008 12:35:11 AM On Sun, 9 Nov 2008 07:16:21 -0000, "Peter Kinsman" <peter[ at ]seguecomputing.co.uk> wrote: [Quoted Text] >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. -- John W. Vinson [MVP]

Re: Work tables "Peter Kinsman" <peter[ at ]seguecomputing.co.uk> 11/10/2008 7:30:23 AM 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" <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> wrote in message news:5a0fh457ru6b8ennricrigjrlf0dhcvdom[ at ]4ax.com... [Quoted Text] > On Sun, 9 Nov 2008 07:16:21 -0000, "Peter Kinsman" > <peter[ at ]seguecomputing.co.uk> wrote: > >>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. > -- > > John W. Vinson [MVP]

Re: Work tables John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 11/10/2008 4:33:18 PM On Mon, 10 Nov 2008 07:30:23 -0000, "Peter Kinsman" <peter[ at ]seguecomputing.co.uk> wrote: [Quoted Text] >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. -- John W. Vinson [MVP]