Sharing and updating an ACCESS database on the cloud

[MALGhalayini]

I have implemented an ACCESS database. I would like to store it on the cloud so my colleagues can access it simultaneously. Some of them may be updating some data while others will look for certain records and files being stored within the database. I realize that I should do all my design and coding on my local PC then upload the file on the cloud; however, this central cloud copy of the database file should be accessed and updated while it is on the cloud by multiple users.

I am not sure if storing on OneDrive would allow me to do this. I would highly appreciate any experienced developer in this issue. 

[#2]

@MALGhalayini 

OneDrive is NOT an option. There are technical reasons for that, which you can learn more about here and elsewhere.  

Access runs on the desktop, under Windows. The interface elements, including forms, reports and code, must, therefore, be on a Windows computer, not in the cloud.

The data, however, can be uploaded to a cloud location. You have a number of options, including SQL Azure or a hosted SQL Server database, other cloud hosted database engines, and even SharePoint lists.

To deploy your relational database application this way, you start by splitting the interface objects from the data in the tables. That allows you to connect the interface--which we typically refer to as the "Front End"-- to the tables regardless of where they are and which database you choose.

Each of your users must have their own copy of the Front End; it should be on their computers. This is critical in order to avoid, or at least minimize, corruption that can result from trying to share a Front End.

There are other approaches, such as a Remote Desktop deployment in which you place the Back End--the tables with the data--on a shared network location and allow users to remotely connect to it. This is a whole topic of discussion, so if you want to explore this option, you'll need to do your due diligence.

[#3]

Dear George

Thank you for your reply.

I found a solution where I can store my database on Windows Server located on cloud.

This service company assured me that things should work fine; however, they told me that I have to have a license subscription for Office 365 for each user. 

I am not sure why I have to have this when I have Office installed on each user computer.

I guess I have to discuss this with Office 365 technical team.

@George_Hepworth 

[#4]

@MALGhalayini 

That is another one of the options, but not the one I'd necessarily select as the first choice.

It's not clear which service you've contacted, but one thing you'll need to be sure of is what they actually offer. Some services actually move the data from the Access accdb into their own proprietary database and replicate the Access interface as web pages. So although it's similar to your current accdb, it's not really Access any longer. 

Other services may have other approaches.

Do your due diligence before committing. I'm not suggesting there's anything wrong with any of the options. Just that you need to be sure the solution works for your organization in an appropriate, cost-effective way.

[#5]

Thank you for your useful reply

I will test the offered solution and see what I get.

Best Wishes

@George_Hepworth 

[#6]

I guess you need a host which provide web service to access the database.

[#7]

if asp,maybe like this
http://anyoupin.cn/bsbm/stu60/sqlDao/sqlEditor_dao.html

you can use VBA xmlhttp object to interactive with remote database

[#8]

@MALGhalayini What I do for my office is:

I have taken real IP (Public IP) from my internet service provider. I have installed MSSQL server to one of my Windows Server computer and assign that public IP to one of network interface so that MSSQL database can be accessed via that real IP. Now I have developed front-end database objects and liked those objects to MSSQL table through linked table. Now, my users can use that database from any where from office (I have 3 different location) as well as home. Obviously I have limit access who do not need to access the database from outside of office. You may think this model or SQL database hosted in cloud.

[#9]

@Harun24HR 

Thanks. That's actually the method I prefer, and which I often used for clients when I was still working. The only thing I'd want to clarify is whether the term "in the cloud" means the same thing for everyone and whether this approach is what is often meant. I think you are right in that most people say "in the cloud", but really mean that they can have users located in many different places, as you described.

[#10]

Dear Mr. Harun

Thank you for sharing your experience ...

I guess those employees are shooting for using ACCESS hosted on cloud  server

@Harun24HR 

[#11]

A quick related question related to our previous subject :
If the user is subscribed to Microsoft Office 365 to be able to use the cloud ACCESS file, does he/she needs MS-Office to be installed on his local PC ?

[#12]

Access runs ONLY on a desktop computer. It is part of most versions of MS 365, but not all. I would imagine that a business would have a version that includes it. It is installed, however, onto the local PC to run the Front End.

[#13]

Migrating Access backend data to a SharePoint List is surprisingly easy (lots of resources available on how to achieve). This includes attachments if you utilise the functionality in Access.
All good if you use SharePoint as a business of course!