Add Full-Text Search of an Access Database to ASP Pages

Although SQL Server 7 is the more robust and scalable database for full-text searching, many developers still use Microsoft Access for less-demanding database applications. You can search an Access database if you concatenate the field names in your query.

For example, in the following ASP code, the SQL query combines the FirstName and LastName fields, uses the keyword LIKE and then wraps the search fragment in wildcard characters. When run against the NorthWind database, this query produces a recordset of all employees with the letters “an” in their names.

 <%@ Language=VBScript %><%Set conn = Server.CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")conn.Open Application("Connection5_ConnectionString")rs.CursorType = adOpenStaticrs.LockType = adLockReadOnlyrs.CursorLocation = adUseClientSet rs.ActiveConnection = connrs.Source = "SELECT * FROM Employees WHERE FirstName + Lastname LIKE '%an%'"rs.OpenDo While Not rs.EOF   Response.Write rs("FirstName") & " " & rs("LastName") & "
" rs.MoveNextLooprs.Closeconn.CloseSet rs = NothingSet conn = Nothing%>
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


Recent Articles: