How do I search for a field in a query?

  • Thread starter Thread starter Diarmuid
  • Start date Start date
D

Diarmuid

Hi
Somewhere in the database, there is a query,that is setting LinkType to
N. Is there a way to search for this query?
I have Office 2000, but I have access to PCs with Office XP and Office
2003, if there is a way on the newer versions of office.
Thanks
Diarmuid
 
Note: only certain kinds of queries can change (or set) values in a table.
Update queries are the most likely suspects followed by Append and MakeTable
queries. Update Queries appear in the database window with a + icon. If you
only have a few queries showing this icon, reviewing them manually may be
easiest/quickest.

A non- code solution:

Use the Documenter (Tools>Analyze>Documenter). Select all queries (or just
all Update queries). Under options make sure "SQL" is selected (you can
deselect everything else). Run the report. Export to rtf format. Open that
file in Word and do a Search on 'LinkType = "N"' (the full phrase will most
likely be something like: SET myTable.LinkType = "N", assuming that the
culprit is an update query.)

Code solution:

Sub FindStringInQuery(strToFind As String)
Dim db As dao.Database
Dim qdf As dao.QueryDef

Set db = CurrentDb

For Each qdf In db.QueryDefs
If InStr(qdf.SQL, strToFind) > 0 Then
Debug.Print qdf.Name
End If
Next qdf

Set qdf = Nothing
Set db = Nothing
End Sub

FindStringInQuery("LinkType = " & chr(34) & "N" & chr(34))

The names of any queries with the specified phrase should appear in the
debug window.

HTH,
 
Hi
Somewhere in the database, there is a query,that is setting LinkType to
N. Is there a way to search for this query?
I have Office 2000, but I have access to PCs with Office XP and Office
2003, if there is a way on the newer versions of office.
Thanks
Diarmuid
Click to expand...

There's no easy way to do this in Access. You could write VBA to loop
through the Querydefs collection, and use Instr to search in the SQL
strings.

It may be worth buying one of the good third-party search & replace
tools:



John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Hi
Somewhere in the database, there is a query,that is setting LinkType to
N. Is there a way to search for this query?
I have Office 2000, but I have access to PCs with Office XP and Office
2003, if there is a way on the newer versions of office.
Thanks
Diarmuid
Click to expand...

Sorry! Hit the wrong key: the third-party tools are

Free:
http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top