My First Access Services Project

Access Services is Microsoft's answer to running MS Access applications on the web, in Sharepoint. The obvious advantage is that a user with login credentials can access their data from any web browser. I have completed and published my first MS Access Services project. It has been tested on an iphone/ipad and it runs just like on a PC (not quite as much fun on an iphone because of the screen size but great to have the option in a pinch). I don't know about you but I can think of lots of business needs that this might fill. Microsoft Access is the most widely used desktop database system in the world. Sharepoint is the perfect intranet environment and adding Access Services extends that usefulness.

Challenges that had to be overcome:

1) Office 2010 was the first release of Access Services so finding a lot of good examples and forum support is a challenge. A Google search on any MS Access features or questions returns beaucoup of resources - such is not yet the case with Access Services. Microsoft recommends http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html and I have found others like http://accessexperts.net/blog/category/access-web-database, http://channel9.msdn.com and http://dmoffat.wordpress.com. Though there are many that try to offer support, most prefice their comments with "I haven't tried this yet, but ..." It's a start, we are all learning and any effort these non-paid folks make to help should definately be appreciated. I've found posts by Albert Kallal are very comprehensive and informative.

2) I heard a lot of buzz about the fact that you must either have Sharepoint hosted in house of pay for the hosting service. Some complained that it was too expensive. In my opinion, this is not an issue. Any serious business application on the web must be securely hosted somewhere and Sharepoint offers that security and flexibility at a reasonable price for businesses. For $49/month accesshosting.com offers 5 users and handles our needs. That is not concurrent users, concurrent users aren't limited. That 5 users is user logins. So I configured administrator, contributor and viewer users with 3 user logins to spare. Accesshosting includes a daily backup in the hosting price but will only restore back one day. This is not an adequate backup/restore flexibility for most businesses but you can upgrade backup/restore options or plan your own backup.

2) Optimizing the design for speed. As we are running this in a web browser with multiple and varied internet speeds, making it as efficient as possible is important to it being a useable tool. My project contained about 14,000 records in the largest table and initially was too slow to load. The solution was to open the main page with a subset of the data (A-C for example) and offer the user a box to search for the record(s) they want and then query the next small subset of data. Doing this in all forms made a huge different in speed.

3) Offering multiple report formats and filtering options. I have 10 reports including formatted labels, each report/label has 3 or more filtering options. I host with Accesshosting.com and they include SQL Reporting Services in the hosting. In this way, my users are able to generate their reports and print or export in multiple formats including PDF, Excel, CSV and more. Does the trick.

I'm excited about the results and looking for my next project!

MS Access 2010 - Sharepoint Hosting

I have taken the giant step of upgradng to Microsoft Office 2010 and I love it. I did not upgrade to 2007 because I chose to wait for the second iteration of this major upgrade. For MS Access 2010, the major improvement and, in my opinion, move in the right direction, is the new Access Services that makes it possible to develop an application totally for the web and hosted on a MS Sharepoint server. Data is moving to the web, we know it and if we are smart, we will get ready to support it.

Here's how it works: In Access the developer must select "Web Database" option when starting the project. All forms, reports, Queries and events are developed with web compatibility. New macro functionality has been added to extend the capabilities. Access 2010 allows forms and reports to be published to web sites using what is called "access web services" that runs on Sharepoint software. These web based forms and reports run in any standard browser. Before publishing your project to the Sharepoint server, it is checked for compatibility and errors reported.

Many have voiced the concern that having to run in Sharepoint creates an expense and complication that will impede this usefulness. Honestly, I had this concern at first but here's what I decided: Any URL will have to be hosted somewhere and security, user-access, multi-user conflicts in tables, etc., will need to be handled somehow. Sharepoint does it effortlessly to the developer and there are many reasonably-priced hosting services out there. You can find 30-day free trials to host and demo sites to customers if needed as well. I have used www.accesshosting.com for such a demo and been happy.