Forum Discussion
Migrating Access back end to the cloud
Hi. I am an amateur. Using Access 365, I have created a small Access database for a local charity and I would like it be usable by 3 or 4 people at once and so I think I need to get the back end (the tables) into the cloud. I have tried to set it up with Azure SQL, SQL Server and I've tried to use SQL Express as a stepping stone. But, in every case, the technology is impenetrable. I have downloaded endless software packages that I think the guys in India have recommended (although the language barrier is very difficult) so that I have installed MS Data migration assistant, MS SQL Server Express, MS SQL Server Migration Assistant for Access, MS SQL Server Tools 2018 and SQL Server Configuration Manager. Can anyone direct me to a step-by-step procedure, suitable for dummies, that can help me achieve this?
Regards
Graham
- Do you want to run this relational database application as a web - browser based application, or as a desktop application?
I don't know which "guys in India" you mean, or what they recommend, but you really don't need a lot of other software packages if you are going to continue to use this as a desktop application. However, if you want to replace the Access interface with one that will operate in a web browser, you WILL have to invest in and learn to use the appropriate tools for that.
So, start by explaining who will actually use it, where they are physically located (in a single facility or dispersed among several) and how you anticipate using this relational database application. From there, we can examine some options.
- Do you want to run this relational database application as a web - browser based application, or as a desktop application?
I don't know which "guys in India" you mean, or what they recommend, but you really don't need a lot of other software packages if you are going to continue to use this as a desktop application. However, if you want to replace the Access interface with one that will operate in a web browser, you WILL have to invest in and learn to use the appropriate tools for that.
So, start by explaining who will actually use it, where they are physically located (in a single facility or dispersed among several) and how you anticipate using this relational database application. From there, we can examine some options.George Hepworth Many thanks for quick reply. I anticipated using laptop (or desktop) versions of Access runtime to allow 3 or 4 users in different locations (indeed different countries) to connect (simultaneously if that so happened) to the back end of the database that is installed on some server in the cloud. The back end consists of 6 tables and is also written in Access 365.
I'll be delighted if I "really don't need a lot of other software packages if you are going to continue to use this as a desktop application", as I found almost all the settings offered in Azure SQL, MS SQL and the various "assistants" completely incomprehensible.
The "guys in India" are the folk who contact you when you ask Microsoft for help. But I have the impression that MS had outsourced the help to an outfit called "Mindtree Limited".
With thanks for your interest in this.
Graham Cresswell
Okay, then you can do this, but not exactly as you are thinking.
You can't really put the existing accdb back end files "in the cloud" that way. You'll want to move to a server-based database, like SQL Azure or SQL Server. Access simply isn't secure enough to be used that way.
That said, once you migrate the data to the server-based database, you can link your existing Access interface (the "front end" accdb) to the tables there and use it very much like you do now.
Depending on the architecture of this relational database application and the interface design, you may end up making some significant changes in the interface to compensate for the latency of remotely linked tables. Specifically, you will need to ensure that your forms and reports always load the fewest number of records possible. This contrasts with the classic Access design of loading a form bound to a table and then applying a filter to it, or navigating to a particular record. That is slow and it won't fly in the remote environment. However, once you get that worked out, you'll find it's pretty straightforward.
This is not an ideal solution but I store a copy of our backend into a DropBox folder. I then wrote the code that sync's all my tables to it. 10 people work together this way. Occasionally, there are conflicts but DropBox shows when this happens and I just delete the conflicted copy and Sync again and usually everything works fine.
Hi Graham,
if I understand correctly, you want to connect a local Access frontend to a database (Access, MySQL or SQL Server) located in the cloud.
On the Access frontend it should be possible to view, modify and delete data as if the database was local.
From my point of view, a database connection from the cloud to a local frontend (of any kind) is not practicable today. Because much too lame.
For this purpose it is much better to use a terminal server system, which can be operated either locally at the customer's site or alternatively as a VPS (virtual personal server) from the Cloud.
In the form of a remote app, the Access frontend solution can then be operated on the terminal as if the program (Access frontend) were installed locally there.
Many greetings
Stephan- Lots of companies would disagree with that conclusion. If it's done right, Access/SQL Azure is just fine.
Other options, such as a VPS are also just fine.
At some point, cost has to be considered as well. It's all well and good to recommend an approach, but if the implementation cost is high enough, it's totally impractical for many situations. One has to consider the entire picture.
One reason we see people trying to get by with SharePoint lists, or OneDrive or DropBox, is that the costs of other implementations are outside their budget.George_Hepworth I completely agree. There are various solutions for putting the back end in the cloud but it terms of cost and complexity using DropBox for a small company without an IT department is a viable option. Just wish there was a simple good one.
I found one method that works but there is a caveat to be aware of, that is by syncing the SharePoint library to OneDrive you can link the front end to the back end in the windows folder structure for the SharePoint library that is being synced via OneDrive. The caveat is I have not done testing with multiple user input scenario with the delay of OneDrive syncing the backend to the actual cloud location.
The use of the export to SharePoint function in Access 365 works well and makes library of lists with the data from the tables in it, the tables in Access become a link to SharePoint, you have to have a properly structured database, or you will get errors when moving data to SharePoint, there is plenty of docs on the web that show you how to do this.
Don't forget to always backup 1st...
Regards... Matt
- I can't imagine that it would work for a multi-user scenario because of the inherent limitation of OneDrive.
