Go Premium for a chance to win a PS4. Enter to Win


Access 365 Desktop App Pointing to Azure DB

Posted on 2014-07-18
Medium Priority
Last Modified: 2014-11-12
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
Question by:LJG
1 Comment

Accepted Solution

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

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...

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

916 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