Solved

Access 365 Desktop App Pointing to Azure DB

Posted on 2014-07-18
1
831 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
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Synchronize a new Active Directory domain with an existing Office 365 tenant
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now