Sharing Access database via online storage (Dropbox, Google Drive, etc). Can it be done?

[Bearlord]

Hello!


I am trying to do a simple database for a small, but very busy, company. I have decided to make it in Access after a lot of reading... there's a huge debate out there in the web related to Access and SQL and I would like to avoid any discussion in this post if possible.


We currently use Microsoft Excel and Dropbox to keep everything. We started having a lot of problems due to the limitations of our current configuration. That's when I decided it was about time to start looking for other options. One of the first results that show up when looking for a database for a small company is Access.


We store a lot of data. We are a steel detailing company and we have to handle tens of thousands of different parts per project. Each part has 20 to 30 fields... so yeah, we really need a database to keep track of our projects. Excel was working fine, but it has become impossible to keep excel synced. We use dropbox to do that and we have come across a lot of issues (different versions being created, people renaming the file, people accidentally erasing a file... etc). We tried using google docs as it can handle real time sync.... but it just can't handle the amount of data we store on it.


I thought on using Access since it seems to fit all of our needs. However I am trying to figure out one last thing. Is it possible to make the database and store it in dropbox? To keep it synced. We wouldn't have multiple users at once, but we do have different locations where we have people that might need information from the database.


I have searched and read a bit about using access through the web. According to what I've found, it seems quite difficult to use Access through the web. I just need it to be available for some users in a different place, but a different time. Not more than one user would be using the database at once. If it could handle two users at once that would be awesome, but its not necessary.


I found this link in another similar post:
Remote access for an Access Application


Its quite helpful... but my question is: Can't I simply put the database in a dropbox folder, split it and place the parts in different folders and share them?. Is there any option that could work under this scheme? We don't have an IT department to make a full SQL deployment.


Thanks for your time!

 

[Bearlord]

Hello


@HiTechCoach: Thanks again for your reply! I know I was oversimplifying things, I hope my post didn't sound like a rant about how there isn't an "easy" way to set up a front end for a web based database.


I gave up on the WAN idea, it was too slow (as you predicted). I am now going to start investigating about the terminal server set up. I hope it doesn't take that long for a newbie like myself.

First, mutli user database deployment is very difficult. It is not fair to compare multi-user database with a spreadsheet. That is like comparing a bicycle to a jet airliners. A spreadsheet or word processor saving a file (bicycle) is very simple compared to a SQL Server (Jet Airliner) receiving data request (read and write) from multi users at the same time.

I know it wasn't a fair comparison. I wasn't trying to compare them on the same level, I was just trying to explain what I consider "easy" to deploy. Using the same example, I was trying to say that it would be great if there was an air-plane that was as easy to fly as riding a bike. That would be awesome, but I know that its really hard to make it possible.


I did look into the Sharepoint server option. It looks really sweet. I might give it a try using the trial version, to see how it works. I really want to take a good decision in this matter. I wouldn't like to be facing a huge data problem later.


Pardon my ignorance, but what is Azure? It sounds interesting. I already have a hosted MySQL server. Can I use it somehow to make the combo Access and SQL? The database would be done mainly from scratch. Importing maybe just a table or two without relationships, right now they are excel spreadsheets. But the idea is to make it from zero.


If you guys have any recommendation of where can I start looking into setting up an SQL back end server for an Access front end, I would really appreciate it.


Thanks for your feedback!

 

[TimHealy]

WOW! HighTechCoach, what clear and concise insight and help with the Access/Web limitations. I have struggled with this for many years (since 1997 lol). I just started using Google Drive to store the back-end of my small access database. All I want it to do is sync (does not need to be live) data between my laptop in the field and the computer in my home/office. I just hired someone to help with data entry a couple of hours a week.
At worst we may simultaneously be modifying the database, on the rare occasion, when I turn on my Pda.Net (hot-spot) in the field to email an invoice to a customer.
Is this another dead-end road?

 

[HarveyFrench]

I'm sharing a database using drop box. I've not done this before, and I'm a little worried about whether the method below will work:

Q1. My first question is whether it is "safe" for a single user to work on an access file help in a folder synced to dropbox. (I've used onedrive like this and it is ok, I assume dropbox will be ok to!)

Here's my second question:

For a database being shared between 3 users that will never use the database at the same time, and who need to share the database over the internet I:

1. Created a folder on dropbox for each user (all can view, only one user can write)
2. created a "not in use" folder (all can read and write )

When a user is using a file they MOVE it to their folder. When they have finished using it they MOVE it to the "not in use" folder. In this way the users can see who is using the database and see whether they can edit the file!

This is very simple way to share a single database for non-concurrent use! It is a bit like sharing a excel file however, the key difference is the access file that is open is "constantly" updated when in use.

NOTE that they all sue the dropbox sync tool so the directory structure on dropbox is replicated on their on C drives.
They work on the C drive. Dropbox syncs the file to the web every minute (as the file is updated constantly!)

Q2 My main concern is what would happen if they do not adhere to the protocol?

Say two of them accidentally opened the file in the "not in use folder" at the same time. I foresee bedlam. The sync tool would in effect overwrite the cloud version of the file with one or other of the copies help by the local user. In effect the last user to clsoe the database will have "ALL" their chnages saved to the cloud and the other user will loose them ALL.

I also wonder whether user1's changes could result in the file being synced to the cloud and then from the cloud to user2 whilst user 2 has the file open (I think not!) (Say user 2 is on a tea break)

Any opinions would be very welcome. (I appreciate the other techniques like terminal servers, access web apps, sql server , but just wonder whether this is a possibility for a very simple database)

Harvey

 

[mhovidz]

Hi Bearlord,

We both share the same problem! I actually try to run MS Access 2010 thru Google Drive and it work fine. I tried to search over the net for the solution if our problem can be done using Gdrive, unfortunately no one tried it and post their experience in any forum. I'm still perfecting it and gathering possible problems that may occur in future.

Here's what I've done. First, I separate the front end (which is also MS Access forms and the table was linked on my backend) and my back end (also MS Access, but pure tables only). Second, I share both files to my collaborators and with the help of teamviewer, I configure the linking of tables to my backend. And finally, we tried to input details at the same time.
​

NOTE: We're using different computers and the app was stored in our Gdrive
​

First I thought that we'll be having problem in AUTO number fields because everyone need to sync their updates, before the updates viewed by all. But it work perfectly fine. Please take note that I'm still observing and conducting series of test if this is the best solution to our problem and what possible problems may occur in future.

If you wish you can try and experiment on your own. :D​

​

​

​

 

[mhovidz]

Suddenly, I realized that this post already 2years old. Anyway, I forgot to share, in order to work the above technique. You need to install Google Drive scrip app. Good luck!

 

[Bearlord]

Hello mhovidz!

Thanks a lot for sharing your findings! Yeah, it's been two years, but I still hadn't found a simple solution for this problem. I will certainly give it a try, even after 2 years. The solution that has been working for me is using a sharepoint list in a sharepoint server. However it is painfully slow. For some reason, even if its a simple database with just a couple of forms and a few tables, as soon as you use the "move data" function to SharePoint, it becomes really slow.

I will run some tests with Google Drive. Just to clarify, where do I get the Gogole Drive Sript App?

Thanks! :D