Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Search tool in MS Access or MS SQL that will allow me to quickly find a specific record?

Is there a  FREE TOOL or is there a way to create my own simple code that will allow me to search through an entire database at once without having to open every table or form individually that will allow me to find a record in a field like the word "box".

Then would this help me identify the table and field where the record resides...?
Any support would I would greatly appreciate. Thank you.
0
ruavol2
Asked:
ruavol2
  • 4
  • 4
  • 3
  • +2
5 Solutions
 
Rey Obrero (Capricorn1)Commented:
0
 
Andrew_WebsterCommented:
In short, no there isn't.

There's a couple of ways to manage something like this is to add a table to the database that can index everything else.  

One way would be to build a new table that will need a column to hold a table name, a column to hold the record id, and a column to hold aggregated text from all the text fields in that record.  If it's SQL Server, you can use a calculated column.  If it's Access, then you'll need to write code, probably in a data entry form's AfterUpdate event to aggregate the text into the new table.  This can then be searched.  The downside, you'll be searching using "LIKE" it's not a given that the database engine will be able to use an index in that case.  So the search might be slow.

The next way is basically the same, except you split the text into separate records, one word per record.  This would probably search more quickly as now you can use an index.

Finally, you could roll some code to use a SoundEx (native in SQL Server) or Metaphone algorithm and store the codes in you table.  This would allow for fuzzy matching, and could still use an index to speed the search.

The only other way I can imagine involved XML, and I can't even spell XML, so we won't go there!
0
 
Andrew_WebsterCommented:
BTW Rick Fisher's Find and Replace, that Cap recommended, doesn't search data, it only searches in objects properties.  For that, it's incredibly useful, a great tool.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Here is a free total Access solution

Universal Search on many text with details
0
 
Andrew_WebsterCommented:
Just had a look at HiTech's solution, and it would work fine on a small database.  You'll have to test it in your situation to see if it's performance is ok.

If it's not, then you'll have some more work to do.

I've faced this before, and used the SoundEx approach, and it's lightening fast, even on big databases.  It just takes a bit of design, build, and test work, is all.
0
 
Jeffrey CoachmanCommented:
TheHiTechCoach,

Is there a direct link to this file?

As was mentioned previously, many users may not wish to go through a third party site (and all the associated steps) just to download a sample file...

;-)

Jeff
0
 
Andrew_WebsterCommented:
I just dl'd it, so here it is:
universalsearch.zip
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Jeff (boag2000),  What I posted was a link to the site with the actual file to be downloaded. No third party jump.   Are you saying it is best to attach the example not a link to the download?
0
 
Jeffrey CoachmanCommented:
Thanks

Jeff
0
 
Jeffrey CoachmanCommented:
TheHiTechCoach,

First I will say that I think that you are a great Expert and your contributions here have been nothing short of stellar.
In fact I got a few tips form you, long before you became a member here.
;-)
I say these things so you get an idea of my tone, so this won't be taken the wrong way...
;-)

The link you posted first brings me to your site
Then I must click "Download"
Then I have to "Agree" to something.

Why not just post the link to the original file form the author's site?
like so:
http://www.fontstuff.com/downloads/CascadingListsDemo2000.mdb
Or post a link to the page itself, so the OP can also see the supporting data and any other applicable data as well:
http://www.fontstuff.com/downloads/index.htm

I also understand that you may have simply copied the link from your site and posted it here.

It might just make some people nervous that they must go to your site to download someone else's file...

Basically when other Experts see a link to a file that sends you to a site other than the original author's site, we may be bit suspicious...

The exact rules of this site regarding self promotion...?
I'm not sure.
But you can certainly contact one of the Mods to be sure.

You can contact me directly if you like.
My contact info is in my profile.

JeffCoachman
0
 
Jeffrey CoachmanCommented:
ruavol2,

I think the sample THTC posted will get you what you wanted.

You can even create a report from this form by saving the from AS a report.

Then on the Open event of this new Report, you can do something like this:
    Me.lstList.RowSource = Forms![frmForm]![lstList].RowSource
(The form must still be opened with the search results displayed)

;-)

JeffCoachman
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
JeffCoachman,

Thanks for the feedback.
0
 
ruavol2Author Commented:
I tried all the solutions and they all seemed to work in some degree. The best seemed to be the Universal solution which found the records I needed. The database is small so it did not seem to matter. thank you all for the feedback.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now