Tool for Finding String in a Query's SQL Code

[Mike Thomas]

I am using Access 2003 and need a way to search the SQL strings behind
queries. The Access Edit > find works well for VBA code, but I have not
found a way to search all queries for a table name, for example.

Is there a way to do this in Access? Is there a 3rd party tool to do this?

Thanks
Mike Thomas

 

[Jeff Boyce]

Mike

Several third party tools, some for a cost. Two that cost are "Speed
Ferret" and "Total Access Analyzer".

Check the mvps.org website for possible other recommendations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I use Rick Fisher's Find and Replace. A real bargin and it works well. Find
it at:
http://www.rickworld.com/download.html

Speed Ferret is pretty much a dead product. They still don't have a version
that works with databases newer than 2000. You can use it, but you also have
to have an older version installed. I called to complain, but apparently,
they are a mom & pop shop that doesn't have the resources to bring it up to
date.

If you need something quick and dirty, this function will print the name of
every query it finds the value in in the immediate window:

Sub FindQueryValue(strVal As String)
Dim lngQryCount As Long
Dim lngQryNdx As Long
Dim qdfs As QueryDefs
Dim blnFoundIt As Boolean

Set qdfs = CurrentDb.QueryDefs
lngQryCount = qdfs.Count - 1
For lngQryNdx = 0 To lngQryCount
If InStr(qdfs(lngQryNdx).SQL, strVal) > 0 Then
blnFoundIt = True
Debug.Print qdfs(lngQryNdx).Name
End If
Next lngQryNdx
If Not blnFoundIt Then
MsgBox "No Matching Entries"
End If
End Sub

 

[Jeff Boyce]

Thanks! I hadn't realized that Speed Ferret was dead. I guess I have a lot
of 2000 dbs to support!

Jeff

 

[Guest]

Opps! I misspoke, it works through 2002. Here is a link that gives the
particulars:
http://www.speedferret.com/sfkb0041.html

 

[Mike Thomas]

Many thanks - I might try the code snippet, then the Rick Fischer product.

Mike Thomas

 

[Guest]

I really like Rick's product. It is an Access Add In, easy to use, and only
$39 US.

 

[moshannon]

Let me take this opportunity to clarify the status of SPEED Ferret.

SPEED Ferret 4.1 is our currently-shipping product. It directly
supports Access 97, 2000, and 2002. It can also fully support any
database that you might be using in Access 2003 provided that a copy of
Access 2002 is also installed on your system.

SPEED Ferret 5.0, currently in development, has the ability to search
and replace, compare, and document databases created in Access 97,
2000, 2002, 2003, and 2007. We're expecting to ship 5.0 at about the
same time that Office 2007 ships.

Bob Snyder, President
Black Moshannon Systems
http://www.moshannon.com

 

[Guest]

That is what you have been saying for two years. I have abandoned your
product, because it is not feasible in most cases to have multiple versions
of Access Installed. I have to use what the company installs.

It seems unreasonable that it would take this long to upgrade an existing
product. In addition, I find the Rick Fisher's product, which is about 1/3
the price of your product, is easier to use and provides as much information
as you do.

You even offered a discount for people who purchased 4.1 within x day when
5.0 would release. No chance of that anymore.

Maybe you should start working on a release for Access 3000, you might just
have it ready.

 

[moshannon]

Klatuu,

You're absolutely right. This release has taken far longer than we
anticipated. We originally planned to just add support for Access 2003
and ship it. But at that time we were getting very few requests for
Access 2003 functionality. Given the fact that we had a workaround for
Access 2003, we decided to "go for it" and add some additional new
functionality that we'd been planning for the future, such as a compare
feature. In retrospect, we should have just shipped an Access 2003
version before embarking on what turned out to be a major design
effort. That's where we screwed up.

Although many of our customers are able to use the workaround (i.e.
also installing Access 2002), we realize that this isn't an option for
everyone. We have lost many sales due to the fact that we don't
currently have a solution for customers who can only run Access 2003. I
think it would be accurate to say that we are "highly motivated" to
ship a high quality version 5.0 product as soon as possible.

We've been in business since 1995. Our first product, a database
analyzer named "Tracker", worked with Access 1.1. During the past 11
years, we have had a total of eight major product releases, with an
average time between releases of 16.5 months. This one's definitely
taking longer than most, but we're also setting our goals higher than
usual.

Over the years, we've had a lot of conversations with a lot of
customers. We've received many feature requests, and we've gained a
pretty good sense of the kinds of things that our customers would like
to see. Our new compare feature takes a different approach than most.
We've put a lot of time and effort into it. We think that it's going to
offer some unique benefits.

But every tool and every tool vendor has its strengths and weaknesses.
We definitely have room for improvement when it comes to predicting
release dates. People are always asking us for a definite date, and we
always do our best to be realistic, saying that we "hope" to ship by
<target date>. But after 11 years in business, I fear that we are no
better at predicting release dates today than we were at the beginning.
Your criticism of our shifting release date is quite legitimate. I lose
sleep over that issue.

Please believe me when I say that we are trying our very best to
deliver a quality product as rapidly as possible. I am very sorry for
any inconvenience that our long release schedule has caused for you and
others who may find it problematic to employ the workaround mentioned
earlier.

Bob Snyder, President
Black Moshannon Systems
http://www.moshannon.com
1-814-345-5657 (M-F 8am-5pm Eastern)

 

[ruralguy via AccessMonster.com]

If you can handle a little VBA, here's one for free:
http://allenbrowne.com/ser-41.html

Klatuu,

You're absolutely right. This release has taken far longer than we
anticipated. We originally planned to just add support for Access 2003
and ship it. But at that time we were getting very few requests for
Access 2003 functionality. Given the fact that we had a workaround for
Access 2003, we decided to "go for it" and add some additional new
functionality that we'd been planning for the future, such as a compare
feature. In retrospect, we should have just shipped an Access 2003
version before embarking on what turned out to be a major design
effort. That's where we screwed up.

Although many of our customers are able to use the workaround (i.e.
also installing Access 2002), we realize that this isn't an option for
everyone. We have lost many sales due to the fact that we don't
currently have a solution for customers who can only run Access 2003. I
think it would be accurate to say that we are "highly motivated" to
ship a high quality version 5.0 product as soon as possible.

We've been in business since 1995. Our first product, a database
analyzer named "Tracker", worked with Access 1.1. During the past 11
years, we have had a total of eight major product releases, with an
average time between releases of 16.5 months. This one's definitely
taking longer than most, but we're also setting our goals higher than
usual.

Over the years, we've had a lot of conversations with a lot of
customers. We've received many feature requests, and we've gained a
pretty good sense of the kinds of things that our customers would like
to see. Our new compare feature takes a different approach than most.
We've put a lot of time and effort into it. We think that it's going to
offer some unique benefits.

But every tool and every tool vendor has its strengths and weaknesses.
We definitely have room for improvement when it comes to predicting
release dates. People are always asking us for a definite date, and we
always do our best to be realistic, saying that we "hope" to ship by
<target date>. But after 11 years in business, I fear that we are no
better at predicting release dates today than we were at the beginning.
Your criticism of our shifting release date is quite legitimate. I lose
sleep over that issue.

Please believe me when I say that we are trying our very best to
deliver a quality product as rapidly as possible. I am very sorry for
any inconvenience that our long release schedule has caused for you and
others who may find it problematic to employ the workaround mentioned
earlier.

Bob Snyder, President
Black Moshannon Systems
http://www.moshannon.com
1-814-345-5657 (M-F 8am-5pm Eastern)

That is what you have been saying for two years. I have abandoned your
product, because it is not feasible in most cases to have multiple versions

[quoted text clipped - 67 lines]

 

[Guest]

Thank you for your candid response. Prehaps my post was abrupt, but I am
sure you understand it is frustrating to purchase a product only to have it
become obsolete in a short period of time.

I found 4.1 a useful tool. I hope 5.0 will be as well. I am, however,
reluctant to pay full price for a product I feel there should only be an
ungrade price for. My plan is to acquire an evaluation copy before I
purchase your product.

The value of any third party product is dependant not only on its quality,
but on it staying current with the product it is based on. The balance
between quality and expeniency is difficult and both are important.
Regardless of how good a product is, it has no value if it is not available.

Thank you again for your response. I look forward to evaluationg 5.0 when
it does become available.