How to pull in Amazon web service data into Access 2007

Article
03/13/2007

Today’s guest writer is Ric Lewis. Several weeks ago he started a general discussion about web services which turned out to be a popular post. This week he is providing the details of the books sample database.

It would be sweet if someone takes this project and details it out a bit further. For example, you could imagine using the title to search for titles and popping up a list of search results to choose from. The ISBN number is a bit awkward. If you do make improvements to the project—send me an email and I will share it with the community.

Clint

Amazon Web Service Code Sample

As a note before you dig in, the code presented in this post consists of excerpts from the full project code. In other words, if you’re going to copy and paste my stuff, do it from the project, not from this post. You can grab the code for this project from here: Code Project.

For those of you who want to compile and run the plugin, here are the steps:

You can grab the code for this project from here: Code Project.
Get a new Amazon Web Service key from the Amazon Developer Center.
Replace the “############” in the DataCollector.cs code file with your new AmazonWS key (the code won't compile until you complete this step).
Build the AmazonDemoAddinSetup project.
Run the setup.exe file generated (this will install the plugin so Access can see it).
Visual Studio will generate an AmazonDemoAddIn.dll.config file in the build directory. Copy this file “\program files\microsoft office\office12” and rename it “msaccess.exe.config”

Also, this post has no pictures. Not one. So if you like pictures, look elsewhere, if you like code snippets, venture on. Okay, with that out of the way, let’s talk about this application.

Form Load

I’m all about flow-of-control discussions, so let’s trek through the app in that manner.

When you load up Access, it determines which, if any, add-ins have been installed and should be loaded. Thereafter you can reference those add-ins by name from your form’s VBA code.

Load up the Book database (from the previous post or the code project above), and you can click on the “New Book” button. This triggers a call to the Form_Load event handler in VBA code. There’s not too much VBA, so let’s step you through the code:

This first bit of code opens the form, checking first to see if it’s a new form, or an existing record.

Private Sub Form_Load()

If (Not IsNull(Me.OpenArgs)) Then

DoCmd.GoToRecord , "", acNewRec

Me.Title = Me.OpenArgs

End If

If this is a new form, or there’s just no name, then set a title for the book. This form requires a title to be set, so it’s nice to get this out of the way upfront.

If (IsNull(Me.Title)) Then

Me.Title = "Untitled"

End If

This just places the cursor in the ISBN field. You probably knew that.

Me.ISBN.SetFocus

This last bit of code is the interesting part. It connects to my addin (AmazonDemoAddIn) and calls the PrepareObject function of my add-in. We won’t go into this function too much, but it warms up the web proxy that we’ll be using later.

With COMAddIns("AmazonDemoAddIn.Connect")

' make sure the COM add-in is loaded

.Connect = True

'warm up the web proxy object--this may take a few seconds

.Object.PrepareObject

End With

End Sub

(This PrepareObject call is why the form is so slow when loading for the first time)

ISBN AfterUpdate

So, now the “New Book” form is up, with an eager cursor, blinking away, waiting for you to enter an ISBN. Type in an ISBN, leave the ISBN text box and it will trigger the ISBN_AfterUpdate code back in our VBA macros

Up to this point, we have a form for a new record, but we have not yet generated a new recordset in the database. The Me.Form.Refresh call will generate an actual recordset in the database, so we have something to pass into our add-in.

Private Sub ISBN_AfterUpdate()

Me.Title = "Retrieving from Amazon..."
Me.Dirty = False

Up to this point, we have a form for a new record, but we have not yet generated a new recordset in the database. The Me.Dirty call will generate an actual recordset in the database, so we have a recordset we can pass into our add-in.

With COMAddIns("AmazonDemoAddIn.Connect")

.Connect = True

' Hook up the desired objects

If Not .Object.FillInBookForm(Me.ISBN, Me.Recordset) Then
Me.Title = "Failed to retrieve from Amazon."
End If

End With

Me.Form.Refresh

End Sub

This bit of code brings up an important point.

There are two ways to manipulate your form from your managed add-in:

Top-down: Pass in a control from your form (e.g. Me.ISBN) and manipulate its properties in your managed add-in.
Bottom-up: Pass in your form’s recordset and manipulate the record set.

For reasons too complex to get into here, if you’re adding complex data (for example, a new attachment) you’ll need to pass in the recordset. However, if your forms only contain textual data, most of that can (and probably should) be handled by manipulation of the controls.

Okay, on to the managed add-in code.

Managed Add-in

Over-simplified data flow of my AddIn.Connect class is as follows:

Grab ISBN control and Recordset passed in from VBA form code
Send ISBN value to a DataCollector object (Amazon, in this case)
DataCollector returns a Book object
Populate Recordset with information from Book object

So let’s, look at how each of those parts happens:

Grab parameters from VBA form code

Remember this call from our form VBA?

.Object.FillInBookForm Me.ISBN, Me.Recordset

This calls the following function in our AddIn:

public void FillInBookForm(Access.TextBoxClass ISBNTextBox,

Access.Dao.Recordset2 FormRS)

Send ISBN to a DataCollector (Amazon, in this case)

From here, we call CollectInfoFromWeb. This function checks if we have a DataCollector setup (we do), makes sure that the ISBN isn’t empty, and sends the ISBN on to the DataCollector.

Note that we’re accessing the .Text property of the ISBNTextBox control. This returns the text that’s currently occupies the ISBN control back in our form.

Book tempBook = CollectBookInfoFromWeb(ISBNTextBox.Text);

DataCollector returns a Book object

This is where lots of magic happens.

The DataCollector always sends back “Book” objects. You should be able to extend the DataCollector class to gather data from any Web Service you’d like (eBay comes to mind), as long as you somehow squeeze all the return data into a “Book” object.

If you’ve installed the .NET 3.0 extensions for Visual Studio 2005 (here: https://www.microsoft.com/downloads/details.aspx?FamilyId=F54F5537-CC86-4BF5-AE44-F5A1E805680D) from within Visual Studio you can add a reference to any WSDL-based Web Service just by pointing it to the WSDL. Simply right-click on your project, select “Add Service Reference” and type your WSDL into the form and Visual Studio will auto-generate a bunch of code for interacting with the service.

Once you’ve got the Service Reference added to your project, you’ll have a proxy class which you can instantiate to interact with the web service.

AmazonClient = new AWSECommerceServicePortTypeClient();

Note: all the configuration, binding and connection info for this proxy client is put in your project’s app.config file. This becomes the AmazonAddInDemo.dll.config file at build time, and needs to be renamed msaccess.exe.config copied to the directory alongside your MSACCESS.exe.

One of the methods on this proxy is ItemLookup. This call takes an ItemLookupRequest object as a parameter, so you’ll need to generate and fill out the properties of that object:

ItemLookupRequest lookupRequest = new ItemLookupRequest();

lookupRequest.ItemId[0] = myISBNString;

lookupRequest.SearchIndex = "Books";

Then make your call to ClientProxy.ItemLookup, and store the result as an ItemLookupResponse object:

ItemLookupResponse myResponse =

mAmazonClient.ItemLookup(itemLookup);

Then, lastly, we’ll map the properties from the response object into a new Book object.

newBook = new Book(currentItem.ItemAttributes.Title,

Author,

currentItem.ItemAttributes.Genre,

…);

return newBook;

Populate Recordset with information from Book object

We call .Edit() on the recordset we were sent (FormRS), and start plugging values into fields

FormRS.Edit();

FormRS.Fields["Author"].Value = tempBook.author;

FormRS.Fields["Title"].Value = tempBook.title;

…

Then for some real magic, we embed the cover art data into the recordset.

Internally, Access sees Attachments (like our cover art field), as embedded child recordsets (sub-tables). So, we’ll need to create a new record in the “Attachments” child recordset.

Access.Dao.Field2 fld =

Access.Dao.Field2)FormRS.Fields["Attachments"];

Access.Dao.Recordset2 rstChild =

(Access.Dao.Recordset2)fld.Value;

rstChild.AddNew();

And then dump our binary image data into the “FileData” field of this new record (from the URL of the image data), update the “Attachments”sub-recordset, the form’s recordset, and we’re good to go.

Access.Dao.Field2 fldChild =

Access.Dao.Field2)rstChild.Fields["FileData"];

fldChild.LoadFromFile(tempBook.coverImageURL);

rstChild.Update(1, false);

FormRS.Update(1, false);

Of course, everything you just spent 20 minutes reading about happens in less than the blink of an eye, and your when you update your Form’s recordset, the form will have all the data collected from the Web Service and you’re done (or at least I am, if you’re really into this kind of thing, you can now click “Save and New” button on your form and do the whole mess all over again).

Conclusion

Phew, I think that’s about it. Congrats if you actually made it this far. If you skipped ahead to this paragraph, I’m not going to gratify your laziness with some kind of all-summing conclusion. Hopefully, you now understand enough to know how to twist my project to explore your own wildly cool Web Service + Access intentions.

You can grab the Visual Studio project from here: Code Project.

It’s important to mention that I can’t take full credit for the generation of this demo. I leaned heavily on Ken Gatz’s excellent “Managed Add-in in Access 2007” white paper, as well as the debugging and VB skills of my fellow teammates. And frankly, the whole thing was Clint’s idea in the first place, so kudos to him as well.

If you’ve got additional, specific questions, first read Ken Getz’s whitepaper on “Managed Add-in in Access 2007” because his is a great article. If you have additional questions, feel free to post your questions here on the blog.