I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications?
Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my users or developers might choose.
I will be testing how a power user can use MS Access to manipulate data within a Azure database.
I am assuming that you read my article on creating a ODBC connection. The only change is the source of the database. Use the “npx8eftmd0.database.windows.net” server address as the source. Before it was local, now it is in the cloud.
You will also need to create Access Linked Tables using my article as a reference. Your database should look like mine below with two linked tables and one linked view.
Performing a CRUD – (create, read, update & delete) test will make sure Azure performs like any local database we worked with before.
First task is to create or INSERT a new record for the Plymouth make.
Second task is to incorrectly UPDATE the Ford maker to Henry Ford.
Third task is to correctly UPDATE the Ford maker record back to its original state.
The fourth and last task is to DELETE the new Plymouth record from the table.
Since MS Access automatically updates the data grid after a change, we do not need to test the read or SELECT functionality.
In summary, connecting a MS Access database requires the creation of a ODBC data source using the newest client driver and the creation of linked tables to access/manipulate the remote tables. Next time I talk about Azure, we will be exploring how to pull data from Azure into MS Excel.