PC Review


Reply
Thread Tools Rate Thread

Tool for Finding String in a Query's SQL Code

 
 
Mike Thomas
Guest
Posts: n/a
 
      18th Aug 2006
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


 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      18th Aug 2006
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


"Mike Thomas" <mike@ease> wrote in message
news:(E-Mail Removed)...
>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
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a
 
      18th Aug 2006
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


"Mike Thomas" wrote:

> 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
>
>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      18th Aug 2006
Thanks! I hadn't realized that Speed Ferret was dead. I guess I have a lot
of 2000 dbs to support!

Jeff

"Klatuu" <(E-Mail Removed)> wrote in message
news:90868070-0D86-4289-857F-(E-Mail Removed)...
>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
>
>
> "Mike Thomas" wrote:
>
>> 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
>>
>>
>>



 
Reply With Quote
 
=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a
 
      18th Aug 2006
Opps! I misspoke, it works through 2002. Here is a link that gives the
particulars:
http://www.speedferret.com/sfkb0041.html

"Jeff Boyce" wrote:

> Thanks! I hadn't realized that Speed Ferret was dead. I guess I have a lot
> of 2000 dbs to support!
>
> Jeff
>
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:90868070-0D86-4289-857F-(E-Mail Removed)...
> >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
> >
> >
> > "Mike Thomas" wrote:
> >
> >> 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
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Mike Thomas
Guest
Posts: n/a
 
      18th Aug 2006
Many thanks - I might try the code snippet, then the Rick Fischer product.

Mike Thomas


"Klatuu" <(E-Mail Removed)> wrote in message
news:64271896-B83E-4D37-BEE4-(E-Mail Removed)...
> Opps! I misspoke, it works through 2002. Here is a link that gives the
> particulars:
> http://www.speedferret.com/sfkb0041.html
>
> "Jeff Boyce" wrote:
>
>> Thanks! I hadn't realized that Speed Ferret was dead. I guess I have a
>> lot
>> of 2000 dbs to support!
>>
>> Jeff
>>
>> "Klatuu" <(E-Mail Removed)> wrote in message
>> news:90868070-0D86-4289-857F-(E-Mail Removed)...
>> >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
>> >
>> >
>> > "Mike Thomas" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>

>>
>>
>>



 
Reply With Quote
 
=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a
 
      18th Aug 2006
I really like Rick's product. It is an Access Add In, easy to use, and only
$39 US.

"Mike Thomas" wrote:

> Many thanks - I might try the code snippet, then the Rick Fischer product.
>
> Mike Thomas
>
>
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:64271896-B83E-4D37-BEE4-(E-Mail Removed)...
> > Opps! I misspoke, it works through 2002. Here is a link that gives the
> > particulars:
> > http://www.speedferret.com/sfkb0041.html
> >
> > "Jeff Boyce" wrote:
> >
> >> Thanks! I hadn't realized that Speed Ferret was dead. I guess I have a
> >> lot
> >> of 2000 dbs to support!
> >>
> >> Jeff
> >>
> >> "Klatuu" <(E-Mail Removed)> wrote in message
> >> news:90868070-0D86-4289-857F-(E-Mail Removed)...
> >> >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
> >> >
> >> >
> >> > "Mike Thomas" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
moshannon
Guest
Posts: n/a
 
      18th Aug 2006
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


Klatuu wrote:
> 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
>
>
> "Mike Thomas" wrote:
>
> > 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
> >
> >
> >


 
Reply With Quote
 
=?Utf-8?B?S2xhdHV1?=
Guest
Posts: n/a
 
      18th Aug 2006
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" wrote:

> 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
>
>
> Klatuu wrote:
> > 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
> >
> >
> > "Mike Thomas" wrote:
> >
> > > 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
> > >
> > >
> > >

>
>

 
Reply With Quote
 
moshannon
Guest
Posts: n/a
 
      18th Aug 2006
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)


Klatuu wrote:
> 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" wrote:
>
> > 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
> >
> >
> > Klatuu wrote:
> > > 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
> > >
> > >
> > > "Mike Thomas" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >

> >
> >


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL code works in Access SQL window but not in VBA SQL code EagleOne@discussions.microsoft.com Microsoft Access 61 5th Sep 2008 07:53 AM
A useful tool, a useful tool, a useful tool, a useful tool... Hilton Microsoft Dot NET Compact Framework 2 11th Jul 2007 11:19 PM
Finding a string within a string Sandra Microsoft Excel Discussion 0 11th Oct 2004 10:23 PM
Finding a string within a string Sandra Microsoft Excel Discussion 12 6th Oct 2004 05:32 PM
Finding Text String Within A String With A Formula Sandi Microsoft Excel Misc 5 17th Aug 2004 08:39 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:05 PM.