tomfolinsbee
asked on
Keyword search in MS Access across all tables
I am looking for a way to search for a text string in any record, in any table in an Access database.
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
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
ASKER
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.
I'm doing this for myself. Not concerned about speed, but have no idea about DAO routines.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@tomfolinsbee
what is the final goal here?
what is the final goal here?
ASKER
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.
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.x lsx",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
export tables to excel (this can be done using VBA)
docmd.transferspreadsheet acexport, , "tablename", "c:\foldername\tableName.x
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
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.
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.
ASKER
Thanks for the suggestion. My colleague is doing something along the same lines as the article suggested and it's working.
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.