Mulitple Users with Only One Front End (1 Viewer)

[Kregg]

Currently the system where I work is set up so that everyone has their own front end on the local network. I would like to change this because updating all of them takes a lot of time for me and skill level for users is too low for me to trust them with updating it on there own.

My question is this:

If I were to create a shortcut on everyone's desktop that linked to one front end would this be wise? I can't seem to get a straight answer from anywhere on the internet. I know most places say that it is better to have the actual front end on the staffs computer but that would make my job of keeping the database updated even worse because staff have multiple computers and this isn't my full time job.

If this is okay practice how many users would this be able to sustain? Currently we only have 6 users on the database with there own front end and it works great. I am planning on adding around 10 more users in the future but if needed they could have there own database as they are separate programs.

Any thoughts?

 

[Jere]

I'm not super-experienced, but what I do takes some initial set-up time, but makes it a breeze afterwards.

What I do is I create a shared folder on each computer for the FE(Front End) file to be located, and I create a shortcut on the desktop to the FE for each individual pc. Once this is done, you shouldn't have to interact with each pc again.

Then what I do is I created a .Bat file that's located on the server hosting the BE(back end) that when clicked, will copy/paste the updated FE I created located on the server to each pc's shared folder. Makes it easy when working with ~10 computers.

Edit: forgot to mention. You can only have one FE/user.

The copy command I use in the .bat file is

Rem copy from server to users
xcopy "C:\Users\XXXXX\Desktop\FE.accde" "\\Lenovo-pc\XXXXX\FE.accde" /y

Pause

the /y auto-confirms the copy/paste. The first time you use this command, it will ask "is this a file or destination folder" so you'll want to say it's a file

 

[Pat Hartman]

If I were to create a shortcut on everyone's desktop that linked to one front end would this be wise?

In a word - NO!

If distributing the FE is a problem for you, it is time to automate the process. There are a couple of tools out there that you can get your company to pay for. Give them an ROI. It takes me x hours at y dollars per hour and so this software will pay for itself in z months.

You could also write your own or take a free version and modify it. There are a couple of techniques you'll see.
1. A batch file on the server that the user opens via a shortcut. This always downloads a fresh copy of the FE. This method is more appropriate for apps that the user opens infrequently or opens once and then leaves open for the day.
2. A VB Script file that opens the server-side BE and compares the version table to that in the FE. If the versions are the same, the script opens the local FE. If they are different, the script copies down a new FE and then opens that.
3. Similar to #2 but done within the FE itself. This technique requires the aid of another database that is used to swap the files since the FE has to close itself so it can be overwritten by the new version.

#1 is the simplest and you could do it in a half hour. It also requires no database change. #2 is more complex but more robust and will take longer. VB Script is almost identical to VBA except that you don't dim variables. Everything is undefined. To use this method, you need to add a version table to the FE and another to the BE so the script will have something to compare. Whenever you want to force a new version, change the version number in the BE. Don't forget to put the matching version number in the FE stored on the server in the download folder or you'll put yourself into a tight loop. #3 is the most complex and I wouldn't advise trying to write it yourself although you might find a free version you can download.

I typed too slowly Jere beat me to the post. He is using technique #1

 

[Kregg]

Thanks for the replies! I am new to this and trying to figure out the best way to maintain the database along with my actual responsibilities.

 

[gemma-the-husky]

Access is multi user out of the box

if multiple users open an excel file, all but the first get a read only message. in access, you do not get this, and the access dbs can be used by several users.

so it depends what happens if multiple users all use the same database copy - what happens?

The theory is that multlpe users should be able to work together in a single database - It's just that experience has shown it appears to be safer not to.

eg - if you use temporary tables to manipulate data, then you will probably find that users will interfere with each other. One user may overwrite another users's data. So you need to be careful about exactly how you do stuff if databases are going to be shared.

The other issue is whether the single database includes data as well, or whether the data is stored in a linked database. Storing in a linked database makes it easier to work on and update the main code database.

so although you may not get errors in a shared database, the safest recommendation to avoid corruption and dataloss is as follows

- split the data to a front end and back end
- back up the backend regularly
- give each user an individual copy of the front end
- keep safe backups of the working code for your front end
- use a wired lan, rather than wireless

good luck.

 

[Pat Hartman]

I would guess that 99.9% of the issues caused by shared FE's or shared monolithic databases is caused by abrupt disconnects. Network disruptions are the most common followed by a freeze of the user's PC and followed by the user pulling the plug by simply powering off. #3 can be fixed by educating users but #1 and #2 are out of our control. So if you have an exceptionally stable wired network (wireless are never stable) and your users don't open a bazillion apps at once, you can skate for years - fat, dump, and happy as they say until the sky falls on you.

 

[boblarson]

Just as an FYI - I have a free tool on my website which will enable auto updating on your frontends.

It is here:
http://www.btabdevelopment.com/ts/freetools

Be sure to read the documentation carefully if you choose to use it. And you can email me (email is on my Contact Me on my website) for free support with using that tool. Or you can post here but I'm more likely going to see it if you contact me directly for that. But I don't answer general Access questions via email as nobody else gets the benefits.

 

[noboffinme]

Just read this & have a question for Gemma the Husky.

When you say "give each user an individual copy of the front end", do you mean go to the Databse file FE which is filed on the Local Area Network (Office Network) & create a Shortcut onto their Desktop?

I hope so because that's what I've been doing for ages.

My steps are, please comment as you like;

• Save the DB Access file on the LAN
• Split the DB into the FE & BE
• Go to each Users PC & create a Shortcut for the LAN FE onto their Desktop

Thanks for any feedback.

 

[boblarson]

Just read this & have a question for Gemma the Husky.

When you say "give each user an individual copy of the front end", do you mean go to the Databse file FE which is filed on the Local Area Network (Office Network) & create a Shortcut onto their Desktop?

I hope so because that's what I've been doing for ages.

My steps are, please comment as you like;

• Save the DB Access file on the LAN
• Split the DB into the FE & BE
• Go to each Users PC & create a Shortcut for the LAN FE onto their Desktop

Thanks for any feedback.

No, that is NOT what I mean and what you are doing is not optimal. They each should not have a shortcut to the file, they need a COPY OF THE FILE.

Read this because Albert D. Kallal explains why this is important (it isn't just something we say but there is a solid set of reasons behind it).
www.kallal.ca/Articles/split/index.htm

 

[noboffinme]

Ok, thanks.

I'll have a read, is this code connecting each Users FE through the LAN to the Network FE?

Is your software doing the same as linking the tables from the FE on the Users PC to the BE on the LAN?

If I'm not correct, could you explain the connections that the software is creating?

Thanks for the info, I'll be reading up to do this in future.

 

[gemma-the-husky]

noboffinme - re your last

no the idea is that each user should have a separate copy of the front end, so that an individual front end is never shared. and each front end is linked to the same backend.

 

[noboffinme]

Thanks for that,

So the steps to do this would be;

• Save the full (FE & BE) database file to the Network Drive (LAN)
• Also Save the full (FE & BE) database file to the Users PC
• Split the DB on the Users PC into FE & BE
• Use boblarsons software to link the Users FE & the Network BE

Is that what you would recommend?

Otherwise, could you advise the best process?

Thanks

 

[CJ_London]

@NoBoffinme: Not quite

• Save the full (FE & BE) database file to the Network Drive (LAN)
• Split the DB on the Network Drive into FE & BE
• Copy the FE database file to the Users PC

Bobs tool is to do with updating FE's so can be used when you make changes to the FE (which you do on the network drive, or more likely on your pc and then copy the updated FE to replace the network copy) and need to update each users copy of the FE

 

[noboffinme]

Thanks CJ London for the clear & complete response.

So when I save a copy of the FE onto the Users PC, I'll need to link the objects to the BE on the Network drive in order for the updates to happen for each user concurrently.

Can you confirm pls?

Thanks

 

[CJ_London]

So when I save a copy of the FE onto the Users PC, I'll need to link the objects to the BE on the Network drive in order for the updates to happen for each user concurrently.

If by updates you mean changes to the data in the tables, then yes.

Your FE will consist of modules, reports, forms, queries and linked tables

You BE will consist of tables only

When you create your FE, you will make all the links to the BE and if the user copies the FE to their computer, those links will remain - only requirement is that each user has the appropriate read/write permissions to the directory where the BE resides

 

[noboffinme]

Thanks,

I followed those instructions & I did find that I needed to relink the FE to the BE when I copied & pasted the FE file from the Network drive to another PC.

A test confirmed that changes on one PC updated the BE & that change was then available on the second PC.

Appreciate your guidance.

 

[Pat Hartman]

You should not have to relink the BE for each user. If your users do not have standardized drive mappings, then you need to use UNC notation when you link originally. That way the link path will be identical for all users and so once a FE is linked, you can just copy it for each user and no change needs to be made. If you are having to relink the FE for each user, that will also be required when you distribute new FE's with updates. SOOOOOOOOOOO fix the link path NOW to use UNC notation.

\\servername\path\yourapp.accdb
rather than M:/Path/yourapp.accdb for user A and G:/Path/yourapp.accdb for user B

 

[noboffinme]

Thanks Pat,

Good point though I did use the Link Table Manager wizard to browse to the BE.

It also may have not auto linked as I had just updated to Windows 7 & it had to install extra software to deal with the issue.

Is there a way I check that an UNC path has been used OR does the Link table manager automatiaclly use this??

 

[Jimmy Cook]

Awesome article. You have some great insights. I love to read your blog while I’m at work to help pass time.

 

[gemma-the-husky]

Thanks Pat,

Good point though I did use the Link Table Manager wizard to browse to the BE.

It also may have not auto linked as I had just updated to Windows 7 & it had to install extra software to deal with the issue.

Is there a way I check that an UNC path has been used OR does the Link table manager automatiaclly use this??

the link table manager is fine, but most of us hsave a way of doing this in code, to avoid users having to understand stuffl ike this. also, different tables may actual need linking to different back ends.

I tihnk both UNC path, or named drive both work.


----

one the backend is linked pretty well everything works exactly the same - there are just a couple of things though -
-you cannot use indexed search on a linked table - you have to use find
-you can't change the backend table definitions directly from the front end

- you do get many benefits though. you can work on the programme. and change what the programme does independently of the data. It becomes easy to work against a test copy of the data. You can back up the data easilly. If necessary, you can change the backend to a different version of access, or even a different type of backend wg SQLServer or MySQL. All you need is a slightly different connection process. Most things wrok the same with a non-Access backensd although there are a few differences.