Full database search for SQL text strings (array)

[isladogs]

I have been developing code to search the SQL used in all database items for selected text strings.

I would appreciate any help or guidance in doing this for the code in standard modules

I'm hoping to use this partly to help tidy up some old & poorly designed legacy field names (including spaces, % & ?) in a large database which I inherited and then took over development work. This is currently used by approx 500 staff in several UK schools for over 15 years. It has an Access front end (approx 140MB) with a SQL Server backend (typically up to 1.4GB)

The starting point was a very useful function posted by Guus2005 in the code repository last year:
https://www.access-programmers.co.uk/forums/showthread.php?t=197449&highlight=SearchInQueryDefs2

This code covered forms (including form modules) & queries
I've since expanded this to include SQL in reports / report modules & table field names. So far, so good.

It works very quickly on the above database - e.g. approx 45 seconds to search all these database items. (approx 300 tables, 1200+ queries, 800+ forms, 600 reports) or less than 20 sec if table fields are excluded

See example screengrabs attached:

When I've completed the code, I intend to upload it to the sample databases area for other users.

However, to finish the task, I would now like to extend the search tool to include text strings in all standard modules but am finding this much harder. Despite searching various forums etc, I've been unable to make much progress.

Yes I know I can use add-ins such as V-Tools or just search using the VBE but I'd like to make this a complete package to cover all database items.

As a later step, I may extend this to finding & replacing text strings but dealing with modules is a higher priority

Any help would be much appreciated

 

[sneuberg]

If you want to do textual processing in the modules I suggest checking out the VBA Extensibility library to see if that offers anything that will help you. This web page will give you some examples of what you can do.

 

[isladogs]

Hi Steve

Many thanks for the link to Chip Pearson's site- I've used it before and its excellent but as its primarily for Excel I'd overlooked it this time.

I'm already using VB Extensibility but its great to have a clear guide to this library

However thanks to your prompt, I've already found some useful items on there:
- Listing All Modules In A Project
- Listing All Procedures in a Module
- Searching For Text In A Module

So hopefully all I need to do is modify code to include arrays & add a loop for all modules. Wih luck it will alos

 

[isladogs]

Autocorrect?

With luck it will also be quick to run ...

Colin

 

[Galaxiom]

Reinventing the wheel?

I thought V-tools Total Search already does cover everything.

 

[isladogs]

Possibly so but I'd already done the search tool work before I found out about V-Tools. Also V-Tools doesn't seem to code with arrays as far as I know

Having got this far, I'd like to finish it.
Anyway, its all useful skills to learn ...

Colin

 

[The_Doc_Man]

If I recall correctly, a module consists of lines (that are internally numbered 1-65535, or something like that). Each numbered line corresponds to a single line of VBA code. Because they (apparently) use an internal 16-bit number for counting, they are limited in size. But that's OK because you can have more than one.

The search and parsing issues are:

* For a form or report, the property .HasModule tells you if there is a class module (singular) for the given form/report. If so, it is accessed as {form-name}.Module, where the {form-name} depends on whether you have it open or not at the time.

* The Modules.Count should tell you how many general modules exist in the database.

* Modules.Name tells you the name of module n. I believe that since this is using the COM specification, n is 0-based, so if you have n modules they are 0 to n-1.

* {module}.Lines.Count tells you the number of lines in the module.

* {module}.Line is a string that contains line n. Again, probably 0-based.

* Len( {module}.Line ) is the size of the string (which you could have guessed given the earlier discussion.)

I believe that where the line ends with "& _" (to signify that the next line is a continuation), the next line still has its own separate number.

From there, you need a parser module to break up the line for its contents, in essence to "tokenize" the line. The tricky part is the parser. I don't have my parser module at the moment so can't post what I used, but there is a way to do this that isn't absolutely ugly. If you can use the SPLIT function to break a line at its parts based on a "space" character, you can see the various things on the line one at a time, though object.property and record!field will show up as single tokens in this case.

 

[Galaxiom]

For a form or report, the property .HasModule tells you if there is a class module (singular) for the given form/report. If so, it is accessed as {form-name}.Module, where the {form-name} depends on whether you have it open or not at the time.

Open or not, the forms are items in the CurrentProject.AllForms collection. Similarly CurrentProject.AllReports for reports. Loop though these collections testing the IsLoaded property will reveal which items are open.

The HasModule property is only available for opened forms and reports via the Forms and Reports Collections.

Forms can also be referred to via their modules:
Form_formname
HasModule property becomes accessible because this reference loads a hidden instance of the form into the Forms collection.
This reference should never be used.

CurrentProject.AllModules collection holds the Standard and Class Modules.

 

[The_Doc_Man]

Thanks for the clarification, Greg. It was so long ago that I used this technique that the "AllModules" collection didn't exist. Neither did "CurrentProject" - and that should tell you how long ago it was that I actually used this method.

 

[isladogs]

Hi

Just to update those who've contributed to this thread, I've now successfully added code to search module code for SQL text strings. I had already dealt with forms/queries/reports & tables.

Its fast & accurate & I'm pleased with it.

The search results are listed in a form listbox from which selected items can be:
1. Viewed in a form or report showing the underlying SQL (or table field list)
- see screenshots attached
2. Opened in design view (or for module code - the function is opened for viewing)

As a final touch, I'd like to highlight the search string(s) in the form or report controls using either conditional formatting or VBA. See 3rd screenshot which I've done in Word.
I think I've done this before many years ago in Access but can't remember how. Any ideas?

 

[isladogs]

[SOLVED] Re: Full database search for SQL text strings (array)

Hi again

Highlighting of search strings now added successfully to a form and report showing the underlying SQL - see attached screenshots
Many thanks to C J London for help in finding a solution to that issue

I will tidy up the code & add this to the sample databases section in case it is useful to other users

Hi

Just to update those who've contributed to this thread, I've now successfully added code to search module code for SQL text strings. I had already dealt with forms/queries/reports & tables.

Its fast & accurate & I'm pleased with it.

The search results are listed in a form listbox from which selected items can be:
1. Viewed in a form or report showing the underlying SQL (or table field list)
- see screenshots attached
2. Opened in design view (or for module code - the function is opened for viewing)

As a final touch, I'd like to highlight the search string(s) in the form or report controls using either conditional formatting or VBA. See 3rd screenshot which I've done in Word.
I think I've done this before many years ago in Access but can't remember how. Any ideas?