Access with SQL Azure cloud tabled

Hi. I  looking at moving my split Access database tables from Access into a SQL Azure cloud database. Does Access work well with a cloud SQL table backend?0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Define "well".

Does it work? Yes. But two problems: you need a stable internet connection and you will have latency issues.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, have you developed the application based on correct client-server techniques? Given your recent questions here I'd think the answer to that is "No", which means you'll have even greater performance issues than normal. An Azure database is little more than a cloud-hosted installation of SQL Server, so you would need to first be sure that you're well versed in dealing with SQL and MS Access.
0
Gustav BrockCIOCommented:
It works fine, but it is slow unless you go for a high-priced instance.

I would leave this option until you have no more things on the to-do list.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
The bottom line is I do not have any other alternative in this project. I have rewired the system to be most efficient according to your expert feedback. For example I had an alphanumeric field as a key field but changed all my tables to rather use autonumber fields as the key.
So I will be doing this and need a few tips on how to make it work.
0
Gustav BrockCIOCommented:
It's relatively(!) easy to check out.

You can obtain an Azure trial for some time for some limited resources.
Then use the tool for converting the backend to Azure SQL:

SQL Server Migration Assistant for Access (AccessToSQL)

Be aware, however, that in a 32-bit Office environment, the newest version will not work as it is 64-bit only, so either obtain an older version like 7.3 or create a (virtual) machine with 64-bit Office installed.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. I have been using Azure SQL for years. I mainly develop Azure SQL solutions with ASP.net, so building that side of it is not a problem. I am mostly concerned about the Access side.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I have never tried to use SQL as the backend for an Access solution. This I what I need advice on
0
Gustav BrockCIOCommented:
Oh, good.

The issues - or rather tasks to perform and considerations to take - are pretty much the same as if you use a "normal" SQL Server.

There are many sites and guides covering this area, you can search for - including here at EE.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In short, be looking to leverage the server side as much as possible; use of views, pass through queries, stored procedures, and triggers.

 Be aware of a couple of things in queries that will always force processing client side:

1. Joining on a local table.
2. Use of a VBA expressions
3. Use of JET specific SQL

 In regards to what Scott was talking about, it's more about the design of the app.   For example, in Access, you think nothing of binding a form to an entire table and letting the user use the built-in filtering.

 In client / server, you don't do that.   You would present the user with a light weight list of records, let them choose one, then only fetch that one record.    Most Access apps are not written that way.

 and I have to say, Gustav is the first person I've heard that has said they can find acceptable performance.  Most I know that tried gave up.   But a few years have gone by, so things may have changed.

Jim.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much Jim. Keeping the bulk of the processing server side and keeping it light client side makes a lot of sense.
Murray
0
ste5anSenior DeveloperCommented:
and I have to say, Gustav is the first person I've heard that has said they can find acceptable performance.  Most I know that tried gave up.   But a few years have gone by, so things may have changed.

Well, I have also some applications running that way. But latency is (in Germany) even on 500MB/s lines a problem. So it works "well", but not fast.
0
Gustav BrockCIOCommented:
Gustav is the first person I've heard that has said they can find acceptable performance.

Well, but I didn't say "acceptable in all cases". However, quite a few application only pull and write a few records at the time, and then the convenience of a cloud instance and the associates backup services can outweigh the latency and slow performance and/or the costs of alternative options.

The speed of the line is of little importance. A few Mbits/s will in many cases prove sufficient for the tiny instances.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.