Full text search microsoft access

Search Search. Summary Thanks to internet search engines, users are used to searching for text but unfortunately even the desktop search tools from Microsoft and Google do not have a search that looks into tables easily. Introduction My favorite search is actually a search of my own computer using Google desktop search Vista has a similar tool. Finding a Text String The Access form see Figure 1 that drives the database search that I am outlining can be imported into your database.

The software has the following characteristics: A front-end Access database is established and links are made to all the back-end tables that you want a particular user or group of users to search.

The System Tables are then analyzed so that we can establish all the tables inside the database. Acceptable Use Policy. Advertiser Disclosure:. MS SQL. SQL Etc. Database Forum. Rss Feed. Free Newsletters: Email subscribe. Advertiser Disclosure. Figure 1 - Tool to search back-end databases for the location of strings The Code All of the source for this search tool lies under the search button.

FindFirst wherestr If. You could improve on this by: Running a query for each individual field in each table and reporting the fields that matched the Search phrases. Make it easy to see the data by actually outputting the rows with a match by creating software using: Currentdb. QueryDefs item. Download dbSearchAll. Help With Microsoft Access. September 4th, PM. Linked table not sorting or filtering - ODBC error. August 28th, AM. Use InStr function InStr function returns the position number of the first occurrence of one string within another.

If the string is not found, it returns 0. The following example searches character F within string Flower. It returns 1. Here we start at the 1st character of the string Flower. Flower - this is the string being searched. F - this is the string sought within string Flower.

You can also use VB constant instead of a number, see below. Table below lists the VB constants and the equivalent numeric value. Performs a comparison based on information in your database. Use the StrComp function StrComp string1, string2 [, compare ] As its name suggests, the StrComp function compares two strings based on the type of comparison specified in the third argument. StrComp function returns 0 if the two strings are the same.

Returns -1 if string1 is less than string2. Returns 1 if string1 is greater than string2. Returns Null if either string1 or string2 is Null. Great or less is based on alphanumeric order. Numeric values are always less than alphabetical characters.

Uppercase characters are less than lowercase characters. Lowercase English characters are less than Non-English characters, see below. Option Compare Statement The Option Compare Statement is used at module level to declare the default comparison method to use when string data is compared.

There are 3 types of Option Compare Statements. It's based on the sort order determined by your system's locale. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur. 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.

It was written in Access The sample code that duffymo provides works very well. I use it with Microsoft Access But a couple of fixes are necessary. 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 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 distinct records for my data table.

Thanks duffymo for some neat example code. It's about using the find tool. I haven't tried it yet and I'm not sure if it works on memo fields. Access is not a database.

I would assume that's what you mean, but you should be more clear what you mean when you ask a question like this. 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. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 13 years ago.

Active 4 years, 8 months ago. Viewed 16k times. BIBD Lawrence Teo Lawrence Teo 2 2 gold badges 6 6 silver badges 19 19 bronze badges. Don't use Access. That's your best bet.