?
Solved

Keyword search in MS Access across all tables

Posted on 2015-01-07
9
Medium Priority
?
733 Views
Last Modified: 2015-01-23
I am looking for a way to search for a text string in any record, in any table in an Access database.
0
Comment
Question by:tomfolinsbee
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40537174
The only way I know to do that is to search through the system tables to find a list of all tables and fields, then set up a loop to open all tables and navigate through all fields to search for it.
Or you can create a very big query that links every table and shows every field but probably not worth it.
At this point, I don't recall how to access that system table since the last time I did anything like that was Access 2000.
0
 
LVL 85
ID: 40537464
You can create a DAO routine that does this, but it's a lot of work and is very slow.

If this is a one-time thing consider something like Find and Replace from Rick Fisher (http://rickworld.com/). It's cheap and works very well.

If you must do this for the end user, Garry Robinson wrote an article for the DAtabase Journal which might be helpful: http://www.databasejournal.com/features/msaccess/article.php/3774016/Searching-For-Text-Anywhere-In-a-Database.htm
0
 

Author Comment

by:tomfolinsbee
ID: 40537531
Thanks for the suggestion, however, I don't think Find and Replace works on the data in the tables? " It provides a "Find and Replace" function for Tables (searching design elements such as field names, not the data in the table)"

I'm doing this for myself. Not concerned about speed, but have no idea about DAO routines.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40537587
Sorry, you're correct. F&R doesn't search data - my bad.

The link I provided shows how to do this with DAO, and includes a downloadable demo database. Try that and if you encounter troubles post back here.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40537672
@tomfolinsbee

what is the final goal here?
0
 

Author Comment

by:tomfolinsbee
ID: 40537731
Goal is to identify which table/field contains specific data. Too time consuming to review individual tables/fields. I figured Access would have something similar to Excel's search & replace.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40537761
you can do this if you want to..
export tables to excel (this can be done using VBA)

docmd.transferspreadsheet acexport, , "tablename", "c:\foldername\tableName.xlsx",true


then you can use the excel's search and replace

a tedious way is to open the table as recordset and iterate thru the recordset/fields to find the the specific data
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40537882
Spreadsheets are a completely different technology from relational databases so what is available in one will not necessarily be available in the other.  For starters, Excel is essentially ONE table not many and the entire table is read into memory.  That is completely different from how relational databases work.

Access does have its own find and replace which works on data.  I have never run it from code but I'm sure you can.  I've run spell check from code behind a form and you may need to run this that way also because Access will need a way to display the results.  I don't have time to research the details but you would have to specify Current Document rather than the default which is current field and you will have to create a loop to do it one table at a time.
0
 

Author Closing Comment

by:tomfolinsbee
ID: 40566216
Thanks for the suggestion. My colleague is doing something along the same lines as the article suggested and it's working.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question