Multi User Issue (1 Viewer)

Tupacmoche · Dec 7, 2016

I have a multi user issue and can use some help. I don't believe that locking record is the answer but could be wrong. Here is the process, I run a macro that creates several temp tables that are used to create yet other temp tables until the final table that I need is made. This is where the problem takes place. Since, at least twenty (20) people could run this process at the same time the final table it is being over written when another user runs the process. How can this be prevented? I thought about concepts that are used in Web development where a user session variable is attached to give the table a unique name. So, for example given users Rob, Joe, Katie the table would be named tbl_rob, tbl_joe, tbl_katie. Since they have distinct names they can all exist at the same time without over writing one another. Any suggestions?:banghead:

Frothingslosh · Dec 7, 2016

Use either a local front end or a disposable 'side end' and have the temp tables created in THAT. Keep in mind that if you use the front end, you're going to have to figure out how to keep database bloat under control. But then again, you have the same issue with your current setup.

Of greater concern, however, is why are you having the final data go into a new table each time? That smacks of an incorrectly designed database, especially as your post suggests that the dataset's creator is part of the data being tracked. (One MAJOR rule is that table names should never include tracked data - Sales is good, February Sales and 2016 Sales are bad.)

Tupacmoche · Dec 7, 2016

This DB is running from a terminal server that the 20 user that I mentioned dial into. The 'Front end' and 'back end' are split apart on this server. I'm not sure what you mean by 'use a local front end' ? Does that mean that the files are created on each users box? What does 'side end' mean? The macro that I mentioned deletes all the temp files as part of the process so there is no issue with 'DB bloat' So, to summarize is your suggestion to create the temp tables on local users machine in order to overcome the problem of over writing the data?

pbaldy · Dec 7, 2016

Frothy appears to be offline, but I'll echo their thoughts. In a terminal services environment, you need to make sure each person has their own copy of the front end; they can't all run the same copy. I had a folder on the server called "AccessApps" and each person had a subfolder under that containing a copy of any front ends they used.

On the bloat issue, it doesn't matter that the tables are deleted. The file will bloat because of the creation/deletion of the tables. If the front end file is 10Mb at the beginning of the day, I'm willing to bet it's larger at the end of the day. Probably exponentially larger. This may be a case for setting compact on close.

Frothingslosh · Dec 7, 2016

Yeah, I'm at work, so I typically pop in here to check only at lunch or while a query is running.

Paul basically nailed it. Access does NOT handle concurrent users well. The back end can take it, but doesn't handle simultaneous code execution well at all. Front ends are basically everything but the tables, and they should be run locally (ie - on the user's machine or virtual facsimile thereof), while the back end is stored someplace on the network.

A 'side end' is simply a temporary database used to contain temporary data, and it is deleted once it is no longer needed. It's a way to avoid having your 20 meg front end balloon up to a 2 gig front end. As a rule, anything that gets deleted - table, form, record, you name it - actually stays in the database file until a compact and repair is completed. That can lead to database files growing surprisingly rapidly until they hit the 2 gig limit and break.

So yes, my suggestion was to have all the temporary files created on the user's local box. That way it won't matter that 20 different people just created a new 'Temp_Table_1', because each of them is saved on their C drive rather than in the main database file.

In your case, use Paul's suggestion - have each person have their own copy of the front end in a folder only they have access to, and do your table work there.

Tupacmoche · Dec 7, 2016

This sounds odd we have been running this Access DB application for years and a dozen orthers and the users simple RDP into it and use it with no issues except the one we are discussing. Anyway I still need a solution given my environment. Any ideas?

pbaldy · Dec 7, 2016

Some things in a terminal services environment with a common front end run fine, others not. One that doesn't is front end tables. I had twitchy little problems until I gave everybody their own copy.

Frothingslosh · Dec 7, 2016

Honestly, your options come down to using individual front ends (and that's the industry standard for a reason) or else finding a way to guarantee that the tables have unique names, and all the queries accessing those tables use the correct names.

As it is, you're already getting bloat, and if you aren't already running regular compact and repairs, your databases where you're putting these tables is probably bloated all to hell. I have a feeling that you would be shocked by how much smaller it would get if you can sneak in a compact and repair. (If you aren't doing that regularly, do a backup first just in case.)

Tupacmoche · Dec 7, 2016

An idea that just hit me is to concatenate to the file name the users unique id for example 'MytableName+' '+uniqueId. This will guarantee that each user will have there own file. What do you think? Is this possible?

pbaldy · Dec 7, 2016

Sure, if it's easier for you. I create a version control utility that keeps users updated, and I used a folder.

MarkK · Dec 7, 2016

query your queried queries

To me--if I could jump in--it's unusual that you should create so many tables. More common practice is to write a query and then use that query as if it were a table, eliminating the need to create an actual table. In this way you can query your queries, and query your queried queries, layering on the complexity until you get your result. This can reduce or eliminate the need to incrementally create tables at all.

gemma-the-husky · Dec 7, 2016

It's one thing technically saying a single instance of a database can be multi-user.

It's another if shared users are changing data in a local table,

User A puts data in the table
User B changes it.
User A now finds different data in the table.

The designer has to take steps to prevent this happening. The easiest and safest is not to share the front end.

The_Doc_Man · Dec 8, 2016

Sharing the front-end file (or sharing the non-split DB) runs into other pitfalls. Access databases are files. They have locks on them from the file system, so you have all sorts of load right there. Then, Access tracks its own internal locks on everything INCLUDING the forms you open. Therefore, a shared DB or FE file exposes itself to block-level locking that can become vexing.

Splitting the DB into an FE/BE and then setting aside an area for each user to have a private copy of the FE DRASTICALLY reduces the number of locks in contention for the FE. Oh, you still lock every form, every report, every macro... but if they are in private copies of the FE, you don't expose those locks to other parties. The only exposed locks are then on the BE file that has to be shared for this purpose.

Access is perfectly capable of handling a shared non-split database. It is the natural consequence of having to share exposed conflicting locks that makes the situation so ugly. The solution is to reduce the number of shared exposed conflicting locks, as described above.

Murtaza45 · Mar 13, 2017

I Think its not possible in it. or its bad idea.

The_Doc_Man · Mar 13, 2017

Murtaza: It IS possible - but you are right that it is a VERY bad idea.

HiTechCoach · Mar 14, 2017

Tupacmoche said:
Since, at least twenty (20) people could run this process at the same time the final table it is being over written when another user runs the process.

Classic symptoms of a shared front end or un-split database.

Tupacmoche said:
How can this be prevented?

Simple, stick with the proven basics ... don't ever share the front end.

Tupacmoche said:
I thought about concepts that are used in Web development where a user session variable is attached to give the table a unique name. So, for example given users Rob, Joe, Katie the table would be named tbl_rob, tbl_joe, tbl_katie. Since they have distinct names they can all exist at the same time without over writing one another.

The reason web developers have to do all that extra work is because the front end is shared. Web sites that use (local) cookies are doing the same thing as an (Access) front end that is not shared.

Temp tables:

I also regularly use local temp tables to build data for reporting. I put all my local temp tables in a "side-end" or scratch back end that is in the same folder as the user's local non-shared front end.

FWIW: I have been running Access apps over the internet since 2000 with Access 97 and Windows 2000 Terminal Servers and Citrix. Today I have 100's of user that work daily in a RDP session.

It does not matter if a Terminal Server/Citrix or a Windows desktop, I use the same NON-shared front end method.

Bottom line:

Shared front end =

and lot of extra work and :banghead: as you have proven

Every user with their own copy of the front end =

Multi User Issue (1 Viewer)

Tupacmoche

Registered User.

Frothingslosh

Premier Pale Stale Ale

Tupacmoche

Registered User.

pbaldy

Wino Moderator

Frothingslosh

Premier Pale Stale Ale

Tupacmoche

Registered User.

pbaldy

Wino Moderator

Frothingslosh

Premier Pale Stale Ale

Tupacmoche

Registered User.

pbaldy

Wino Moderator

MarkK

bit cruncher

gemma-the-husky

Super Moderator

The_Doc_Man

Immoderate Moderator

Murtaza45

New member

The_Doc_Man

Immoderate Moderator

HiTechCoach

Well-known member

Similar threads

Users who are viewing this thread