Forum Discussion
Migrating Access back end to the cloud
- 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.
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
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.
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.
So I fear I may have hit the buffers again.
Kind regards
Graham Cresswell
