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.