Search through all Access queries for text in SQL string

[goatamus]

I need to find a way to search through all of the queries in my Access 2007 database to change the names of more than a few variables. There are close to 900 queries I need to search through many of which may use the variables as criteria, so if I miss even 1 instance I might screw myself completely.

I found this post and it tells me exactly what I need to do. Only problem is the post was written in 2002, and I've followed the below instructions only to have it not work.

Maybe it needs a good tweaking in order to work in Access 2007. I'm not quite sure. Anyone have any ideas?

Any help would be greatly appreciated.

Thanks





Posted: 23 Jul 02

As a contractor working predominantly on databases I haven't developed, it can be a daunting task trying to find references to tables, fields or functions within a list of several hundred queries.

The following code is attached to a command button on a simple form listing all queries in the database. The record source for the form is

SELECT DateCreate, Name FROM MSysobjects WHERE Type=5 ORDER BY DateCreate DESC

There is a text box where the user enters the text to search for. The code builds a table containing the SQL string of every query, then searches for the required text, and displays those that match.



Your form also needs a procedure the set the record source back to the default.

There are off-the-shelf applications such as Speed Ferret which perform this sort of function, however some employers are too cheap to purchase them!

sub cmdFilter_Click()
Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim tdf As TableDef
Dim strSQL As String
Dim strQdf As String
 
 
On Error GoTo ErrorHandler
 
If Me.txtSearchSQL = "" Then
MsgBox "You must enter some search criteria.", _
vbInformation, "No Search Criteria"
Me.txtSearchSQL.SetFocus
End If
 
DoCmd.Hourglass True
strSQL = "SELECT DateCreate, Name FROM"
strSQL = strSQL & " MSysobjects WHERE Type = 5"
strSQL = strSQL & " ORDER BY DateCreate DESC"
 
Set db = CurrentDb()
Set tdf = db.CreateTableDef("tblQuerySQL")
 
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With tdf
.Fields.Append .CreateField("DateCreate", dbDate)
.Fields.Append .CreateField("Name", dbText)
.Fields.Append .CreateField("SQL", dbMemo)
End With
 
db.TableDefs.Append tdf
Set rsFilter = db.OpenRecordset("tblQuerySQL", _
dbOpenDynaset)
rs.MoveFirst
 
Do Until rs.EOF
strQdf = rs!Name
 
With rsFilter
.AddNew
!DateCreate = rs!DateCreate
!Name = strQdf
!sql = db.QueryDefs(strQdf).sql
.Update
End With
 
rs.MoveNext
Loop
 
Me.RecordSource = "SELECT * FROM tblQuerySQL"
Me.Filter = "SQL Like '*" & Me.txtSearchSQL & "*'"
Me.FilterOn = True
 
 
CloseFilter:
rs.Close
db.Close
DoCmd.Hourglass False
Exit Sub
 
 
ErrorHandler:
Select Case Err.Number
Case 3010
db.TableDefs.Delete "tblQuerySQL"
Err.Clear
Resume
 
Case Else
MsgBox Err.Number & ": " & Err.Description
Err.Clear
GoTo CloseFilter
End Select
End Sub

[boblarson]

Why not just use the free V-Tools. It has a replace function that you can use to do it and it is fully functional. The tool within it is called Total Deep Search.
http://www.skrol29.com/

[goatamus]

Thanks, I've downloaded it, but when I go to install it in Access I get the following error:
The add-in could not be installed because it is missing a USysRegInfo Table.

No idea what this means.

[boblarson]

How are you installing it? And did you download the one for 2007?

[goatamus]

I'm installing it on the C:/ drive, and I downloaded version V-Tools 1.74.

[boblarson]

I'm installing it on the C:/ drive, and I downloaded version V-Tools 1.74.

No, not WHERE you are installing it. But HOW. (I can't get to the V-Tools web page from work as it is blocked for some unknown reason so I can't go double-check what the process is to install and I installed mine at home quite a long time ago).

Also, you don't want to install to the C:\ root directory. You need to go to another one (don't use Program Files either because Windows 7 will not like it if you ever upgrade from XP).

[goatamus]

I'm following the instructions per the website. Same error.

[boblarson]

I'm following the instructions per the website. Same error.

You downloaded the 2007 version and not the 2010 version?

[goatamus]

Yes I did.

[drewetzel]

Bob, I work with goatamus. He's able to install it on his computer, but is getting the error message when setting up the add in in Access. Do you have any ideas?

[boblarson]

That's why I'm confused. From what I remember, to install V-Tools, you extract the files and then open the MDE file (or ACCDE file) which is in the folder the files were extracted to and then it should install it and you shouldn't have to do anything from within Access.

[goatamus]

Ok, I got it to work finally. Looks like it's going to take awhile to go through near 900 queries.

Thanks Bob!