[RESOLVED] Automating search of entire database

[ice_531]

Hi all,


Odd request here(or so i think!). I have a need to be able to locate all query names within an entire database, that use a certain field name in the query.


a little background on this request: The developers at my company are about to launch a change to a specific field in a specific oracle table. They are no longer going to be using this field, and will have 4 other fields that will contain the data that this field currently contains.

The problem for me is we have hundreds of databases (Access), that generate hundreds of reports and/or have manual forms and systems setup in access that MAY make use of this field that is about to get changed in the next month.

So I need to be able to locate ALL queries in hundreds of databases that are currently using this field, so that when the change goes live, I can go in and switch the field out with the new fields.

I was hoping that at least the search process could be automated some how, so it could give me a least of all the queries that i need to change in any given database. I'll still have to manually make the changes I know....but at least the search process would be simplified and know how time i will have to dedicate to the changes.


Soooo if anyone knows anyway to accomplish this in MSACCESS, whether it be an application thats already available somewhere...or if someone knows some code that I could implement into VBA to do this...

Once again, I'd need to search every query within a given database, looking for a specific field name from a specific table to see if that query is using that field.

thanks!

[techgnome]

wait... so you're taking out one field and replacing it with 4 new ones? sounds like an odd change...but what about a calculated field instead? Add your 4 new fields, then edit the existing one to use the data from the 4 fields to re-assemble the data into the old format... that should then preserve any views that use it. the only time then you would run into a problem is if you have some code that attempts to update that field.

-tg

[ice_531]

wait... so you're taking out one field and replacing it with 4 new ones? sounds like an odd change...but what about a calculated field instead? Add your 4 new fields, then edit the existing one to use the data from the 4 fields to re-assemble the data into the old format... that should then preserve any views that use it. the only time then you would run into a problem is if you have some code that attempts to update that field.

-tg

The old field isn't being taken out, as it's been used for 10+ years. However the data will no longer be populated into that field, but instead will go into these other 4 fields. (But, yes I have thought of doing some sort concatenation to push the fields into 1 field for the reports) this may cause problems though due to the broader scope of the change the developers are making(and why)..

My problem isn't so much what to do about the change itself...it's how to find all the queries that will require the change.

We have ~95 databases that automate reports each day. Any given database can contain hundreds of queries and tables. So I'd like to be able to just run a search on 1 database at a time and then be told exactly which queries in said database use that field. So then when it comes time to change, i go in each database and view the list of queries i need to check.

[ice_531]

In other words; I don't want to have to go into every single query in every single database to see if it will require the change.

I want a list to be generated for each database that will tell me exactly which queries do use that field. This will save me lots of time instead of looking through ~1000 queries, I may only have to go into ~400 to make changes. (ignore arbitrary numbers above)

[ice_531]

Just wanted to show what I've got so far with this issue. So I found out that there is a system table for MSACCESS called MSysObjects. I was able to now use this to query and get the names of all user created tables or user created queries.

e.g.

SELECT MSysObjects.NAME AS Expr1
FROM MSysObjects
WHERE (((MSysObjects.[TYPE])=5) AND ((MSysObjects.[NAME]) Not Like 'MSYS*' And (MSysObjects.[NAME]) Not Like "~*"))
ORDER BY MSysObjects.NAME;

Type 5 = queries
Type 1 = tables

So, I'm not really seeing a way to get to objects within each query from that system table. I could use the results of this query in VBA I guess, to interate through each query name listed and somehow check the fields used in each query.... this is my only thought so far..

[techgnome]

not 100% familiar with access.... if the msysobject table is similar to the sys.procedures table in sql server... you should be able to query it out some how....

here's something I use in SQL Server to find functions/sprocs that contain what I'm looking for...

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%99999%'
ORDER BY Name

hmmm... nevermind... I thought irt was looking at somethibng in the procedures table... I see now it uses the object_definition function... well thats not going to help.....

surely there's a way... just need to find it...

-tg

[ice_531]

Thanks for the help TG,

It seems i'm fairly limited with ACCESS, I've seen examples of it being possible for SQL SERVER and ORACLE.

I was able to locate a function using VBA code and DAO that is working for me.

I found the code at the following site, I wont post it directly as the author of the code requests only to link to the site for re-use.

Code example found at http://www.devhut.net/2011/04/08/ms-...field-is-used/

Works wonderfully!