SOLVED

Migrating Access back end to the cloud

Copper Contributor

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

 

39 Replies
best response confirmed by GrahamCresswell (Copper Contributor)
Solution
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

 

@GrahamCresswell 

 

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.

 

@George Hepworth Thank you.  That is what I hoped would be confirmed and indeed what I have been trying to do.  I'm afraid that I simply can't make the migration process work.  I have a SQL database set up in Azure, which I can see via portal.azure.com.  Within my Access back end I have six tables, all indexed and linked to the primary key.  Within Access, if I drop down the [External Data] tab and go to [more...], I'm offered "ODBC Database Export selected object to an ODBC database, such as SQL Server", which seems to be what I want. When I select that, it offers to export the first of my tables. Then it starts to get opaque.  I'm invited to select the file data source that describes the driver that I want to connect to, which appears to be a DSN.  At some stage in this voyage of discovery I must have downloaded "ODBC Driver 17 for SQL Server" because I can choose that.  But there's also "SQL Server", "SQL Server Native Clent 11.0" and "SQL Server Native Client RDA 11.0".  Then it asks which SQL server I want to connect to and I've copied the server name from my portal.azure.com.  Then there are 5 options for verifying the authenticity of the login ID.  The default is "With Integrated Windows authentication"  I have no means of deciding which to choose.  Then there's a box called "Create a New Data Source to SQL Server" which sounds promising but which contains 8 options of impenetrable jargon but one option (READWRITE or READONLY) which I understand.  I've accepted all the defaults including READWRITE.  Then there's another box, also called "Create a New Data Source to SQL Server", with 6 incomprehensible choices.  I've accepted all the defaults.  Then we get to the long-awaited [Finish] which tells me that "ODBC Driver 17 for SQL Server Failed to authenticate the user in Active Directory (Authentication option is WindowseIntegrated) Error code 0xCAA100001;state 10".  

 

As you can see, there are plenty of hurdles to get over.  

 

Kind regards and many thanks for continuing to take an interest.  

 

Graham Cresswell

 

@GrahamCresswell 

That initial set up can be daunting, for sure.

 

For starters, "I have a SQL database set up in Azure, which I can see via portal.azure.com."

 

Okay, you'll need to ensure that the appropriate IP address is allowed to connect through the Azure Firewall. It should, but double check that here.

AzureFireWall.png

 

Again, that should be okay, but verify it.

Actually, I would NOT use the Access Export function to move tables to the azure SQL database. I would use SQL Server Migration Assistant to do it. It's more flexible and, I think, a little more user-friendly. Download the latest version, which is I think this one, Version 8.6 

Make sure you select "SQL Azure" as the destination. See how that goes. I think you'll find it easier to work with.

 

Oh, and you want "SQL Server Authentication". Windows Auth only works with a SQL Server which is on the same Windows network where you have a Windows login.

@George Hepworth Thank you.  You'e being incredibly patient and generous of your time.  

 

The first catch was that I've been using unblock.us to give me access to UK TV.  It had loaded a different IP address into the portal.  I've removed unblock.us and restarted the computer and the client address shown in portal.azure now conforms to the ip address returned by my whatsmyip.org.  The same ip address has also been added as a rule along with a range from xx.0.0.0 to xx.192.192.192 that includes my ip address.  Since neither I nor my users will have a fixed ip address, I'll have to come back to that later.  

 

I've checked that the version of SQL Server Migration Assistant that I have is v8.6 and I've started a new project.  I've done this so many times I'm now on to SqlMigration9!  In the top left hand panel, I'm able to select the folder in which my Access back-end resides.  When I click "Connect to Azure SQL Database" and enter my password, I get a connection string, which seems encouraging.  In the top left panel I can select either Tables or Queries or both.  I selected Tables as the queries are all in the front-end.  But I see that, when I hover over the name of the database (CorallDataAzure) I get an error message relating to the COM class factory!   I'm certainly running 64bit Windows, 64bit Access and, since the Migration Assistant .exe file resides in the Program Files folder, I assume it is also 64bit.  

GrahamCresswell_0-1582570087061.png

 

So I fear I may have hit the buffers again. 

 

Kind regards

 

Graham Cresswell

 

 

@GrahamCresswell 

Ah, yes, there is, or was, a problem with regard to 64 bit Access. I don't have the reference off the top of my head, but it involves downloading and installing the proper bits. I'll go look for the discussion. 

@George Hepworth 

 

Still confirming, but I think this is what you need.

@George Hepworth 

 

Here's a good article which talks about some of the issues around this topic.

Many thanks.  @George Hepworth That seems to imply that, since I am using Access 365 as a part of Office 365, it should work "out of the box".  

@GrahamCresswell 

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.

The only "impractical" situation for VPS use of an Access solution, in my estimation, is for personal use. That is, whenever you can't afford your own terminal server (or alternatively a hosted VPS).

A company that cannot afford this is, in my opinion, doomed to failure.

The alternative cost of using the Access solution via terminal server/cloud VPS would be to redevelop a PHP front end as a replacement. We all know what that costs.
Well, I don't think DropBox is a good alternative for Access applications, though. Therein is the problem. As noted before, in order to share an Access accdb, the file system MUST support the ability to update only one part of a file, e.g., CRUD operations on records in a table. At the same time, other users can also be updating other parts of the same file, e.g. CRUD operations on other records in the table or other tables. The lccdb locking file you see when an accdb is in use is part of that; it tracks who is currently operating on that accdb.

The key is that changes are saved without replacing the entire accdb. And, as a result, multiple users can share the Back End.

Windows supports that.
DropBox and OneDrive, and similar services do not. It's "all or nothing" when saving changes in those environments. If two people have tried to change different records in the same table, or in different tables, the last to save wins all the chips. I.e. their copy of the accdb -- with their changes in it -- overwrites the original accdb, including any recent changes made by other users.

Obviously, in a single user situation, that works, because that one user is not contending with other users to keep their own changes. In a multi-user situation, it becomes a management circus, at best.

SharePoint lists, on the other hand, CAN be used in a manner similar to the way an accdb on Windows handles changes. It's not free because you must have at least one MS 365 account to host the SharePoint lists, but it is, at least, a mult-user capable approach.

"A company that cannot afford this is, in my opinion, doomed to failure."

I wonder how many thousands of small businesses would agree they are doomed to failure on that basis. Or maybe the count is in the millions....

Using DropBox also does not bring the frontend to any operating system, but only to Windows systems (as opposed to Terminal Server/VPS).

 

An Access application deployed via a remote app on an Android or Apple smartphone or tablet will run there with a few adjustments just as it would on a normal Windows desktop (apart from the limitations imposed by the small screen).

The cheapest VPS with 4 cores, 8 GB RAM, 200 GB SSD storage and AMD EPYC™ cost just 180 EUR net per year.

And what exactly is the viable alternative to bringing Access to virtually any mobile device?
1 best response

Accepted Solutions
best response confirmed by GrahamCresswell (Copper Contributor)
Solution
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.

View solution in original post