IMPORTANT UPDATE
Before going any further, please quickly read: Steer clear of Access Web Applications (AWA)
The following article explains How-to ‘Hybridize’ your MS Access Database In Office 365 Azure Database or How-to use an Office 365 Back-End database with your MS Access Desktop Front-End (FE). It is meant to be a step-by-step instructional guide of how-to to convert a standard split desktop database so that the data, the back-end (BE), is imported and available through an Office 365 account. So the data thus becomes available to anyone with an Internet connection, but the application itself, the front-end (FE), remains on each user’s local computer.
A few people have now asked me ‘Why such an article?’ If you are here, you already know the reason! Simply put, Microsoft has, once again, not provided a simple step by step explanation of how to accomplish this task. I was banging my head trying to get a simple ODBC connection functional and couldn’t locate instructions that actually worked anywhere. So once I finally got everything working smoothly, I decided I’d share with the rest of the world to save the days of frustrations I faced! Microsoft’s support (whether it be online documentation, Knowledge base, forums), more and more, simply disgusts me. This learning experience allowed me to learn that there is a flaw (it does not work! just a small flaw, lol!) in the Windows ODBC Data Source Administrator when creating File DSN to Office 365 SQL Azure Databases (that has been confirmed now by fellow MVPs), which I provide a work around to below. No where have I found this mentioned anywhere. This cost me 6 days of development and countless frustrations. Hopefully Microsoft will address this, eventually. I can only imagine the number of people faced with the same issue with no solution. But if you are here, you now have a solution!
Another question asked is ‘why ‘Hybridize’ a database when you could simply create a Web Database?’ Simple, VBA! Need I say more! Web Databases are primitive versions of MS Access databases and you cannot use any VBA whatsoever. A hybrid database bypasses this limitation while making the data available over a WAN (in the case of Office 365 – The Internet/World). Sort of the best of both worlds; get around Access’ native limitation of not being available over a WAN while bypassing Access’ Web Databases’ limitation of not allowing VBA.
Also, for existing databases, converting them into Web Databases can be involve a tremendous amount of work, to the point of being inconceivable altogether. By Hybridizing them, you can greatly benefit from this new technology while requiring very little changes to your standard database.
So what is involved?
Let’s get going.
First, before doing anything whatsoever, PLEASE make a backup copy of the database you are wanting to ‘hybridize’, before going ahead with any of the following instructions! This way should anything go wrong you can at any time revert back to the original copy. Never work directly with your good copy of your database!
Below are the step-by-step instructions as of 2014/01/07.
Office 365 Setup
Obviously, you need to open an account.
Login to O365 account.
Once logged in, click on Sites
Click on Team Site
Click on Site Contents
Click on Add an app
In the search control, perform a search for ‘Access’
Click on Access App
A Adding an Access app dialog will pop-up. Enter a name and then click on OK (will now appear in your list of assets, allow a couple minute for Office365 to configure things for use.). You should, after a moment, see something similar to the following where your newly created Access app is flagged as New.
Click on the newly created App.
Once the App loads, Click on Settings (the Gear in the top right-hand corner)
Click on Customize in Acces
Click Open (the pop-up at the bottom of the screen – Access should then launch on its’ own)
At the bottom of the Access screen, Click on Access (assuming your be is an Access database) from the Create a table from an existing data source menu
Simply follow the prompt to select your existing database back-end to pull the tables from to import into Office 365 SQL Azure Database
Now that you have successfully uploaded your database tables into your Office 365 SQL Azure database, you have to enable remote connections and take note of the DSN setting to create said connection. To do so:
Goto the File tab and click on Manage (under the Connections)
The following menu/dialog will appear. Click on From Any Location, as well as, Enable Read-Write Connection.
Click on click on View Read-Write Connection Information.
The following dialog will appear. Be sure to copy the information verbatim as it will be required to create a File DSN (or a DSN-less connection).
Close the dialog and then close Access. You can now log off of Office 365.
SQL Server Driver Native Client
If you do not already have the SQL Server Native Client 11.0 install you need to get a copy and install it.
Goto http://www.microsoft.com/en-ca/download/details.aspx?id=29065
Click on the Download button.
On the following page, click on the Install Instruction link which will expand a listing of the various files that you can download. Scroll down until you find the Microsoft® SQL Server® 2012 Native Client (about midway down).
If you are running on a 32bit OS, then download and install the X86 Package (sqlincli.msi). If on the other hand you are running on a 64bit OS, then download and install the X64Package (sqlincli.msi).
Create a File DSN
Note: If you are going to create a DSN-less connection (which is always a good idea) you can step over this step). That said, this is still the easiest method for beginners to get things up and running.
I’m not going to go into the details, just let me say that do not try to create a File DSN using the Windows ODBC Data Source Administrator utility as it seems to be very unreliable. Instead, simply create a new blank text document and give it whatever name you wish with a dsn file extension.
Then open the newly created text file and enter the following (replacing the appropriate value with those you retrieved from the MS Access ‘View Read-Write Connection Information’. Pay particular attention to the UID (username/user id as it includes the server!) and the SERVER as it is prefixed by ‘tcp:’.
[ODBC]
DRIVER=SQL Server Native Client 11.0
UID= db_1446956c_xxx_xxxx_xxxx_xxxxxxxxxx_ExternalWriter@ kcxxxxxxxxfx
Pwd=jsxxxxxxxxxxxxxxFu
Encrypt=yes
DATABASE= db_8332e578_xxx_xxxx_xxxx_xxxxxxxxxx
SERVER=tcp:kcxxxxxxxxfx.database.windows.net
Save and close the file.
MS Access Setup
Launch your MS Access Database front-end (FE).
Goto the External Data tab and then click on the ODBC Database from the Import & Link group
Click on the Link to the data source by creating a linked table and the click on OK
Go select the File DSN from wherever you saved it and then click on OK
Select the table(s) you wish to create linked table to and then click OK
You are done. Your front-end should now have read-write access to the Azure database on Office 365!
Final Conclusion(s)
At the end of the day, the hybrid database fills a need. It has to be noted that performance does take a hit when compared to local database or LAN databases. That said, performance tuning can greatly help with overall performance (like with any database) and aspects such as your Internet Connection can also greatly impact your ability to work smoothly.
Daniel – I am enormously grateful to you for this article. I am a user of Access (not really a professional) who has developed and supported a database for a community organisation through several versions of software, OS’s, hardware and interfaces since the year 1999. It’s quite complex (I think) using a fair bit of vba code to do fancy things with membership invoices, anniversaries in a foreign calendar, relationships etc. Currently we’re using Access 2013 and I was dreading the (inevitable and desirable) move to the cloud having read so many warnings of the difficulties of marrying Access applications with vba code to Office 365. In fact I have been putting this off for about 2 years (under pressure from our office staff who want to use Office 365 to share documents and work from different sites). Microsoft seem to have gone out of their way to make this job difficult – but it’s obviously not impossible, and you have shown the way! Absolutely brilliant!
I followed your guide, and have successfully copied my backend tables to an Office365 site and connected them to my existing frontend application using a DSN file. Yippee! Was a bit confused at first, because my frontend application is still using the original linked tables, whilst also having established additional connections to the ODBC tables, which have different names (beginning with ‘Access_’). So I shall need to modify the names, or establish some sort of bridge between these tables. I hope this won’t be too difficult…
You shouldn’t have duplicate linked tables (original and office365). I’d delete any still pointing to your original network. You should only have those using the office365. The last thing you want is some data being updated in one system and some updating in the other.
Hi Daniel,
Thank you so much for this article. It’s finally give me what i need. I’m interested to know the DSN less connection. Could you please add the DSN less connecton step for the same example.
In the Create a File DSN section of this article I did provid a link to Doug’s article on the subject, see: Using DSN-Less Connections. I hope this help.
Hi Daniel – Now that I have my Access front-end linked successfully to an Azure SQL database – created via an Office 365 Access app in accordance with your fine advice – what should I do to preserve it in the light of Microsoft’s impending abandonment of Access Web Apps (AWA)? I am not actually using AWA, other than having used an Office 365 Access App as a means of setting-up the back-end database. But I may want to repeat the process in the future. Presumably there will come a time soon when this will no longer be possible?
I like the hybrid model. Should I move the Azure SQL database to some other SQL server? Or is there a way that I can take over administration of my Azure SQL database without using the sadly obsolescent means of AWA?
You will have to look at migrating your backend. You can get some pointers on that by reviewing Export\Salvage Your AWA Data. Azure would probably be my first choice as SharePoint is not a relational database by any means! The issue with Azure is cost.
That all said, when I made my post Steer clear of Access Web Applications (AWA), I was very serious, I personally think it is time to completely abandon Microsoft when it comes to data and the Web. It is time to turn to time tested alternatives from companies that have a vision and sick with them. I’m not going to get burnt again like this.
IMHO, Microsoft just doesn’t listen, or appear to even care, proof they’ve even closed the comment section on there announcement page after 2 weeks. Great way to support your users, just don’t allow them to say anything. Is this really a company you want to trust any further? And yes, I know I’m being harsh here, but at some point history has to act as a teacher and with Data Access Pages, Access Web Databases, and now Access Web Apps, I think it is time to learn our lesson and move on.
Thank you for an excellent step by step instructions. I did have a problem on the last step with the DSN file when running from the Access client, got the message “ODBC Call Failed. Driver keyword syntax error (#0)” and it wouldn’t run. Set the DSN exactly as instructed so not sure what’s gone wrong.
Regards, David UK
found a link that sorted this out, may of use to others.
http://www.utteraccess.com/forum/lofiversion/index.php/t2032822.html
Hi Daniel,
I was looking how to make this, but in June of this year when i tried to do it this message came to my hand:
https://support.office.com/en-us/article/Access-Services-in-SharePoint-Roadmap-497fd86b-e982-43c4-8318-81e6d3e711e8
This guide is still valid despite the article mentioned?
Regards!!
Microsoft is killing AWAs. So if your intention was to create an AWA, then you’re out of luck.
If on the other hand, you have an Azure db and want to host your tables there and use a hybrid db to use them, then you should still be able to do so.
Thanks very much Daniel for a brilliant article. I haven’t as yet implemented it but I shall do shortly, would you please explain how the users of my application will use the data tables. Would they have to have an Office 365 and login every time they want to use my access program (Access 2010) or will they automatically connect?
Regards.
I wouldn’t go down this road anymore since AWAs are being terminated with prejudice by Microsoft, so the Back-End technology will soon cease to function.
If, on the other hand, you are looking into doing a similar setup with SQL Azure, then yes, some developers have done so with success. To answer your question, every user needs a copy of Access or Access runtime to being to run the front-end database.
Daniel,
You are amazing for taking the time to create such a fine tutorial. As you have pointed out, Microsoft seems to not care about the multitudes of people that invested and believed in them. Like most things, this is clearly about the money. Having said that, I am trying to help a small nonprofit get a database up and running on the cloud and the only program I know how to use is Access. While I could learn, I really do not have the time. I would like to point out a positive here for small nonprofits which is that Microsoft does give nonprofits free or reduced price software. This gives Microsoft huge tax deductions at no real cost to them and helps the nonprofits at the same time so while it is not completely out of the good of their hearts, it is still a great benefit to small nonprofits. One of the really nice benefits is a credit to use azure essentially free along with microsoft office. With this information would you suggest that I go this route? I am open to other ideas if you have one. Thank you so much for this help, I have been searching everywhere since I discovered that Microsoft pulled the rug out from under its own sheeple…
The new option is that you can host the back-end (data) on Azure and use a standard desktop database in a hybrid setup. The issue here is of course performance. You need to develop your app while always minimizing all the data being pulled/pushed across the internet.
Personally, I wouldn’t develop such a solution. This leaves you at the mercy of Microsoft. They can change the prices, cancel the service … at their whim.
I would recommend a proper web application if the cloud is truly necessary. Why do they need the cloud?! Why not a standard desktop database?
Exactly what I’ve been looking for.
Haven’t tried to implement yet.
Grateful you took the time to help.
I’m looking into creating a project management database for a friend of mine who runs a small business. Multiple users in several locations would need concurrent access to this database.
I have experience in Access and some VBA, so I know I could create a database that does what she needs using MS Access. If Office 365 is no longer a sustainable place to host the back-end, what do you suggest as an alternative? I don’t understand how a remote desktop would work for multiple users at once.
Look into using Azure to host the back-end and use a standard Access front-end.
I worked for a public school system and one of their departments needed to use their local Access db across different locations, some of which were outside the school system’s network. I googled a solution and came across Daniel’s tutorial…brilliant! I even set up a mock trial run with 9 users all running the hybrid db setup at the same time and it was a success! And then…Microsoft’s announcement that they were killing AWAs…bummer, but thankfully I hadn’t deployed the hybrid db yet. Make a long story short, I got laid-off this past summer, but last I heard, the department went with a ‘customized’ student registration DB costing thousands of bucks, and as of this writing, the department has to use both the ‘customized’ db AND the local Access db at the same time because the ‘customized’ folks over-promised what their db could do. If only Microsoft had kept Office 365 as a host of these DBs at a reasonable price, I’m sure my former department and others would have given MS their business. Hey, Microsoft’s loss…
Do note, that although Web Apps, Web Databases, … are dead, Hybrid databases live on with SQL Azure.
Thanks for your response, Daniel! I have a feeling that, as part of their agreement with Microsoft, the school system already has Azure (if not, easy to add to the agreement). I’ll pass the info along to my former coworkers. Thanks again!
This is an excellent article. Thanks a lot for sharing.