Using Microsoft Access as a Database


Sometimes, it's simply not efficient to query Google Web Services for the same information. Many users have research projects that span a week or two (and sometimes longer). They make the same queries every day and wait for Google to deliver the results. In some cases, the order of the URLs vary, making it hard to know which URLs you've already visited. For this and many other reasons, it's often easier to simply cache the data locally. The sections that follow discuss caching techniques and methodology.

Caching as a Practical Performance Enhancing Technique

You might wonder why caching will work for search applications. Many developers tend to think that every search is unique and consequently, cached searching won't work. However, most people in an environment tend to look for the same bits of information. For example, a company that provides insurance to automobile owners is unlikely to spend a lot of time researching barn roof failures in Wisconsin. In fact, such a company will have a focused search routine. The search areas of interest might encompass the automobile crashes in a single area. If you can identify a search commonality in your company or for your personal use, a cached search can be quite productive. In fact, you'll receive the following benefits from cached searches.

Faster Searches The one benefit that everyone can agree on is that you can search more quickly using a local data store ” especially if that data store is optimized for a specific use. No matter how fast your Internet connection, it can't compete with the speed of your LAN and local database. (This isn't to say that Google is slow ”Google is quite fast and you'd be hard pressed to duplicate that speed, but the realities of the Internet conspire to reduce even Google's stellar performance.)

Higher Hit Rate You can optimize a cached data store using statistics and user input. For example, some search results are almost certainly going to contain unwanted data that you can purge from the database. It's also possible to optimize the appearance of links based on hit rates and usefulness , with the better sites appearing first. In this case, better means sites that you're more likely to need, rather than the general audience that Google serves.

Search Optimization Users often become frustrated with search engines because they don't know the required keywords. They might know a synonym of the word, but not the precise word. Google overcomes this problem by allowing synonym searches, but using this technique often results in huge return sets. By watching the keystrokes users make when entering search criteria and by knowing the typical searches for your application, the application can make word suggestions that are more appropriate and provide better results.

Offline Searches It's impossible to perform a Google search when you don't have a connection to the Internet available. You can still perform a cached search, however, and might obtain everything you need from the offline database. As users become more mobile, the need to develop and enhance offline storage becomes more critical.

Enhanced Snippets The Google snippets you receive are helpful, but might not be precisely what you need. It's possible to create a database that includes part Google information and part specialized information. You could store essential elements of the site when you visit it as part of an extended snippet.

Personalized Notes Google's customer comments can become quite contagious. Storing the Google search results offers one more opportunity to include comments. The database could store localized notes about the various sites. These notes would reflect the biases, needs, and requirements of your particular organization. In short, the cached data would become value enhanced through the addition of user opinions .

Reduced Google License Usage One of the big reasons to use a cache is so you don't eat away at the limited number of searches you can perform on Google everyday. Remember that you only get 1,000 searches per day (unless you request more and Google grants the request). Consequently, a cached search presents an opportunity to save one of the searches for that day.

Tip  

A particular benefit of Access is that it does work well on a single machine. You can create an application that provides a personal data store of search results. Developing a personal data store means that your search data goes with you.

Creating a Cached Application

A cached application is one in which you can save data for later use. The code in this example demonstrates many of the principles you need to know in order to create a cached application. Listing 5.7 shows typical code for this kind of application. Note that this code is much shorter than the actual code in the Access database and the section doesn't describe the table layout. You'll find the complete source for this example, along with queries and table layouts, in the \Chapter 05\Access folder of the source code located on the Sybex Web site.

Listing 5.7: Caching Google Web Services Searches
start example
 Public Sub SimpleQuery()      ' Define the Web Service variables.      Dim Client As SoapClient30 ' SOAP Client      ... Lots of Other Variables ...      ' Define the database variables.      Dim SearchRec As Recordset      Dim ResultRec As Recordset      ' Define the SearchQueries table field variables.      Dim Index As Integer      ... Lots of Other Variables ...      ' Define the Results table field variables.      Dim Title As String      ... Lots of Other Variables ...      ' Create the recordsets.      Set SearchRec = CurrentDb.TableDefs("SearchQueries").OpenRecordset      Set ResultRec = CurrentDb.TableDefs("Results").OpenRecordset      ' Create and initialize the SOAP client.      Set Client = New SoapClient30      Client.MSSoapInit "http://api.google.com/GoogleSearch.wsdl", _                        "GoogleSearchService", _                        "GoogleSearchPort"      ' Make a search request.      Set Doc = _         Client.doGoogleSearch( _           "Your-License-Key", _           "DataCon Services site:www.mwt.net", _           0, 10, False, "", False, "", "", "")      ' Process the search information.      For Counter = 0 To Doc.Length - 1         ' Get the node.         Set Results = Doc(Counter)         ' Check the node type and react accordingly.         Select Case Results.baseName            ' Get the original search query.            Case "searchQuery"               SearchQuery = Results.Text            ' Is document filtering enabled?            Case "documentFiltering"               DocumentFiltering = CBool(Results.Text)            ' Number of search results available.            Case "estimatedTotalResultsCount"               EstimatedTotalResults = CInt(Results.Text)            ... Other Cases ...      End Select   Next   ' Begin processing the SearchQueries data.   With SearchRec      ' Add a new record.      .AddNew      ' Fill it with data.      !SearchQuery = SearchQuery      ... Other Fields ...      If Not SearchTips = "" Then         !SearchTips = SearchTips      End If      ' Update the database to hold the record and then point to it.      .Update      .MoveLast      ' Get the index for use with the results table.      Index = !Index   End With   ' Process the results.   For Counter = 0 To Doc.Length - 1      ' Get the node.      Set Results = Doc(Counter)      ' Check the node type and react accordingly.      If Results.baseName = "resultElements" Then         ' Set the real item counter.         RealData = 0         ' Process each item in turn.         For ItemCount = 0 To Results.childNodes.Length - 1            ' Get the item.            Set Item = Results.childNodes(ItemCount)            ' Determine whether this is a data item.            If Item.baseName = "item" Then               ' Process each item data element in turn.               For DataCount = 0 To Item.childNodes.Length - 1                  ' Get the data item.                  Set ItemData = Item.childNodes(DataCount)                  ' Print the required data on screen.                  Select Case ItemData.baseName                     Case "title"                        Title = StringToText(ItemData.Text)                     ... Other Cases ...                  End Select               Next               ' Update the real data counter.               RealData = RealData + 1               ' Add this record to the Results table.               With ResultRec                  ' Add a new record.                  .AddNew                  ' Fill it with data.                  !Index = Index                  ... Other Data ...                  ' Update the database to hold the record.                  .Update               End With             End If          Next      End If   Next 
end example
 

As you can see, this is a large application and there aren't any good ways to make it shorter. The application has two tables. The first table contains all of the items that affect the results as a whole, such as the query terms and the search time. The second table contains the individual item values, including the page title and URL. The code represents these two tables as SearchRec (search items) and ResultRec (individual items). One of the first tasks the code performs is to create recordsets from these two database tables.

The code then requests the data from Google. As with other examples in this chapter, Google Web Services returns an IXMLDOMSelection object. However, unlike other examples in the chapter, you can't use a single loop to process the data ”you need two loops , one for each recordset. The first loop creates the data for SearchRec . Notice the code uses VBA functions such as CBool() and CInt() to convert the data into a format the database can accept.

After the code creates all of the required values, it adds a record to SearchRec and begins filling the fields with data. Some variables, such as SearchQuery , always contain data. However, other variables, such as SearchComments , only contain data when Google Web Services sees a need to provide it. If you don't detect the empty variables as shown in the code, Access is likely to display an error and not accept the new record.

Note  

You must obtain the Index field from the main record. The code uses this value to link the Results table to the SearchQueries table.

At this point, the second loop begins. The code is only concerned with the individual items this time, so it begins by searching for the <resultElements> element. Once found, the code processes each of the <item> elements in turn. Like every other VBA application, Access returns about twice as many <item> elements as you'd expect, with half of them containing nothing.

The code processes individual values, just as it did for the search items. However, it adds these values to the Results table after each loop. Consequently, you'll normally see 10 result records for each search record. Adding the record to the table is the same as the search values. However, if you don't include the Index value, Access will reject the record for referential integrity reasons (no matching search record).




Mining Google Web Services
Mining Google Web Services: Building Applications with the Google API
ISBN: 0782143334
EAN: 2147483647
Year: 2004
Pages: 157

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net