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!

 

[HiTechCoach]

However I am trying to figure out one last thing. Is it possible to make the database and store it in dropbox?

Nope. It is not possible to use Dropbox for this. Dropbox should only be used for storing backups of an Access database.

I see you found my article. I hope my article was helpful. I have 500+ remote users running my Access applications. I have be running Acess apps over the internet (started with dial-up) using Cirix and Terminal Services since 1997.

I would recommend setting up a PC on your network for remote users. This one PC can be accessed remotely by a single person at a time. They can run any software installed on the PC and us the local LAN just like if they were in the Office. You can even use the free Access Runtime version to run your Access front end. With this solution everyone on your local network can be in the Access app at the same time plus on remote user via the remote user's PC. This is the simplest and cheapest way I know when using Access.

 

[Bearlord]

Thank you very much for your reply. Your article was indeed very helpful.

I will try to set up the machine as you recommend. I am going to give Access a try. I was having second thoughts due to all the discussion out there related to how its so outdated compared to MySQL and others, however it seems to fit very well for my needs. I do wish there was an alternative that could work with a cloud storage network instead of virtual private networks, remote control, etc. In our case, I can see all the problems I will have trying to set up the network controlled pc due to different OS versions, different networks, etc... and a cloud storage based database sounded perfect for my problems. I would just need to share the folder and I'm done xD. I guess that was too good to be true. However before i completely give up on my "dropbox" database i would like to post an idea to get feedback from the expert community.

What if... with the aid of a third party script (aka AutoIt, AHK, VB, etc) an access database could be used through a cloud storage service. I know it would have a LOT of limitations, but for a small company it would do wonders. I am pretty sure that using Access saved in dropbox won't cause any problems as long as you are the only user "using" it (srry about that). What if instead of simply launching the database, you would launch a small app that would look for the database, check if anyone else is using it and then launch it if no one is. If some is using it, it could create a copy, store it somewhere else and launch that instead. Of course it would tell you it's for "view only" because it is in use. Perhaps in a more complex idea, if the database is in use and a second user wants to work with it... it could create a copy and after he is done doing his changes, it could just send the copy to the computer that has the main database and then that computer could import the changes as if it was simply an updated table. Perhaps this second thought is too complex to be done but what about the idea of an app and the cloud storage service. Do you think it could work? What are the problems you see in this idea?

I would really appreciate your feedback!

Sincerely,
Bear

=)

 

[HiTechCoach]

I was having second thoughts due to all the discussion out there related to how its so outdated compared to MySQL and others,

Yeah you hear that a lot from people that have never used Access and/or are Anti-Microsoft.

See: Did you know that Access is NOT a database but a Front End?

I have built Access applications (front ends) that use mySQl and Microsoft SQL Server for the data storage (tables/back end). Even connecting to remote a SQL Server.

however it seems to fit very well for my needs.

Access tables are not a good fit for this. You will need to use a different back end.

Note; SQL Server will not work with inside Dropox.

I do wish there was an alternative that could work with a cloud storage network instead of virtual private networks, remote control, etc. In our case, I can see all the problems I will have trying to set up the network controlled pc due to different OS versions, different networks, etc... and a cloud storage based database sounded perfect for my problems. I would just need to share the folder and I'm done xD. I guess that was too good to be true. However before i completely give up on my "dropbox" database i would like to post an idea to get feedback from the expert community.

FACT: The Ace and JET database engines (Access's default database engine - See link above)) are not true client/server but file/ server. The only work over a LAN. This mean cloud store is not an option.

You could use a remote hosted SQL serve r(mySQl or MS SQL) and link to the table over the internet.

Setting up a database for remote access is very complex.

Using Remote control or Remote Desktop sessions with Terminal Services or Citrix is the simplist wat to get remote acess to an Access applicaiton. You can run most lots of applicaitn this way thsat ere never designed to be used over the internet. Why" Because using Remote control or Remote Desktop you are not actually running the application over the internet.

What if... with the aid of a third party script (aka AutoIt, AHK, VB, etc) an access database could be used through a cloud storage service. I know it would have a LOT of limitations, but for a small company it would do wonders. I am pretty sure that using Access saved in dropbox won't cause any problems as long as you are the only user "using" it (srry about that). What if instead of simply launching the database, you would launch a small app that would look for the database, check if anyone else is using it and then launch it if no one is. If some is using it, it could create a copy, store it somewhere else and launch that instead. Of course it would tell you it's for "view only" because it is in use. Perhaps in a more complex idea, if the database is in use and a second user wants to work with it... it could create a copy and after he is done doing his changes, it could just send the copy to the computer that has the main database and then that computer could import the changes as if it was simply an updated table. Perhaps this second thought is too complex to be done but what about the idea of an app and the cloud storage service. Do you think it could work? What are the problems you see in this idea?

A lot. Most it will rarely be reliable with Dropbox.

What you have suggested would work great at scrambling your data and have lots of data loss.

Cloud storage like Dropbox is great. Just not for true multi-user applications like databases.

I have worked several client who hired programmers that have spent many months of work (100s of hours), one over a year, trying to do basically what you have described. Only to find it was very complex, not reliable, and took a lot of man hours to support. In less than a week I have them fully functioning. Some use Remote control somne use Remote Desktop session.

Windows XP and later have built-in to allow a single Remote Desktop sessions. Everything can usually be set set up in less than 10 minutes. This is what I use a lot when you need only a single remote users at a time. It is fast and reliable. No special programming required. No changes are required to the Access database if already split into a front end and back end. No need to learn SQL Server. No additional recurring hosting fees (dropbox etc).

 

[Bearlord]

txt Mr.Excel Dropbox Idea.


Hello HiTechCoach,


Thanks once again for your reply. I really appreciate your feedback and the time you put into answering my doubts point by point. The explanations leave me with no doubt that what I am trying to do is quite complex, if not impossible, haha. However I find it disappointing... that in 2014 we don't really have an easy to deploy database software. We have awesome technology surrounding us. Sync is everywhere, I can't believe there isn't one way to use all this technology to keep some data organized. I know I am over simplifying things, that syncing a database is not an easy task.


About the remote desktop and remote control, I know this might be really easy to set up for some people. Actually I find it quite easy as well, however I also find it a very buggy process. It works with some computers, then it doesn't work on others. Or it works in a computer but later in that same computer it stops working, even though nothing has changed. I know this because I've done this in the past. I know its all problems related to our network itself, we don't have the same OS on every PC, we don't have fixed IP, we don't have a decent router, and so on... and I know all those problems could be solved by keeping all of this under control (same OS and versions in all the computers, same network in all our pcs, etc). However that's not the panorama for the average small company. We work with what we have. We will invest in technology to help us be more productive, but for a smaller company its impossible to invest that kind of money when it is starting.


Sorry if this sounds like a rant, it doesn't pretend to be one. I just want to make a point: There is a need of an easier way to set up a database. I know that writing all this won't make it happen. I am just explaining one example where it would be useful.


On another note, I tried to set up my network this weekend. Its working, but as I thought, its full of bugs. Some people can connect, some people can't. I read something about Hamachi and setting up a "lan" over the internet. I tried that and it seems to work, but that might be another time bomb waiting to destroy all my data.


The article about Access being a front-end is so informative! I didn't know that. It solved many doubts I had about using Access. Now I see things differently, I even think that the posts and blogs I read about how Access wasn't a good option are based on not understanding what Access is (I didn't know myself!!).


After all the help I've received from you and other articles I've read on the net... I've decided that perhaps the best solution might be investing in a SQL capable server. It might take a while before I manage to do that, since I am not an expert, but in the long run I think it will be worth it. I want a fast reliable solution. Maybe we will have to struggle a bit longer with dropbox, excel and emails, but once I'm done I can forget about my nightmares! I hope!!! haha.


Thanks a lot HiTechCoach!


Sincerely,


Robert

 

[HiTechCoach]

Robert,

I have installed 100+ networks for small businesses. I have a lot of clients with 10 or less PCs at a single location. They rarely have networking issue.

On another note, I tried to set up my network this weekend. Its working, but as I thought, its full of bugs. Some people can connect, some people can't. I read something about Hamachi and setting up a "lan" over the internet. I tried that and it seems to work, but that might be another time bomb waiting to destroy all my data.

A LAN over the interent is called a WAN. Access will not work over a WAN. Just to slow and not reliable.

If you are having that many "bugs" with your network then dropbox of file syncing will be a nightmare if impossible to use fast and reliably. You can expert data loss.

You are actually making a very strong case for Terminal Server or remote control. Not for dropbox or any external (cloud) storage.

However I find it disappointing... that in 2014 we don't really have an easy to deploy database software. We have awesome technology surrounding us. Sync is everywhere, I can't believe there isn't one way to use all this technology to keep some data organized. I know I am over simplifying things, that syncing a database is not an easy task.

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.


Muti-user database with remote users is very common today. There is facebook, google, etc. which are considered truly web based since they run in a browser over the internet. Writing a web apps takes a lot of time and money. You will also lose a lot of the functionality that you get with a true Rich Client/desktop app like Access. HTML5, JAVA, .NET etc ave rally made what a Browser based app can due a lot better.


There have been a easy, fast, and reliable way to deploy database applications over the internet for remote users for 15+ years now. It is using a Remote Desktop session with Citrix, Microsoft Terminal Services, and others. It has been around a lot longer that Dropbox, Google Docs, and other cloud storage. Using this technology you can easily (no application changes) , fast, and reliably run true Rich Client/desktop app like Access over the internet (not he same as the web)


You probably don't want to hear this but setting up a terminal server will be the faster, cheapest and mot reliable solution to run a multi-user database. Since you have some much networking issue I would urge you to high a networking professional to set it up properly. The few hours you pay them will help save your business. Computers that are networked is the digital body of your business. Hire the best networking guys you can find to make sure your business is health just like you would for your own body.

 

[Kyle123]

I agree with all the above especially with using Citrix/TS. @HiTechCoach Out of interest and slightly off topic, but what about an Azure/Access combo? I don't use access, but it seems to me that this has some major potential benefits - though I don't know how easy this would be to set up.

 

[Kyle123]

Or maybe hosted sharepoint?

 

[HiTechCoach]

I agree with all the above especially with using Citrix/TS. @HiTechCoach Out of interest and slightly off topic, but what about an Azure/Access combo? I don't use access, but it seems to me that this has some major potential benefits - though I don't know how easy this would be to set up.

I am using Azure now with a hosted SQL Server as the back end with an Access front end on the local machine. It takes a lot of VBA programming skills to pull it off.

 

[HiTechCoach]

Or maybe hosted sharepoint?

To get the most out of Sharepoint you will want to use Access 2013. You could recreate e the database as a web database . Unfortunately an Access web databases has very limited is power since no VBA code is allowed. Or You could use a hybrid databases with an SQL Server back end through Sharepoint.

If you wan to get it working reliable, fast, and affordable in less than 8 hours setup time then use Terminal Services or Citrix, You can run any version of Access without having to rewrite your database or go with an SQL Server back end. There are even very affordable hosting solutions if you want to use a public cloud instead of a private cloud (self hosted).