Share via

Web service and sample book database that uses Amazon's Web Services

  • Article

Today’s guest writer is Access Program Manager Ric Lewis. Ric spent the last couple years with the Indigo team and just took an Access PM job. He is looking for some customer feedback on interesting web services scenarios. To start the conversation—he created an interesting and useful sample book database that uses the Amazon web service.

Clint

Media Players

For a few years now, there has been a slowly developing convergence of databases and Web Services. Perhaps one of the best examples of this is your standard media player.

A media player is a specialized case of asset management (with the assets in this case being digital data). Of course, the biggest barrier to tracking assets in a database is manually entering the information. Remember the first time you ripped a CD and had to manually enter all the album and track info? Now we take for granted that when we rip a CD, our media player will call the Gracenote (or some other) CDDB web service and automatically fill in all the information for us.

CDDB web service request.

Nowadays, most media players will even go the extra mile of calling Amazon or Wal-mart to grab a high quality cover-art pic.

A media player is a great example of a widely used database-as-asset manager. And without you really noticing it, over the last few years, between calls to CDDBs and online-music stores, your media player has increasingly become a web application.

Databases and Web Services

Web services are all about distributing information, and databases are all about organizing and visualizing that data. Internally, we talk about Access databases being a landing pad for data. Using ODBC Access databases connect in rich ways to a number of different data sources.

Access 2007 Sample Books Database

Until then, I’ve thrown together a hack-ish example of a book-asset management system in Access 2007 which calls through the Amazon web service to collect book information when you type in an ISBN number.

Books Details form.

Installation instructions:

- Download the zip file file (right click on zip and choose Save Target As).

- Unzip it.

- Run setup.exe (install for “Just Me”)

- Copy the msaccess.exe.config file into \Program Files\Office12

- Open the Books.accdb file

You’ll also want to make sure you’ve got macros fully enabled in Access. Then just hit “New Book” (wait a few seconds if it’s the first time), type in an ISBN (ISBN-10 only, AmazonWS doesn’t like ISBN-13), tab out of the ISBN field, and watch your data get filled in!

In a later post, I’ll talk a bit more about the behind-the-scenes of how this app was developed, and share out the Visual Studio project for the add-in. Download it, play with it, break it (that shouldn’t be too hard) and let your brain start spinning up about the possibilities.

The program should “just work” on Vista, but on WinXP you’d certainly need WCF installed, which is included in the .NET 3.0 runtime.

Moving Forward

As part of investigating many areas for future work we are researching Web Services. The best way is to always start with identifying useful customer scenarios. What scenarios would you find interesting connecting to web data?

Comments

  • Anonymous
    February 20, 2007
    I'll bite on this one.  I'm new at this remoting businesss, so please bear with me: I need two types of remoting: A hands-off web services (SOAP) type for editing one record at a time, and also a binary type for downloading  the results of huge queries.  XML-based SOAP protocols are just too bulky for the latter scenario. For the XML scenario, I need the ability to specify a specific custom schema, and get my data into/out-of that XML format and into/out-of an Access form automatically.  I need some robust object models to support that task programmatically. For the binary format, I would go with serialized binary ADO objects of some sort, with the big assumption that nearly everyone can support ADO or ADO.NET Just a few random thoughts.  I hope they make sense.

  • Anonymous
    February 20, 2007
    Thanks AL, You have described what you want from a technical perspective--can you give me an idea what you are trying to achieve from a business scenario perspective. For example, what system has XML that you would like to consume? We can figure out the technical details provided we have a clear understanding of the what kind of business data you are trying to remote and why you are remoting it.

  • Anonymous
    February 21, 2007
    The comment has been removed

  • Anonymous
    February 21, 2007
    Using a map service such as Map Quest or Google Map would be useful.   Integrating with AP News Web service would be useful also.  This would allow users to gather information (source, date, time, and title) based on a keyword search.   Stock Quotes web service integration would allow for someone to gather data so their own analysis can be performed. What would be really cool:  If from within a custom Access application a web service could be easily created that would provide an interface to the data.  This web service could be used by the developer to interface between a custom internal application and an Access backend file hosted on a web server.  This would allow for better integration between internal applications and external web applications.

  • Anonymous
    February 21, 2007
    It's good to see this level of response.  It sounds like you folks have been thinking of this well before I brought it up. If you'll forgive me, I'm going to proceed to milk you folks for all the info I can gather here in the comments.  So let's see if I can address specific comments so far. AL:  For your first scenario, do I understand correctly that you're looking for read/write record functionality from an Access record mapped to a web service?  Are you looking at large public web services (Amazon, eBay, etc) or are you thinking more about private, B2B services? Fred Boer:  Glad to hear we've got you excited :)  I agree that I'd love to see web service access enabled not only for the power, but even for the average Access user, and utilizing Amazon's E-Commerce Web Service is a key scenario to enable. Matt:  We've had a fair amount of internal discussion about integrating maps.  How would you be using the map?  Is it "map the data in this table to points on a map image"?  Would you expect the map to visible in your Access application, and would you expect a static map (Map Quest) or a dynamic map (Google, Live maps)?  The stock quote scenario is a great scenario. Matt brings up a great scenario about exposing Access application data through a web service.  I think the place to do that might be through sharepoint.  I could see a world where you could publish an Access app to Sharepoint, and then call sharepoint's SOAP service to query out of that data. Alright folks, this is getting fun.  Keep 'em coming.

  • Anonymous
    February 21, 2007
    Another way of asking the question... What data in web sites would you like to include in your Access application? IOW - without thinking about it in terms of APIs--what web site are interesting to your Access application? What are the scenarios you would find interesting?

  • Anonymous
    February 21, 2007
    BTW, Fred, I just checked out your library app on your website.  Nice app.  That's exactly the kind of application for which we'd love to enable Web Services. How are you currently handling parsing of the Z39.50 data?

  • Anonymous
    February 21, 2007
    Thanks for the kind words. Hope what follows isn't too verbose! :) It's not too hard to parse MARC records into their "tag fields" - the record itself has a "leader" which contains the information needed to extract the tag fields. Some simple VBA is all you need for that. BUT... that only gets you "tag fields", and doesn't provide access to sub-fields. Parsing data out of subfields (to me at least), is more of a challenge. I use VBZoom as my Z39.50 client, and it works well. It has a GetFields() method to pull out subfields. Even that isn't too hard (if I can do it anyone can! <g>). Repeating fields (like multiple subject headings) exist; I've used arrays and loops to manage them. Subfields are trickier, since a subfield sometimes holds more than one piece of data, and they vary from record to record in how the data has been structured - (eg. "c1988" or "1988" for copyright year) - I've tried using string functions with some success. I've been told that using XML is effective, but I have no skills in working with XML. Regular expressions would also be effective, I think, but again, I haven't taught myself Regex... yet.. :) The demo Z39.50 mdb shows what I've managed to this point. I can go on... (no one I know is remotely interested in this subject, so it's fun to talk about it! <G>), but I perhaps that is enough for a start... Cheers! Fred

  • Anonymous
    February 21, 2007
    Ric In regards to maps, what I envision is being able to obtain a map (static) and directions for a customer based on their address information stored in the database record.  The map and directions could then be emailed or printed for the customer.   In regards to using Access with SharePoint, my question is if there is (or will be) the ability to query the data from an application that is hosted by a third party hosting company.  Many of my small business clients have their website hosted externally by a hosting company for convenience, security, and cost benefit. Another beneficial web service would be payment processing using a web based processing service.

  • Anonymous
    February 21, 2007
    Matt--I don't know if I fully understand your question about Access+Sharepoint yet.  Are your small business clients hosting their websites using Sharepoint at the third party location, or some other mechanism?  I'll take a shot here, and you tell me how close I'm getting. If the third party is hosting the SMB's website using Sharepoint, I'd like to see you be able to query your Access data (that you've published to / hosted in Sharepoint) using Sharepoint's SOAP endpoints. If the third party is hosting the SMB's website, and just exposes the .accdb (or MDB?) file on the site, I don't forsee that we'd be able to expose the data through a WS--you'd probably be stuck using a local Access client at that point. BTW--as of this morning, the Access+Web Services investigation team started watching this blog post (you thought I worked alone?).  We like scenarios.  :)

  • Anonymous
    February 21, 2007
    Hi Ric - you a right on the direction I am trying to go with the Access/ Sharepoint services. Currently my clients are using third party hosting that is not SharePoint.  But I would definitely move them to a SharePoint hosting platform if I could interface the local internal Access application with the web based access backend more directly. Example - Database Search: http://www.acecny.org/memberdirectory.htm (Example search: City – Albany) This website has an MS Access backend file that is created though an export function from an internal custom Access application.  I update the web backend database by exporting the information into an mdb file and using FTP to transfer it to the website.  Some issues with this method are:    - Occasionally this hangs the ASP application pool on the web server which requires a call to the web-site hosting company.    - All records are uploaded, even if there is only a change to 1 record. I could create an ASP file to post the updated/new record that would then update/append to the web mdb file.   For several months I have been working on a POS application for a client who provides classroom and online educational courses. The application is an MS Access front-end, SQL server backend and integrates with an internal SharePoint sever, internal asp website, and an external asp website for customers/students.  Some of the web functionality I built into the internal Access front-end is credit card processing to a web payment service, searching Department of State website to verify license information, automatic email notifications to students, and updating course information on the external website (access db).  My biggest challenge is updating the website backend to be more ‘real-time’ with the information from the internal application.  For example: removing the class from the website when it is full.

  • Anonymous
    February 21, 2007
    Hi Clint, thanks for this good sample. How about Map services like yahoo, google, mapquest...maps/direction/distances...or even MS MapPoint integration ? I definitely can see stock tracking (yahoo finance?) as well as weather feeds, too. Another important application coming to mind would be exchange rates. It is impossible to work with static internal rates when they constantly change. For personal interest...how about NBA/NCAA/NFL...feeds...especially good for fantasy sports. Would make it a lot easier to track the Wizards season sweep over the Jazz ;)

  • Anonymous
    February 21, 2007
    The comment has been removed

  • Anonymous
    February 21, 2007
    The comment has been removed

  • Anonymous
    February 22, 2007
    The comment has been removed

  • Anonymous
    February 22, 2007
    Another option coming to mind would be to retrieve results from other possible web searches e.g. switchboard.com, anywho.com, superpages.com...maybe even from myspace, facebook, secondlife...This would be great to gather information about people. I can see many usage scenarios for this (store personal information e.g. addresses of customers from a web lookup, reverse lookups, data clarification...). Oli

  • Anonymous
    February 22, 2007
    Hi Clint, Thanks for the sample database, it will be interesting to analyze and learn from. As for scenarios, one thing my mother is always looking up is stock prices. She has a little program to monitor certain stocks and I created an Excel spreadsheet with calculations to help her with analysis -- but she needs to copy-n-paste the information in --- would be nice to be able to grab the data directly. In the Oil and Gas industry, things like Pressures, Temperatures, and Volumes are always monitored -- graphing helps tremendously to spot areas that need attention. Data collectors or direct interfaces with monitoring equipment are used to transfer information to the computer and Excel is a great tool for plotting (I guess Access would be ok too <g> but I always use Excel for these types of things so the files can be saved individually -- they tend to get quite big); it would be nice for many if you didn't have to be a programmer to do it <smile> Well, I guess that example is not necessarily using web-services, but real-time, or close to it, can be critical. A web service used alot in O&G is Dwight's ... weather, gold/silver prices, real estate, finding out what is playing on Streaming Internet...maybe even a way to be notified when specific songs come on (!) ... and the list could go on ... Warm Regards, Crystal Microsoft Access MVP 2007 strive4peace2006 at yahoo dot com

  • Anonymous
    February 22, 2007
    I develop desktop Access based applications. I want to use write my own web services (probably in VB.NET) to serve my Access applications for two purposes:

  1. Activation type licensing (ie accessing a central web based license server).
  2. Upgrade management (check whether they have the current version, and prompt for upgrade if available). I believe I will be able to do this with available technology... but I haven't delved too deeply into it yet (finish the app first!). One thing in particular I am wondering about is asynchronous processing for the version check - it would be nice if I could start the upgrade check on startup, but not have to wait for the return information before starting up the app. When the server response is available, I would like to be notified by an event. Cheers, Chris Bartley