Drop Box for MS Access

<< Click to Display Table of Contents >>

Navigation:  Imports, Exports and XML >

Drop Box for MS Access


Is Dropbox useful for an Access Developer / Office Power User



A MS Access database in a Dropbox folder behaves like a drunk teenager. If you are hoping to share the database using a Dropbox, then it behaves like a group of drunk teenagers.  That's the bad news out of the way.  I love Dropbox, I use it
* in a company with geologists spread across the country
* to distribute 40 pdf reports to 40 customers using the Public Folder
* to move files to the clients computer where I work on them remotely using Logmein
There is good news, read on.


Introduction: For quite a while now I had been contemplating a very popular specialised online tool called DropBox for one of our important projects. Finally I have taken the plunge and I like what I see. This article explains how I think it is useful for developers and users of MS Access and Office.



Figure 1


Online Backup:

Dropbox is a secure file syncing product that moves the latest version of your file to the Internet as soon as it is saved on your hard disk (see figure 1). It does this (as far as I can see) as follows. When the file is saved / created, the online DropBox database is updated. Then DropBox goes about moving the files. You have the ability to pause the movement of the files and you can also make it move the files at a slow bit rate. DropBox will also slow down the movement of the files if it senses other Internet activity going on. Once the files are on the Internet, you can browse the files using a Web Interface. You can store upto 100 gigabytes on the Internet as a single user. There is a corporate plan as well that is more extensive. Use this link to sign up as it gives me an extra 1/4 gig credit :)


Figure 2

Two Computers:

Probably the most appropriate thing for developers is the fact that you can use the same DropBox account on two computers and have files synced between the two computers plus have the files saved on the web (as in figure 2). This only works on files that are located in a folder tree that has a folder named DropBox at the top. You cannot rename DropBox folder but all the other folders are yours to arrange as you will. To show you how smart this is, consider that you have a powerful desktop for most of your development and a lightweight laptop for field visits and occasional work. When both machines are turned on and you are logged in to an account on one of those computers, DropBox recognises when the Local Area Network is available and synchronises the computers quickly using that. This will allow you to start developing on the beast (desktop) rather than the dainty little laptop. The visual clues that DropBox provides makes this pretty seamless. Remember that working on two computers on an Access database or a Excel spreadsheet in this environment relies on one thing. The SYNCING BETWEEN THE COMPUTERS MUST BE COMPLETE otherwise you are working on the old version. The LAN syncing makes this quite practical and the Internet sync in this case is your backup and can progress in the background. This functionality is a very good reason for having DropBox.

DropBox will happily handle folders and subfolders and if you want to get smart you can either stop the syncing of a folder or cut and paste the folder back into your normal environment when you are finished with a project.

Inviting Another Person:

In your DropBox folder, you can right click on a folder and invite a friend to share that folder. DropBox generates a special link which is sent in an email to that person. When they accept the invitation and install DropBox on their computer, that folder will end up in their DropBox folder. From then on if either of you change, delete or add a file, hey presto, that change moves to your computer and your folder on the web. You can later remove that person from sharing the folder at a later date.

If You Both Change The Same File At Once:

I believe that I am just about smart enough not to change a file if I have already changed it on another computer and am waiting for it to sync. I am not smart enough to know when someone else is modifying a file at the same time as me. So to my simple mind, you need simple rules to make this environment work. One rule that may be easy to manage is that one person does 95% of the work on the files and you simply have the files on your computer for help when they need it. At this stage you would notify them that you are going to change the files. The risk of this is reduced as DropBox maintains all the modified files online for a month. It also makes it easy to find these files as shown in Figure 3. This could be appropriate for database prototyping.


Figure 3


Practical Implications For Access:

When you open a database in MS Access, DropBox thinks that you have changed the file even if nothing actually is changed. As a result the file is then synced to the web and any other shared computers. This clearly is only OK with smaller files and only OK when everyone understands the rules for opening the file. This is why you have to think this through carefully. Another consequence of opening the file is that a laccdb or a ldb file is created in the folder to manage object sharing in Access. This file is rapidly transmitted across the internet or LAN to other computers. If you are smart enough, this can act as a visual cue that the database is in use as in Figure 4. I would not grapple with multiple person file sharing until everyone really understood what the issues are and can communicate file ownership rules effectively.

Figure 4

Use Open Exclusive

If you are using an Access database in a Dropbox folder, its best to open it Exclusively.  That way the file is locked until you finished with it.
Sample Shortcut here  (this doesnt work for the backend in linked database).

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" "C:\dropbox\admin\123Invoices\ThickToast2002.mdb"/excl


See this article on Open Exclusive to see how to do this in code


Transferring Files Using Public Folders:

Dropbox automatically sets up a folder called Public. If you place a file in this folder, DropBox can generate a hyperlink to that file on the internet. You can then add that hyperlink to an email and send it to someone to download the file. This link is open to anyone but the link is encrypted so you would need to have a copy of the email to download the file. As soon as you delete the file from your local Public folder, the file is deleted from the web. This approach will reduce Inbox / Sent email bloat by 90%.


Why Not Try It Out:

To get started follow this link to dropbox.com and download the installation file or look at the video's that explain the product. By following this link, you will receive some bonus free shared space. I recommend that you start DropBox gradually until you fully understand how it will benefit you and your associates. It is possible that you may only end up using DropBox for sharing photos and doing some backups. I am sure that there are other products out there that do a similar job but this one is quick, simple, smart and well worth thinking about. In a future edition of this newsletter, I will outline how we are using DropBox.


Once you commit permanently to DropBox, have a good look at a permanent backup facility called Pack-Rat that comes with Dropbox.

Comments by Glenn Lloyd "The other caution is that folder sharers are able to delete files, so when it matters, copy files to those folders, rather than move them there."

by Garry Robinson


Other Articles You May Wish To Read

Drop Box - File Transfers

External Articles

ZdNet on best online data storage solutions (Dropbox wins)