Question Using Dropbox to host a front end / back end database

[humph]

Has anyone any experience using Dropbox to host a split database. Ulimately I would like to create a run version for users with the back end held in a drop box directory.

As an unsplit database everything is working fine on dropbox records are saved in a timely manner and great.

Once I split to FE /BE only some records are saved on the BE and some are dropped. Was getting conflicted copies of Back end but manged to stop this happening but the records were noit on either copy.

Gave each user their own FE (on a drop box directory) linking to a BE in a sub directory on the same dropbox.

The key issue is that not all users have access and I don't want them to get into code so want to aim towards a run version.

Any help greatly appreciated.

Humph :banghead::banghead::banghead::banghead:

 

[Pat Hartman]

I've never used a Dropbox to share a BE but I will address some concept issues.
1. To create a less open version of a database, save it as an .accde (or .mde if you are using Jet). This will prevent users from getting into design view for forms/reports/modules but will do nothing to protect tables/queries/macros. There is no executable version of your Access app if that is what you were thinking. Changing the extension of an .accde to .accdr tells Access to pretend to be the Access runtime engine and prevent all access to design elements. Only allow user to view forms and reports and make data changes. But, this is a trick and a savvy user may circumvent it by renaming the app back to .accde.
2. Users should NOT share the same physical copy of the FE. So, the Dropbox should only be used as a means to distribute the FE. Users should be instructed to copy the FE to their C: drive and run it from there. Leaving the FE in the Dropbox folder adds to network overhead and you will get better performance by having the FE local to the user's PC.
3. Users who don't have Access can install the runtime engine. When using the runtime engine or a database with an .accdr extension Access will prevent the user from getting to anything but the forms and reports you created for him. You will need to have extensive error trapping because untrapped errors will simply close Access. You may also need to create custom menus/ribbons to provide features that are no longer exposed because under control of the runtime engine, all default ribbons, toolbars, menus are hidden.

 

[humph]

Hi Pat - thanks for your comprehensive response.
You opened my eyes with point 1 - I thought in my naivity that if I used access to split the database that the code forms etc was protected and that only the datatables could be accessed.

Point 2 I was aware of and what I was trying to do albeit from the dropbox was for each user to have a copy FE on dropbox with BE also on dropbox.

The focus on dropbox or some other cloud is because 2 users will be mobile using laptops and I thought this would be a solution.

My next approach will be to load the database on the individual pcs with the BE on dropbox and see how that goes as regards saving the data. The key issue seems to be triggering a save record on the BE.

Again in my naivity I thought the run time had to be loaded as part of the FE - you seem to suggest that users can load this as a standalone - am I understanding you correctly? I have fairly comprehensive error trapping but having said that probably not got them all.

Thanks for your thoughts on this.

 

[RainLover]

The only suggestion I have is for each user to have a Front End in their c:\Drive in a separate folder.

Link the Front End to a Back End on a Dedicated Machine's Dropbox Folder. This would require that Folder to be shared.

Turn off the Dropbox Notifications as you could receive so many that it would become very annoying. You would get a Notification on every Edit or Addition.

In essence this is the same as a normal FE/BE setup except you are using the One Machine's Dropbox for the Back End.

 

[humph]

Thanks for this - as above going to try the individual loading on the various C drives with the BE on the dropbox

The database can find the back ends OK and is linked ok just for some reason does not save all the records.

You thoughtts are as per mine the location should not technically make any difference provided they can link ok.

 

[spikepl]

WHat is happening here? There is only ONE WAY in which you can safely share a BE on Dropbox, and that is if each user has own timeslot for access, so that only one user accesses the db at one time.

Dropbox updates the centrally stored file with a copy of the most recent version made by whichever user that made changes last. Users do NOT access the same file simultaneously - they access copies. The last updated copy "wins".

 

[humph]

That would certainly explain why records are lost. So my thought that there should be no difference between a FE/ back end link on the server and a drop box is incorrect.

Back to drawing board to think of some storage approach that will allow me to get remote access.

Many thanks

 

[boblarson]

Back to drawing board to think of some storage approach that will allow me to get remote access.

Can you describe your current network setup and how users would be needing to connect and all? There may be options but not knowing anything about how your current system is and how users may need to use it kind of limits what we can suggest.

 

[humph]

Yes thanks in advance for help - really quite a simple application

System will have 6 users 2 office based other mobile with internet connection.

Front end
Was planning to provide application on a runtime version - one copy for each user. Had originally thought of hosting these on Dropbox but could be on individual pc's

Really want to protect code hence the use of runtime version however given security comment from Pat above this may not be as secure as I thought.

Back end
As you will have seen above was originally planning to have BE on drop box so that the off site users could access using internet connection. However had major issue with records disappearing and given comments above re how dropbox works now understand why they were disappearing.

Everything is working perfectly until we use Dropbox for BE.

Another option might be to use something like a Seagate product Free agent Go flex Home network storage - haven't researched this too much yet.
Open to any suggestion - thanks again for any help

 

[boblarson]

If you are using Access 2010, you could use SharePoint with Access Services installed. There are some providers who provide that as a backend if you don't have SharePoint 2010 with Access services installed in your workplace. It does cost but if you have this you can create webforms which can work on mobile devices using a browser.

Here's one hosting site:
http://www.accesshosting.com/index.asp

 

[RainLover]

As I said before. One Machine in the office has to be the dedicated keeper of the back end and it can be in the Dropbox folder. The Folder must be set to Shared.

Other users may have dropbox with a copy of the back end but do not use that copy. Use the copy on the dedicated machine.

For remote users they must also be able to link to the dedicated machine. Not the dropbox folder on their machine. Remote users will only work if everyone else is logged out. The can then use their own Dropbox Folder provided they are connected to the internet.

I have TESTED this and it works the way I described.

 

[Pat Hartman]

Look into Terminal services. This will allow your remote users to open a desktop that actually exists on a computer at the office. The application will run on that PC which would be connected to your network so you could access the shared BE. The only thing that travels over the net is pictures of what is happening on the desktop so performance would be similar to what the local users experience.

Terminal services is included in "pro" versions of the various Windows operating systems but must be separately purchased for each seat for other versions. I think a seat license is around $110. Citrix is a more well known option but it requires a Citrix server which sounds like it might be too pricey for this application. A third option is something like GoToMyPC. I haven't used it but it comes with a monthly fee and will require a separate dedicated PC in the office for each of the remote users to connect to.

 

[RainLover]

Pat
Just for my own information I would like to ask a little about Terminal Services. I have used it many years ago but it fail because of connection speed. With todays internet speed it may be worth revisiting.

Question. If we were to set up TS for remote users who accessed the same machine in the Office would we then need to create a unique folder for each user's front end. Otherwise would not the remote users be sharing the same front end.

 

[Pat Hartman]

When I have used terminal services it has always been to connect to my own PC at the client site. I'm not sure multiple people can connect at the same time. I have always had technical people to set this up for me and have never done it myself so my knowledge is supericial at best.

 

[gemma-the-husky]

rain - if you do not give each user a login to a separate folder - they will end up all using the same physical netwrked copy of the dbs - which you are general trying to avoid.

so each user needs a profile that directs the login to their own dedicated path.

 

[RainLover]

rain - if you do not give each user a login to a separate folder - they will end up all using the same physical netwrked copy of the dbs - which you are general trying to avoid.

so each user needs a profile that directs the login to their own dedicated path.

Dave you are correct and that make things more complicated than one would first think.

What I have done is placed a shortcut on the desktop.
This points to an update file in Dropbox.
The update makes sure the user is using the latest version of the FE. If not it updates it.
The front end is located in a Folder that is not part of dropbox. So each user has a separate copy of the FE.
All Front Ends have their tables linked to the same back end. I spoke about a dedicated machine earlier. This BE is therefore shared between all users. Its location is within Dropbox which naturally updates each user even though they are not using the back end.
This update file (An Access DB) also checks that the path to the dedicated machine. If not the path is changed to the users own back end but the FE is made Read only. The background color of the Main form changes to alert the user that they are read only.
Finally the update file opens the Front end on the users machine.

Remote users are Read only. To give them access you would have to make sure all other users are loged out. For remote users would really only allow for travellers who wish to enter data in their Motel Room after hours. Remote users could at all times open in Read only if they wanted to refer to some data. All remote users are Read Only in my design.

Dave I have done this successfully for very low usage. One user most of the time with 2 or 3 others on occassion.

What you and I understand that many others do not is that there is no direct connection to the File that is kept on the dropbox server. They are wanting to use Dropbox as though it was a server that they had direct access to. Which it is not.

For the information of others reading this, Dropbox and other systems like it are simply smart off line storage that automatically updates itself and each user with the latest version.

A Web based application is really the only correct solution.

Hope I have explained this correctly. If not I am prepared to clarify.

 

[gemma-the-husky]

rain

do you mean that the backend is in a public dropbox folder shared by all users. that would work i expect - although there would be a lot of broadband traffic as the public folder continually updates the backend to the "cloud".

 

[RainLover]

rain

do you mean that the backend is in a public dropbox folder shared by all users. that would work i expect - although there would be a lot of broadband traffic as the public folder continually updates the backend to the "cloud".

Yes there would be a lot of traffic. But I am only using a small office so it is not a problem.

EDIT

The next statement is incorrect.
---------------------------------------
I am not sure but I don't think Dropbox replaces the whole file. Just the part that has changed. I could be wrong with this, not really 100% sure.

EDIT 21 March 2013

Any change to any file causes the new file to over ride the existing file in all Drop Box locations. Namely all those sharing the same Drop Box. It also over rides the Cloud version of Drop Box.

When two people change the Drop Box file at the same time, that is both update the file on their own Drop Box Folder that resides on their own computer, then a conflict occurs and both copies are saved. It is then up to you to decide which file you want to keep.

 

[ma t]

I realize this is an older thread, but its the only one I can find with a similar conversation to my question... i am trying to use dropbox as a way to share a documents folder which will store documents that are linked to a filepath stored in my access table. I have done this before using a shared drive on a network, but I am not sure how to set the file path to the dropbox folder, any help please?

 

[Pat Hartman]

If there is a URL for the dropbox folder, you can concatenate that with the file name and use the FollowHyperLink method to open the file.