Multi User - Splitting db or not

[teachme]

Hi,

I am in the final stages of going live with the db that i have been working on and I need some advise on what is the best way to share the db in a multi user environment. There will be no more than 10 users using the db at any give point in time.

Option 1: Use vb code to prevent table etc from accidentally being deleted etc and place that 1 file on the network drive and multiple users can use the db at the same time. Our network drives are backed up daily so if something happens, we should be able to recover.

Option 2: To split the db. Keep the back end on a network folder and give the front end to each user on a usb drive etc and still use vb code to protect the data etc.

What is the best way to do this?

Any help is greatly appreciated.

 

[Minty]

Option 2, but don't use it on a USB stick. Put the FE on their PC, and use one of the auto update methods (search for it here) to keep the FE up to date if you make changes.

Option 1 will give you no end of problems.

 

[teachme]

Option 2, but don't use it on a USB stick. Put the FE on their PC, and use one of the auto update methods (search for it here) to keep the FE up to date if you make changes.

Option 1 will give you no end of problems.

Yeah sorry, I meant to say that put it on a USB drive to deliver the FE db to their PCs.

I wasn't aware of any auto update method. I thought if there is any changes, I would have to deliver the new version of the db via USB or put it on the network drive somewhere so they can download it to their machine.

I will search for it here.

 

[Minty]

Put it on the network drive somewhere so they can download it to their machine.

I will search for it here.

Basically the auto update does this - checks a version number that you maintain and then copies over the new front end.

 

[teachme]

Basically the auto update does this - checks a version number that you maintain and then copies over the new front end.

Thx another question. If I split the database, does that reduce the efficiency - slow response time? Perhaps because the data will need to move on the network?

thx

 

[Minty]

Inevitably there will be a difference between a totally local single user database and a local FE database pulling data over the network.

This is where you may need to box a bit cleverer - e.g. don't load a form with every record from a table if you don't need to. If you have lots of subforms on forms consider moving the subforms you don't need to see all the time to tabs on the form and only load them when the tab is selected.

You may not see any issues. But ensure your network is in good shape and is as fast as possible.

 

[teachme]

Inevitably there will be a difference between a totally local single user database and a local FE database pulling data over the network.

This is where you may need to box a bit cleverer - e.g. don't load a form with every record from a table if you don't need to. If you have lots of subforms on forms consider moving the subforms you don't need to see all the time to tabs on the form and only load them when the tab is selected.

You may not see any issues. But ensure your network is in good shape and is as fast as possible.

OK I read directions here to split the db and tested with another user and things seem to be a bit slower- which is what was expected but its not that bad. Hopefully it is not going to get slower and slower as more and more users are using it. I mean there should be a limit on how much slower it gets lol

https://support.office.com/en-us/ar...base-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc#bm1

Ok here is the a question.

It says that we should convert the FE db to ACCDE file before distribution to save forms etc. But if we do that then the user is unable to link the table. ....because linking to table on the BE db is disabled when you convert a file to accde format.

Should this be the other way around? Am I doing something wrong?

Thank you

 

[teachme]

Update:

I split the DB and used it through VPN and it was really slow, I mean it took up to 15 sec to open a form. But perhaps, its VPN so I am not worried about it too much.

There is a couple of good articles below:

http://www.granite.ab.ca/access/performancefaq.htm

http://www.techrepublic.com/blog/10-things/10-easy-ways-to-speed-up-an-access-database/

Initially my back end was several folders down, I brought it up a little which improve a little but not by much.

Out of these many tips, I will go with the following:

- put the backend on the root directory
- Track name AutoCorrect should be off
- check - compact on close

Tomorrow I will test it over the network with a local team before I schedule a testing session with the end users to see how it goes.

I will keep you posted.

If you have any tips, please be sure to share.

Thank you

 

[Galaxiom]

Access does not like slow connections. It is likely to corrupt the back end if you put the front end on the VPN client and have the backend on the network server.

Better to use Remote Desktop over the VPN and have the FE running on the RD server.

 

[Minty]

Converting your distrubuted FE to an Accde will prevent users changing your design.
As Galaxiom stated do not try and use Access via a VPN, remote desktop is the way to go.

Also End users only need a runtime installed not a full version of access, this will also prevent design changes to your accdb, if that is what you are distributing.

I don't think linked tables are an issue with and accde?

 

[teachme]

Access does not like slow connections. It is likely to corrupt the back end if you put the front end on the VPN client and have the backend on the network server.

Better to use Remote Desktop over the VPN and have the FE running on the RD server.

oh that may be a problem if any of my users try to use it via VPN. I had no idea of this limitation.

 

[teachme]

Converting your distrubuted FE to an Accde will prevent users changing your design.
As Galaxiom stated do not try and use Access via a VPN, remote desktop is the way to go.

Also End users only need a runtime installed not a full version of access, this will also prevent design changes to your accdb, if that is what you are distributing.

I don't think linked tables are an issue with and accde?

Ok I get what you are saying but can you please clarify?

Is Runtime different than ACCDE file? Do I need to go with either runtime or accde or runtime is an additonal step?

The MS link below suggesst that I have to download runtime for access 2013. i work for a big corporation and downloading is not allowed. I have to establish if this is something i really need before I take the route of getting permission to download runtime for 2013

https://www.microsoft.com/en-ca/download/details.aspx?id=39358

Just just fyi: my mdb file size is 67000kb but when i convert to accde file, the size reduced to 4000kb

Thank you

 

[Galaxiom]

Runtime is a free cut down version of Access that can be installed to run an an Access database without having the full version of Access that also supports designing forms and reports.

The accde is a "compiled" version of your Access database file where all the VBA has been converted to a more machine friendly version and is no longer able to be easily changed by a user. The conversion is done in Access.

It is essential that the runtime version installed on the cleints is at least the same or later than the version of Access used to compile the accde. Make sure you get it from Microsoft.

 

[Galaxiom]

Just just fyi: my mdb file size is 67000kb but when i convert to accde file, the size reduced to 4000kb

This is because everything is stripped down to the essentials. The VBA no longer exists as text nor the forms as design objects.

Just be sure to never lose the accdb file because the accde cannot be edited.

 

[gemma-the-husky]

its more likely to be the absence of a persistent connection that contributes to poor performance, rather than the network.

When you split a database you need to ensure you have a persistent connection, or windows ends up taking a relatively considerable amount of time granting you access to the back end, when in a multi-user environment.

 

[teachme]

Runtime is a free cut down version of Access that can be installed to run an an Access database without having the full version of Access that also supports designing forms and reports.

The accde is a "compiled" version of your Access database file where all the VBA has been converted to a more machine friendly version and is no longer able to be easily changed by a user. The conversion is done in Access.

It is essential that the runtime version installed on the cleints is at least the same or later than the version of Access used to compile the accde. Make sure you get it from Microsoft.

Ok I think I dont HAVE to install runtime because all my end users have MS ACCESS installed on their machine. based on what I found on MS site below, you need runtime if the end users dont have MS ACCESS application installed.

"Microsoft Access 2013 provides a rich platform for developing database management solutions with easy-to-use customization tools. If no end-user customization is required (including report modifications), you can choose to distribute those Access 2013 solutions so that they run without requiring a full installation of Access 2013. To do so, you must package and distribute your application with the Access 2013 Runtime."

 

[teachme]

its more likely to be the absence of a persistent connection that contributes to poor performance, rather than the network.

When you split a database you need to ensure you have a persistent connection, or windows ends up taking a relatively considerable amount of time granting you access to the back end, when in a multi-user environment.

In the next couple of hours I will gather a few colleagues and test this split db over the network for performance.

I will follow the following steps:

1. Keep a copy of the ACCDB file and split the db
2. Put the BE db file (ACCDE) version on the network drive
3. Distribute FE db file to the users
4 Have them run the db and monitor the performance

5. Report back here.

Thanks

 

[teachme]

I tried the split db over the network with 3 users and it was not too bad. But when I tried to have the 4th user open the FE db - everything was fine but when I asked her to map the tables queries, forms through link manger, she got access denied message to the BE file. I cant figure out why. 3 users were able to map/link but 4th user (i tried it with 2 of them) start getting access denied error.

 

[Minty]

Did you not map the links before distributing the FE ? Access would remember the links in the FE once set.

Access denied is a user / network permissions problem. Can the user see the BE file if the use file explorer?

Make sure you are not using a mapped drive letter - use a URL link to your network share.

 

[teachme]

Did you not map the links before distributing the FE ? Access would remember the links in the FE once set.

Access denied is a user / network permissions problem. Can the user see the BE file if the use file explorer?

Make sure you are not using a mapped drive letter - use a URL link to your network share.

Yes, I mapped the links but it didnt work so I thought I had to refresh the links (map it out again) for it to work.

Yes, the user can see the be file but when they click on the file and open they get a message similar to this:

https://social.msdn.microsoft.com/F...-cannot-open-the-backend-file?forum=accessdev

I just sent another FE file to the user and asked to open the forms with the links to BE already mapped out and she got this error - see attached.


I dont think the message is right - because I or anyone does not have the BE file exclusively open. But when I go to the folder where back end file is located i see that the record locking file is there which means that it is in fact open. OR does the locking file show up once users are linked to the back end file?