Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 365 Desktop App Pointing to Azure DB

Posted on 2014-07-18
1
Medium Priority
?
896 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 2000 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

Google Certified Professional - Cloud Architect

This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.

Question has a verified solution.

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

There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
Considering cloud tradeoffs and determining the right mix for your organization.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

670 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