Solved

Access 365 Desktop App Pointing to Azure DB

Posted on 2014-07-18
1
884 Views
Last Modified: 2014-11-12
Transparency:
The points for this question will probably be given to either Jack Leach,  Joe Anderson, or  Jim Dettman for the great job they did on this  Question .
All opinions are invited.

Below questions presume the person knows SQL Server and Access

Question #1
If in Access 365/2013 if you want to build a Desktop App hooked to, SQL Server (Azure) back end on the MS's cloud
how easy is it to
      Build Tables
      Build Views
      Build SP
      Build Functions

Question #2
Is there someplace to get an introduction to the above - not a book, but a place on the internet that give a good, but short explanation?

Thanks in advance
LJG
0
Comment
Question by:LJG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 4

Accepted Solution

by:
Jack Leach earned 500 total points
ID: 40206791
You called? :)

For the most part, you can use Azure as easily as  BE as you can with an on-premise SQL Server installation.  The only real consideration is the fact that you're working, of course, over a remote connection to your data, and as such your application's design needs to accommodate for it, specifically in retry logic and in putting emphasis on server-side data processing instead of client-side.

Before I go much further, I'll point out that the link Russell gives is specifically for use with a Desktop application that's connected to a pre-configured Azure database that an Access Web App is using... this is different than what you're asking.  Access Web Apps use Azure as a BE, and we can connect to them for CRUD (Create, Read, Update, Delete) operations, but that's about it - views, procedures, tables, all the objects are restricted and cannot be modified (as they must conform to their requirements to host the Web App).

With that out of the way, here's how to get started with Azure as a cloud-version of SQL Server rather than part of a Web App that you happen to be able to connect to:

- Don't try this on Windows XP
- Sign up for Azure
- Create the database, get your connection string from the Azure Mgmt portal
- Install SSMS on your computer (2012 version or newer)
- Connect to the Azure DB and you can start creating objects almost identically to how you would in on-premise (eg, using SSMS)
- Get the Native Client 11.0 Driver for your Access application
- Setup a file based DSN* in Access
- Connect via DSN file
- Convert connection via DSN-Less Connections (google it, by Doug Steele)
- Make good use of passthrough queries and capitalize on server-side processing
- Avoid heterogeneous SQL from Access (eg, don't write queries that require all the Azure data being brought to Access via ODBC, this is slower than death, way faster to import a temp cache to a local table and process it Access like that, or send the required data to Azure and let your sprocs process them).

In a nutshell, that's it - not much different than a normal SQL Server BE with an Access FE.

*DSN: the only tricky part here is the server name
SERVER=tcp:gibberishservernamefromazure.database.windows.net,1433


All that said, Azure is indeed a subset of SQL Server and has some limitations.  You'll find that the greater majority of these limitations is on the Administration end of things, and not so much on the data-processing end of things.  We don't have access to physical storage administration, various import/export utilities (some, but not all), stuff like that.  Data replication, etc.  Although, they are making some significant changes early next year (currently in preview) that address many of these administrative type shortcomings.

For the most part, for our purposes as database application developers in need of a cloud based BE, Azure is a great option.  I've had much success with it.

You may find a lot of helpful information from MVP Alumni Patrick Wood at http://gainingaccess.net - he's written a fair amount on the subject.  I know of few enough other places to find info... after the initial setup per above, you'll basically be using SQL Server practices rather than Azure specific practices, which of course are widespread.

hope that helps...
0

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

During and after that shift to cloud, one area that still poses a struggle for many organizations is what to do with their department file shares.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question