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


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 (22)

  1. AL says:

    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.

  2. Clint Covington says:

    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.

  3. Fred Boer says:

    Hello:

    Not sure if I can add anything valuable, but…

    I am an amateur "hobbyist" developer, and I’ve created a freeware small library application (www3.sympatico.ca/lornarourke/fred.html), and have created a demo (www3.sympatico.ca/lornarourke/fred1.html) of using Z39.50 to download cataloguing information from Z39.50 servers.

    This demo looks intriguing. Most professional library programs enable access to Amazon data – it would be great if this type of service could be done easily from within Access, opening up this resource for the broad range of Access users. It would be so cool for Access power users – and not just professional software developers – to be able to create something like this

    Now, I just have to wait until I can get my hands on a copy of Access 2007 –

    but my birthday is coming up, and I’ve been really sucking up to my family lately. 😉

    Cheers!

    Fred Boer

  4. Matt Carmichael says:

    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.

  5. Ric Lewis says:

    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.

  6. Clint Covington says:

    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?

  7. Ric Lewis says:

    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?

  8. Fred Boer says:

    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

    +

  9. Matt Carmichael says:

    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.

  10. Ric Lewis says:

    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.  🙂

  11. Matt Carmichael says:

    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.

  12. 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 😉

  13. Clint Covington says:

    Oli,

    You crack me up–especially after a tough loss against <gulp> Portland last night. We will catch you in March in Salt Lake.

  14. AL says:

    Hi Clint and Ric,

    To clarify a bit:  I’m a biomedical researcher/programmer involved in the  National Cancer Institute’s "caBIG" project.  I’m also an Access veteran/expert since Access 1.0.

    The project is way too big to describe here, but you can Google it.  I am looking for ways to connect my legacy biomedical informatics apps (written as Access ADPs using SQL Server) with the caBIG "Grid."  The grid relies heavily on standardized SOAP web services and remoting methods via Java (yuck) APIs.  I am looking to connect my apps to participate in the grid.  If successful, I hope to distribute these Access based tools, perhaps for free, as part of our project to promote interoperable tools using federated data stores for cancer research.

    The actual data is a huge model-driven, metadata-driven mammoth collection for a large number of biomedical uses.  The internal federated data models are quite varied and are often different from the client data models, but the metadata-driven data object construction makes everything work.

    So there are two main areas I am interested in: 2-way SOAP web services for routine record by record read/write data,  and a remoting interface, preferably one with a native Java interface/proxy layer for VBA (and .NET).  The remoting part is needed for the large images and large data dumps for research.  Since it’s all still in development, I’m not at all sure that SOAP will scale well enough or be fast enough for the big stuff.  

    I’m new at this WS/SOAP and remoting stuff, so I hope I’ve explained it correctly.  You can find more info at the caBIG website and wikis.

  15. Al reminded me of another possible scenarios which I use frequently at work (NIH). It would be great to get data from sites like PupMed (www.pubmed.gov) or WebMD (www.webmd.com). It would also be great to temporarily get search results into the application without using the webbrowser control and exposing the user to the actual web interface. I have a primitive sample of this using Google.com here:

    http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1135275&Zf=f48&Zw=&Zg=0&Zl=a&Main=1135275&Search=true&where=&Zu=64822&Zd=l&Zn=&Zt=b&Zs=b&Zy=#Post1135275&Zp=

    Clint…see you in the finals even though the Jazz have it easy in the West going possibly only against teams like Phoenix, Dallas, San Antonio… <g> 😉

  16. 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

  17. Crystal says:

    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

  18. Chris Bartley says:

    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

  19. Gary Walter says:

    How about an interface to

    http://support.microsoft.com/dllhelp/

    I have a table of dll files and would like to automatically get info

    name (of software that provides the version)

    file version

    file size

    file date

    for every version of each of the dll’s in the table.

    I have not visited this site for some time and maybe they have

    quit updating it to the point that above would be worthless…

    I don’t know… just something I thought about when I read this.

    Fred, I have only just downloaded your dbs, but it is something

    I am extremely interested in following. Thanks!

    gary

    ex-Access MVP

  20. Today’s guest writer is Ric Lewis. Several weeks ago he started a general discussion about web services