Find And Replace Program (1 Viewer)

[carlnewboult]

Hi All,

I have a database that has about 80 queries in it and would like to know if anyone knows of a program that can search through those queries looking for a particular title. If would be handy if it could also replace this with something of my choosing but not essential.

The reason for my request is that we have 2 systems here that have similar field names and I am hoping to save time by using a copy of a database I already have.

Any help is greatly appreciated.

Carl

 

[namliam]

You can do this using a combination of Querydefs, instr and for each.
Lookup the "Currentdb.Querydefs" in the help and look at the example given there, you can probably addapt that to suite your needs.

Good luck !

 

[DCrake]

Are you looking to change a field name in the sql?
Thi smay get complicated as you may not want to do so in certain queries. Can you be 100% certain the name exists in underlying queries. If you change them in the wrong order then they will fall over. Can you not simply change the source table? or create a new query aliasing the old name for the new name and base your root queries on the new query?

David

 

[carlnewboult]

Are you looking to change a field name in the sql?
Thi smay get complicated as you may not want to do so in certain queries. Can you be 100% certain the name exists in underlying queries. If you change them in the wrong order then they will fall over. Can you not simply change the source table? or create a new query aliasing the old name for the new name and base your root queries on the new query?

David

David,

Thank you for your help. I am looking to change the field name in the sql unfortunatly I cannot change the souce table as it is not just myself that will be running queries from it.

I had thought about creating a base query which aliases all of the tables new name for old ones but this would then mean repointing all of the queries to a new table.

 

[DCrake]

IF you were to do in VBA as a patch then you would need to do the following.

First enumerate through all the query objects in your application using QueryDefs.

As you visit each querydef you would pass the QueryDef(Name).SQL to a variable.

Then do a replace on the variable Replace(SQL,"Search For","Replace With")

Then reset the querydef .sql to be the string variable.

Loop to next querydef.


Important things to consider/remember:

Could the old name be extracted from the name of another field?
Does the name of the field already exist in another table?
Are there any spaces in the new name/old name?
Have any relationships been setup between the old name and other fields/tables?

If you go down this route make sure you take a backup first and at each significant step in the rebuild.

Good Luck

David

 

[namliam]

you can do this using a combination of querydefs, instr and for each.
Lookup the "currentdb.querydefs" in the help and look at the example given there, you can probably addapt that to suite your needs.

Good luck !

*cry*

*cry*

*cry*