Feb 20 2020 08:13 AM
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
Feb 20 2020 08:31 AM
SolutionFeb 22 2020 09:43 AM
@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
Feb 23 2020 11:23 AM
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.
Feb 24 2020 05:25 AM
@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
Feb 24 2020 07:45 AM
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.
Feb 24 2020 10:55 AM
@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
Feb 25 2020 06:38 AM
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.
Feb 25 2020 06:40 AM
Feb 25 2020 07:58 AM
Feb 26 2020 07:34 AM
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".
Jan 16 2023 05:02 PM
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.
Jan 17 2023 09:31 PM - edited Jan 17 2023 09:38 PM
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
Jan 21 2023 06:11 AM
Jan 21 2023 06:30 AM
@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.
Jan 21 2023 06:41 AM
Jan 21 2023 06:47 AM
Jan 21 2023 06:50 AM
Jan 21 2023 06:52 AM - edited Jan 21 2023 07:11 AM
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).
Jan 21 2023 06:58 AM
Feb 20 2020 08:31 AM
Solution