|HCS Consulting Group.|
|We make Complex Systems simple|
By Albert D. Kallal
Monday, September 13, 2010
A Search engine in Access web services?
Not really, but searching for names that sound the same in Access Web services is easy!
Recently a question was asked on the MSDN Access developer forum how to create an Access web form that allows searching of people's names when the name is miss-spelled. By the way, the MSDN forum is a great community place to ask questions and get help using Access. You can find this Access forum on the Microsoft Developer Network here:
While for many, the idea of an intelligent search form in Access that allows fuzzy name matching sounds like real magic. To us folks who been around a long time we instantly recognize this as a soundex type of search.
While we are not going to build the next great search engine in Access, it is rather easy to build a search based on names that sound the same. The end result is a search form that behaves much like using any modern search engine on the web where exact name spelling matches are not required for the search.
The solution for this magic centers around using code that converts a person's name into a soundex code. We then save this soundex code into another column in the table. It then becomes a simple matter to search names on this soundex column. Search results will thus match on how names sound, not how names are spelled.
There are likely more variations of soundex code to be found on the web then are flavors of ice cream. You are thus much free to go out and choose one of the MANY examples of soundex coding systems. It goes without saying that the classic soundex routines have been much changed and improved over the years. However, the basic soundex code will suffice for our needs and is what I used for this example.
Prior to Access 2010, to maintain such a list of soundex codes we would have to use after update events in form's code. However, if you then write some VBA code, or created additional forms that edits that data, then again additional code would be required in the application to again update and maintain those soundex search values.
With the arrival of database triggers in Access 2010, all forms and code will benefit from one copy of our centralized bit of code as a stored table procedure.
The beauty of one centralized copy of our code really makes this task oh so easy. Any VBA DAO update code, SQL update (action) queries, editing data with a form, even editing data with a web form will in ALL cases trigger our one copy of table code to run.
An Additional benefit of centralized code is a separation of user interface you build from that of the application code and business rules. You are well free to build client forms, web forms, or even write VBA DAO update code without a worry. I find this a near magical development experience as now all you have to do is just have fun building and painting your forms. In other words separation of these tasks makes the development process more enjoyable for me. In fact since you dealing with just code or just creating a user interface, then I find this reduces my mental workload when developing software. It is this reduced mental workload that likely explains why I find developing applications this way more enjoyable.
The first task here is to find some soundex code. I just happen to have some soundex code I wrote in Access VBA code more than 10 years ago.
There not really a whole lot of code here sans the comments. I am pasting this older code as is. You can much skim and scroll over this code quickly for now.
Function strSoundex(strSource As Variant) As Variant ' ' Produces a code based on the "Soundex" method ' ' ' Parms: ' strSource Passed string ' ' ' Written by Albert D. Kallal ' ' ' See page 392 of Knuths' book 'Sorting and Searching', Volume 3 of 'The ' Art of Computer Programming", Addison/Wesley publisher. ' ' Method used: ' 1. Change all lowercase to uppercase ' 2. Retain first letter of input string (must be alpha) ' 3. Ignore the letters A, E, H, I, O, U, W, Y, and any other ' non-alphabetic characters: ' 4. Subsitiute the following ' 1 = B F P V ' 2 = C G J K Q S X Z ' 3 = D T ' 4 = L ' 5 = M N ' 6 = R ' 5. Ignore identical letters next to each other ' 6. Add trailing zeros if the length is less than "max.soundlen" ' characters (in this example it is 4 numeric + 1 alpha = 5). ' Stop when the string reaches max.soundlen (ie:truncate the rest) ' 'START:-------------------------------------------------------------------- ' If IsNull(strSource) = True Then strSoundex = Null Exit Function End If Const maxsoundlen As Integer = 4 ' Max length of resulting soundex code Dim TransTable As String Dim offset As Integer ' Offset for easy TABLE translates Dim SourceLen As Integer Dim charptr As Integer Dim testchar As String Dim lastchar As String Dim intLookup As Integer Dim strDigit As String ' Conversion table ' [ABCDEFGHIJKLMNOPQRSTUVWXYZ] TransTable = "01230120022455012623010202" offset = Asc("A") - 1 ' Offset for easy TABLE translates ' ' note: for "AEHIOUWY" are ignored by translating to zero ' strSource = UCase(strSource) ' convert string to uppercase SourceLen = Len(strSource) ' find/set string length to process strSoundex = Left$(strSource, 1) ' Get/set first character lastchar = strSoundex charptr = 2 ' We skiped the first char above Do While (charptr <= SourceLen) And (Len(strSoundex) < maxsoundlen) ' testchar = Mid$(strSource, charptr, 1) ' get 1 char to test ' ' if different than last character, then process ' If testchar <> lastchar Then ' intLookup = Asc(testchar) - offset If (intLookup > 0) And (intLookup <= 26) Then strDigit = Mid$(TransTable, intLookup, 1) ' table translate to soundex If strDigit <> "0" Then strSoundex = strSoundex & strDigit lastchar = testchar End If End If End If ' charptr = charptr + 1 ' move on to next character Loop 'strSoundex = Left(strSoundex & "00000", maxsoundlen) ' pad with trailing zeros (5 CHARS) End Function
While Access web services allows a mix of client VBA and web macro based forms, the web forms can't use VBA code. We thus will need to re-write the above VBA code as Access macro code. I on purpose posted the above VBA code since I want readers to get a feel for how I went about converting the above code to run in the Access web environment.
A quick look at the above code shows a lot of classic VBA functions. I see Ucase$(), string$() (create string of repeating characters) left$(), mid$(). I even see Asc$() being used. Golly, Asc$ ?, that is an function I not used in many years! The Asc$() converts a single character into a ASCII number. For example, the letter A will return the number 65.
In fact, just looking at the above soundex code harks me back to the old days of typing in BASIC code. I have fond memories using these basic functions (pun intended) when I was young. If any of you remember typing in BASIC code from some Byte magazine before the internet age, you remember well what I am talking about. At least now in this internet age, I provide a download at the end of this article and save you the trouble of having to type in this code.
I well remember the pleasure of discovering functions like Mid$() or Asc$() back in those old days. I had no idea that so many years later I would be using that same knowledge of these functions to create code that will run on a web site! Yet, this is exactly what Access Web Services allows me to do.
Fortunately, looking at the Access data macro function set, I see every one of the above classic VBA functions used in that soundex code is available for Access Web services. This means I can near read and translate the VBA code line for line into macro code. Assuming you have duel monitor nice and handy, I going to type and convert this code right into the editor on the fly. Ok, here I go...
Opps! There is a feature missing I need here! The data macro is missing a for/next loop. Ok, this is where we get a bit creative. By creative this is a fancy term we professional developers use when we sugar coat what we technically call a kluge.
For this looping requirement I substituted a for/each record loop. This loop is based on a tblMonths table with 12 records. This is a month picker table I often use in applications for combo boxes to pick month as text, but return 1-12 as a number.
I also just happen to need a for/next loop that runs for about the first 12 characters of a person's name for conversion into soundex code. So, I can well say this creative table loop trick (kluge) fits well within the current design I have.
The resulting macro code much reads very much like the VBA code on a line for line basis. I was able to re-type each line of VBA into macro code by eye sight on the fly. The comments were cut + pasted. In less than 20 minutes, I thus had:
This macro code is saved as what we call a named table macro. In Access web development you can often think of named table macros as your replacements for code modules. This code just sits there and will not run unless called from a form, a web form or a table trigger. If you are new to Access web development, then you find that named data macros are where a large portion of your common Web application code will reside.
With our soundex routine done, the next step is to create one of those neat-o table triggers that calls the above code to convert the person's first name into soundex during data entry.
Here is the table trigger code in the table after update event. This code calls the above soundex code and the returned soundex value is saved into a new column in the customers table.
I also cut + pasted the above code to the table's on-insert event. Eagle eyes will not the above log event, and that just some debugging code I had not removed as of yet.
Ok, that really quite much all we need here.
Next, I simply used the wizard to create a multiple items form (continues form). Drop in an un-bound text box for searching + a search button, and we get:
Note in above, you can see the name I typed in, and the results after hitting the search button. The results matche similar sounding names. So, Cindy, and Cyndi, Cynda were all matched in this search here.
The code behind the search button on the above web form is just this little bit:
We take the value of the un-bound search box and call the Soundex routine. We then set a filter based on the returned value from the soundex routine. Note that both the table trigger and the web form call the same Soundex routine.
Using functions like string$() and Asc$() have been real staples of my computing career over so many years. In fact, I used these functions in classic BASIC, FoxPro, Pascal and on more systems then I can remember. I can now add Access Web Data Macros to my long list of platforms where these little function friends have followed me over so many years.
While Access Web Services entails learning many new concepts, the bulk of this solution was based on leveraging past access skills and code.
Simply, easy, and fun this solution was to build. And, this solution was a real break from those massive frame works that so much of the computing landscape requites to build a simple hello world program.
Seeing such wonderful results run on the web for so little effort made me feel young and induced that long forgotten sensation and love of computers I not felt since typing in my first few lines of BASIC code into an 8bit computer.
This was really fun, and I hope you enjoy this code sample as much as I enjoyed creating it!
You can download the sample application here - Access Soundex Example
This sample Access 2010 Web database runs fine in the Access desktop client. You can even import the sample table into a non web database, and the soundex macro code will copy along for the ride. I also included a non web based edition of the search form. So this soundex example will work equally fine in client (non web) Access applications.