Sharing database using Dropbox

[Nocton]

Previously, with the help of this forum and especially DACM, I have set up my database in server mode. Stability and reliability has been excellent. The database has never crashed nor lost any data, since being converted to file server mode. To share among users, when one user updated the database, the whole Run folder was zipped and then emailed to the other users, who would unzip and replace their existing Run folder. This has worked quite well, but I have now got an improved method of sharing the database.

On each PC I install a Dropbox folder (http://www.dropbox.com) containing a shared Run folder. The START.vbs script is modified to copy files from the Dropbox Run folder to the PC Run folder so that the user's database is always up-to-date (I add an 'Update from Dropbox Y/N?' option, just in case the user has some data he does not wish to have overwritten). After editing the database I have an UPDATE.vbs script that copies the PC Run folder to the Dropbox Run folder. This works very well, and is much easier for users than the zip/unzip method. As Base does not support multiple users, to avoid conflicts all that is required if a user wishes to edit the database is for them to email other users to let them know. In fact Dropbox does protect a little against conflicts in that a new file is created if a conflict occurs, but this is not much use in this case. Mostly when a user opens the database they do not wish to edit the data, but instead to look it and print reports, so no notification to other users is required; but they do know that they always have the most up-to-date information without doing anything.

[ps_jayadev]

I am trying to share base using dropbox. I could not understand your explanation on how this could be done.

Can you please give a detailed step by step process on how to set up can be done?

Thank you very much.

regards,
Jayadev.

[Nocton]

Are you using file or server mode as described in DACM's QuickFix?

[ps_jayadev]

I tried using file mode. Also please let me know how a database can be used in a front end - back end mode using dropbox, so that data corruption can be avoided.

[Nocton]

OK, so first set up your DropBox folder on one PC and then invite another user/PC to share the folder. I am using server mode so I need server.start.bat as in DACM's QuickFix.

Assuming you have your database in a folder called C:\Database\Run and any documents, e.g. merge files, help files, etc. in a folder called "C:\Database\Documents"
Make a vbs file called START.vbs containing the code:

Code: Select all

Dim sDropbox, sPCRun, sPCDocs, sFileName, sTitle

' /////// NOTE: Edit variables in this section ///////
'
sDropbox = "C:\Documents and Settings\User\My Documents\Dropbox"
sPCRun = "C:\Database\Run"
sPCDocs = "C:\Database\Documents"
sFileName = "server.start.bat"
sTitle = "Run Batch Silent"
'
' /////// NOTE: Edit variables in this section ///////

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")

'check if wish to update
Reply=MsgBox("Update from Dropbox?",4,"UPDATING")
If Reply=6 then ' 6=Yes=update
    If oFSO.FolderExists(sDropbox & "\Run") Then oFSO.CopyFolder sDropbox & "\Run", sPCRun 
    If oFSO.FolderExists(sDropbox & "\Documents") Then 	oFSO.CopyFolder sDropbox & "\Documents", sPCDocs
    Reply=msgbox("Database has been updated from Dropbox", 0,"UPDATING FROM WEB")
Else
    Reply=msgbox("Database has NOT been updated.",0,"UPDATING FROM WEB")
End If

If Not oFSO.FileExists(sFileName) Then
	oShell.Popup sFileName & "  -  batch file not found", 10, sTitle, vbCritical + vbSystemModal
	Wscript.Quit 1
End If

' run the batch file with quotes added
iRC = oShell.Run("""" & sFileName & """", 0, True)

' Return batch file error level when exiting script
Wscript.Quit iRC

This code is adapted from DACM's START.vbs file. I think it should be self-explanatory. You will need to alter sDropbox as your DropBox installation will not necessarily be in "C:\Documents and Settings\User\My Documents\Dropbox".

Running START.vbs will copy the required folders from the DropBox folder on your PC to the working/Run folder on your PC. When you have finished editing you will need to Update the DropBox folder on your PC, by copying the files from your working/Run folder to your DropBox folder, i.e. reverse what START.vbs does. Here is my UPDATE.vbs code:

Code: Select all

Dim sDropbox, sPCRun, sPCDocs
' /////// NOTE: Edit variables in this section ///////
'
sDropbox = "C:\Documents and Settings\User\My Documents\Dropbox"
sPCRun = "C:\Database\Run"
sPCDocs = "C:\Database\Documents"
'
' /////// NOTE: Edit variables in this section ///////

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")

'check if wish to update
Reply=MsgBox("Update Dropbox?",4,"UPDATING TO WEB")
If Reply=6 then ' 6=Yes=update
    If oFSO.FolderExists(sPCRun) Then oFSO.CopyFolder sPCRun, sDropbox & "\Run" 
    If oFSO.FolderExists(sPCDocs) Then oFSO.CopyFolder sPCDocs, sDropbox & "\Documents"
    Reply=msgbox("Dropbox has been updated", 0,"UPDATING TO WEB")
Else
    Reply=msgbox("Dropbox has NOT been updated.",0,"UPDATING TO WEB")
End If

Again, it should be self-explanatory. Just adapt to your situation.

Regards, Nocton

[ps_jayadev]

Thank you for the explanation.

If I install OO on all the systems and then install dropbox folder connected to a single account in all the systems and directly place the .odb file in dropbox, I think all of them can access the file and any changes made by a person would reflect everywhere else. This is very simple and no other setup is required.

I actually tried this and it is working well but I would like to know if there would be any issues, which you feel would arise in the long run.

I will also try your option as I am planning to get this on a server mode.

[Nocton]

Well done! Your set-up is OK, but if two users start to use the database at the same time you may have problems because the .lck and .log (in file or server mode) and the .odb files will be interchanging with each other via DropBox. Especially if you not using file and server mode I think there is a big chance of irrecoverable crashes, which happens with Base anyway. By irrecoverable, I mean that you lose all you data. By using a working/Run folder many users can use the database for using the data and making reports as long as only one is editing the data. With my system, no user needs to inform another user that they are 'on-line' unless they are going to edit the database.

One nice thing about DropBox is that you have nine previous copies of all your files so that in principle you can always revert to how things were at an earlier date

Regards,

Nocton