Does Microsoft Access have Full Text Search?

Question

Does Microsoft Access have Full Text Search?

I know that MySQL and SQL Server have Full Text Search, but I am not too certain on Access.

If Access doesn't have Full Text, what is the best alternative to achieve the same objective of Full Text Search?

Thanks

Scurrility

Don't use Access. That's your best bet. – Castoff

Listen to Justice, he is wise. – Anastasio

Answer 1

I'm not 100% certain, but the fact that this site from Microsoft doesn't mention Access suggests to me that the answer is "no".

My gut reaction would also be "no". Access wasn't intended to be the ne plus ultra in relational database technology. If it were, there'd be no reason for SQL Server.

Daliadalila

Answer 2

The first step in full text searching is to create a word list containing all the words in the database. Full text searching also has other features like stemming, which relates derived words to a base word (fast, faster, fastest) and it has stop words which are ignored because they are so common (and, the). A little VBA code can generate a word list which can be visually scanned. With a little more work, it would be possible to use code to check the word list first before searching the database and this might make searches much faster. Below is some code that I created for the purpose. Its real life application is to find peoples names in database so that I can remove or alter them for privacy protection and as a biologist, I want to italicize scientific names in reports. If I can create a list of scientific names, then I can replace them with the name enclosed in html tags.

The code works well but I have not tested it extensively or against large memo fields/rich text fields. It was written in Access 2010.

'This code requires a table called tblWordList with fields called Word (str 255), WordCount (long), FirstCopyID (long)

Option Compare Database
Option Explicit

'Click on this procedure and press F5 to run the code
Private Sub ScopeWordList()

    'A list of tables and fields that need to be processed
    Call CreateWordList("Issues", "IssueSummary", "IssueID")

End Sub

'The main routine that finds new words
Public Sub CreateWordList(TableName As String, FieldName As String, ForeignKey As String)

    Dim dbs As Database
    Dim rst As Recordset
    Dim SQL_Statement As String

    Dim r As Recordset

    Dim RowText As String
    Dim OriginalWord As String
    Dim SearchWord As String
    Dim SearchTerm As String
    Dim Quote As String: Quote = Chr$(34)
    Dim i As Long
    Dim RecNum As Long

    SQL_Statement = "SELECT " & FieldName & ", " & ForeignKey & " AS FirstCopyID FROM " & TableName & " WHERE " & FieldName & " IS NOT NULL;"
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(SQL_Statement, dbOpenSnapshot)

    Set r = dbs.OpenRecordset("tblWordCounts", dbOpenTable)
    r.Index = "Word"

    With rst
    If .RecordCount = 0 Then GoTo ExitCreateWordList

    Do Until .EOF
        Dim RowWords As Variant 'holds an array which needs to be created
        RowText = .Fields(0)
        'strip out quotes, slashes and other characters
        RowText = CleanLine(RowText)
        'split data into words
        RowWords = Split(RowText, Space(1))

        For i = LBound(RowWords) To UBound(RowWords)
            OriginalWord = RowWords(i)
            SearchWord = Left(Trim(OriginalWord), 254)
            If Len(SearchWord) > 0 Then
                    r.Seek "=", SearchWord
                    If r.NoMatch Then
                        r.AddNew
                        r!Word = SearchWord
                        r!wordcount = 1
                        'records ID field of first occurrence, so you can debug unexpected results
                        r!FirstCopyID = !FirstCopyID
                        r.Update
                    Else
                        r.Edit
                        r!wordcount = r!wordcount + 1
                        r.Update
                    End If
                End If
'            End If
        Next i
        RecNum = RecNum + 1
        If RecNum Mod 20 = 0 Then Debug.Print "Record " & RecNum
        .MoveNext
    Loop

ExitCreateWordList:
    End With
    Debug.Print "Done"
    Set rst = Nothing
    Set dbs = Nothing


End Sub

'Need to clean out unwanted characters and replace then with normal spaces
Private Function CleanLine(RowText As String) As String

        Dim X As Long
        Dim Y As String
        Dim Z As Long
        Dim W As String

        For X = 1 To Len(RowText)
            Y = Mid(RowText, X, 1)
            Z = Asc(Y)
            Select Case Z
                Case 65 To 90      'capital letters
                    W = W & Y
                Case 97 To 122     'lowercase letters
                    W = W & Y
                Case Else
                    W = W & Space(1)
            End Select
        Next
        CleanLine = W

End Function

'Delete all records in Word List table
Public Sub ClearWordList()

    Dim SQL_Statement As String

    'Delete all records from tblWordCounts
    SQL_Statement = "DELETE FROM tblWordCounts"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL_Statement
    DoCmd.SetWarnings True

End Sub

Buckels

Answer 3

MSDE (now called Sql Server Express) has full-text indexing, if you're looking for a client-deployable database

Anastasio

Answer 4

The sample code that @duffymo provides works very well. I use it with Microsoft Access 2003. But a couple of fixes are necessary.

1. The table needs to be defined as: 'This code requires a table called tblWordList with fields called Word (text 255), WordCount (number), FirstCopyID (number)

2. The other fix is tblWordCounts needed to be replaced with tblWordList, and of course the Call CreateWordList needed to be changed to the table and field that needed to be sent to tblWordList.

On a 5000 record table it ran so fast I thought that it didn't work when I clicked on Sub ScopeWordList() and pressed F5, but the module created a word list with over 700 distinct records (for my data table). Thanks @duffymo for some neat example code.

Lunkhead

Answer 5

http://www.dummies.com/how-to/content/finding-records-in-your-access-2003-tables.html

It's about using the find tool. I haven't tried it yet and I'm not sure if it works on memo fields.

Neukam

This isn't even close to the same thing as full text search. FTS is a programmatic way to search one or more columns across one or more tables within a database. The CTRL+F item you linked to is a one off type of thing and certainly isn't controlled through code. – V2

Without looking at it @Neukam might be on the right path by performing a docmd. find to quickly retrieve some basic data. – Enrapture

Answer 6

Access is not a database. But it does ship with a default database engine, Jet/ACE. I would assume that's what you mean, but you should be more clear what you mean when you ask a question like this.

Jet/ACE does not have any full-text search capabilities.

The best way to do full-text search of Jet/ACE data files is through whatever full-text search capabilities you have for files on your computer. This is not going to be fast, nor is it going to be usable via SQL.

You don't say what context this is, but I have in general never seen a need for full-text search except on websites (where it's something of an expected capability). If you're using Jet/ACE as the datastore for an HTTP application, then you've chosen the wrong data store. While Jet/ACE can work fine for low-volume read-only websites, that's not an advisable usage (because of limitations in the Jet/ACE database engine).

If you need full-text searching then you need a different database engine.

Mallis

Access is a DBMS with a development platform bolted on (VBA, Access Forms and Reports). An Access file can be a database, but not necessarily. Unless you state these things, your post is only going to confuse new users, and not help them develop their understanding of the product, given that Access is generically referred to as a database. In the same way, SQL Server is not a database but a DBMS. The difference, in terms of what its files represent, between SQL Server and Access is that SQL Server files are always database files. – Clubman

@awrigley: how is your comment anything other than just restating what I said in my answer? I'm quite clear in my answer that I'm talking about the Jet/ACE database engine. There is no ambiguity at all in what I wrote. You really should reverse the downvote, as your comment is just gibberish. – Mallis

A newcomer to Access reads an answer that says Access isn't a database, but the comment doesn't expand on what Access is except in acronyms? That is gibberish to a newcomer. The downvote stands, not just because your comment is puerile. – Clubman

You say my comment restates what you say then you say my comment is gibberish. Make your mind up. My comment restates what you say in a way that might just help the kind of person asking this question. Which is what SO is all about. – Clubman

The question is asking "(...) what is the best alternative (...)j". It's an easy path saying "change your tool" but I'll never undervaluate the author doing so. I'm sure she/he has good reasons to keep the tool and try to find a workaround. There's nothing of an answer by showing your frustrations with Access and throw a personal cryptic review on Jet/ACE. The statement is clear: an alternative. Post it as an answer if you have it. All other considerations are futile and should go on comments. – Zerelda