INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

How to perform a Google search based on data in Access by jrbarnett
Posted: 27 Sep 08 (Edited 7 May 10)

Google is a widely used search engine and it offers a very powerful range of filtering features.
This FAQ shows several techniques to be able to open a page of Google search results with three different levels of sophistication, which you can incorporate into your own applications.  This technique could also be used with other search engines, given appropriate documentation.

Notes:
1. In order to use any of this code, create a new standard module, then copy the code and paste it.  Initial testing can be done via the immediate window, but it is more likely you will want to use this from within your own application user interface.  I leave that exercise to you, since you know your application far better than I do.

For use with the the data in tables, you will need to adapt the SQL code to bring in the terms from your own tables/queries.

2. I have used an ADO connection to connect to the database, so you will need to set a reference to an ADO object library before any of this will work.
By replacing "CurrentProject.Connection" with a valid connection string, you can have the data come from SQL Server, MySQL, Oracle or other databases supported by ADO.

Ideally you would add some far stronger error handling to this prior to any sort of production rollout, since this is for demonstration purposes only.

3. My reason for using Application.FollowHyperLink rather than an Internet Explorer browser object to access the results is that it takes account of the users default web browser rather than forcing IE for use of this functionality.  If, however, you want to read the results generated into your database, I think that this will become necessary.

4. I used the UK google site when testing.  Using the one within your geographical top level domain is likely to prove the fastest and give the most relevant results to your searches.
To change this, alter "http://www.google.co.uk/" to the base URL of the site that you use.

Basic search facility - one parameter
The basic simple search function to show the results of a simple search is as follows:

CODE

Function SimpleGoogleSearch (strSearchString As String)

' VBA function to perform a simple Google search,
' written by J Barnett (tek-tips handle jrbarnett)
' Requires Access VBA and web browser installed.

   If Len (strSearchString & "") >0 Then
    Application.FollowHyperLink "http://www.google.co.uk/search?q=" & strSearchString
   Else
        MsgBox "No search term specified", vbOkOnly+vbInformation
   End If
  
End Function

This will search for one particular term, and is the basic functionality on which the following steps build.  However, for simple applications, this may suffice.

To test this, from the immediate window use

CODE

SimpleGoogleSearch "MySearchTerm"

Search several terms based on data in a table
Create a table called tblKeywords with the following data:

KeywordID Primary Key Autonumber
KeywordText Text 50 Not Null

Enter some data into rows based on items you are interested in searching for.
What we are going to do here, is to go through the data in this table and implement one browser session (a tab in newer browsers such as Firefox 2/3 or IE7, or a separate session such as IE6 and older where tabs aren't supported).

CODE

Function MediumGoogleSearch(strSQL As String)

' VBA function to perform a Google search based on data in a table
' customise WHERE and HAVING clauses in SQL input parameter
' to retrieve only relevant data for passing to Google.

' Written by J Barnett (tek-tips handle jrbarnett)
' Requires Access VBA and web browser installed
' plus ADO Reference set in VBA editor.

    
    Dim cn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Dim strURL As String
    
    Set cn = CurrentProject.Connection
    
    ' This is the SQL Code that retrieves the data
    Set rst = cn.Execute(strSQL)
    
    
    Do While Not rst.EOF
        strURL = "http://www.google.co.uk/search?q=" & rst!keywordtext
        Application.FollowHyperlink strURL, newWindow:=True, addHistory:=False
        rst.MoveNext
    Loop
    
    rst.Close
    cn.Close

End Function

To try this out, from the immediate window use:

CODE

MediumGoogleSearch "SELECT KeywordText FROM tblKeywords ORDER BY KeywordText"

You can also use the name of a query saved within Access that returns records (eg a standard SELECT, UNION or pass through query with the Returns Records property switched on).
To filter down to only specific words, specify a where clause to retrieve only the data you need.

Advanced: Filtering by site
The final stage is to implement the ability to filter specific keyword results for a specific site.
This is implemented by adding "&inurl:abcd.com" onto the end of the query, where abcd.com is the name of the site.
For a fixed site, in which everything is restricted to the same domain, this can be hard coded (eg for searching a company website). Example:

CODE

Function AdvancedGoogleSearch1(strSQL As String)

' VBA function to perform a Google search based on data in a table
' customise WHERE and HAVING clauses in SQL input parameter
' to retrieve only relevant data for passing to Google.
' Filters to a fixed site specified in the SITE_RESTRICTION constant.

' Written by J Barnett (tek-tips handle jrbarnett)
' Requires Access VBA and web browser installed
' plus ADO Reference set in VBA editor.

    Const SITE_RESTRICTION As String = "&inurl:www.tek-tips.com" ' change this to your own site
    
    Dim cn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Dim strURL As String
    
    Set cn = CurrentProject.Connection
    
    ' This is the SQL Code that retrieves the data
    Set rst = cn.Execute(strSQL)
    
    ' Loop through the data and run the query    
    Do While Not rst.EOF
        strURL = "http://www.google.co.uk/search?q=" & rst!keyword & SITE_RESTRICTION
        Application.FollowHyperlink strURL, newWindow:=True, addHistory:=False
        rst.MoveNext
    Loop

    ' Tidy up
    rst.Close
    cn.Close

End Function

To implement just one restriction per site, you can add a new field to the Keywords table giving a URL or site and pass this across with a minor adaptation to the previous function (the SITE_RESTRICTION constant can be removed if you use this):

CODE

        strURL = "http://www.google.co.uk/search?q=" & rst!keyword & "inurl:" & rst!sitename

If, however, you want to specify a range of sites, this necessitates a different table to containe the restricted sites.

Add a new table called tblSites with the following fields:
SiteID Autonumber Primary key
SiteAddress Text (255) Required

Enter some sites with lots of content on into this table (eg en.wikipedia.com. msdn.microsoft.com etc) and in your keywords table put some useful keywords relevant to some of the tables.

Next, create a query that includes both tables and displays the KeywordName and SiteAddress fields.  Do not join the tables.  If you run it, you will get the cartesian product of those two tables - one row per entry in tblSites and tblKeywords.
Save this query as qryAdvancedSearch

CODE

Function AdvancedGoogleSearch2()

    Const QUERY_NAME = "qryAdvancedSearch" ' Query that generates the data (as a cartesian product of tblKeywords and tblSites
    
    Dim cn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Dim strSQL As String
    
    Set cn = CurrentProject.Connection
    
    Set rst = cn.Execute(QUERY_NAME)
    
    
    Do While Not rst.EOF
        strSQL = "http://www.google.co.uk/search?q=" & rst!keyword & "&inurl:http://" & rst!webpage & "&meta="
        Application.FollowHyperlink strSQL, newWindow:=True, addHistory:=False
        
        rst.MoveNext
    Loop

    rs.Close    
    cn.Close

End Function

Once you have some data in your tables, just run this from the immediate window.
I've deliberately not put in a parameter to specify SQL code for retrieval, since this is more likely to be customised by manipulating the query with restrictions (although this is an obvious enhancement).

Taking this further
What is here is very basic, and if you want to enhance this further to make more use of the functionality available, Google has some documentation available at http://code.google.com/apis/soapsearch/reference.html which explains what is needed and the format required.

Happy Googling.

Important Update 6 May 2010
It has recently been brought to my attention that due to an updated set of terms and conditions issued by Google, use of automated mechanisms to issue searches to Google is now prohibited.  As such, use of this code "as is" is not permitted without express permission from Google.
However, the techniques used in my code to pass data from Access into a web URL query string can be used with other applications or search engines.
 

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close