Can I programatically replace things inside my queries?

[Access9001]

For instance, say I have a bunch of queries like so:

select a, b from [pathway to db.mdb].RandomQuery


and I want to programatically change them to this:

select a, b from [a new pathway.mdb].RandomQuery

 

[namliam]

currentdb.querydefs("YourQueryName").sql will retrieve your sql and you can replace stuff in there and write it back into your sql

Lookup querydefs in the VBA help and be a little creative...

 

[Access9001]

Is there a way to cycle through all queries in my db without needing the name?

 

[boblarson]

Is there a way to cycle through all queries in my db without needing the name?

This would:

Function ReplaceQueryPart(strTextToReplace As String, strTextToUse As String)
 
Dim qdf As DAO.QueryDefs
Dim strSQL As String
 
For Each qdf in Currentdb.QueryDefs
     strSQL = qdf.SQL
        If Instr(1, strTextToReplace, strSQL) > 0 Then
           strSQL = Replace(strSQL, strTextToReplace, strTextToUse)
           qdf.SQL = strSQL
           qdf.Close
        End If
Next

(untested air code)

But you might not want to change all of them so then you would need to do some sort of checking.

 

[namliam]

Lookup querydefs in the VBA help and be a little creative...

The access help is very helpfull in finding samples

 

[boblarson]

The access help is very helpfull in finding samples

And from what I've heard, it is more helpful if you turn OFF the online help.

 

[gemma-the-husky]

I decided the other day, I needed a search form, to locate where certain fields were used within queries.

I was going to extend this to forms and reports, but haven't done it yet.

You may find it useful.


View attachment DBS Search.zip

 

[vbaInet]

By the way, if you're going to use Bob's code to replace an SQL string from a querydef, you need to strip off unwanted characters which may be present

http://www.access-programmers.co.uk/forums/showthread.php?t=204437

 

[Access9001]

For large queries, that routine takes forever. Is there any way to just edit the SQL without needing to "execute" it on the backend to get all its properties when I run code? ALL I want to do is be able to mass-replace things in the SQL in a flash.

 

[boblarson]

For large queries, that routine takes forever. Is there any way to just edit the SQL without needing to "execute" it on the backend to get all its properties when I run code? ALL I want to do is be able to mass-replace things in the SQL in a flash.

1. The code I gave doesn't execute anything. It just replaces the SQL within the query.

2. If you want a faster way of doing it (don't know how much faster it will be or if at all) you can try using the free V-Tools Total Deep Search and see how long it takes it. But I don't think you can programmatically use that.

 

[Access9001]

The code works but it always seems to hang on the following

For Each qdf in Currentdb.QueryDefs
strSQL = qdf.SQL

for large queries


Basically I am moving a bunch of Access DBs to a new server and I need a way to update any hardlinks that may exist (relink tables, any links in-queries, any VBA linkages, etc)

 

[boblarson]

The code works but it always seems to hang on the following

For Each qdf in Currentdb.QueryDefs
strSQL = qdf.SQL

for large queries


Basically I am moving a bunch of Access DBs to a new server and I need a way to update any hardlinks that may exist (relink tables, any links in-queries, any VBA linkages, etc)

Well, the part you point out is needed.

Second, if I were you I would NOT hard code anything in any queries or anything. Use tables to store data/properties that may change. It is much faster to change one entry in a table than it is to change hundreds of instances where the database has to search to replace.

 

[Access9001]

Is there an easy way to do mass-replacements within tables in the same way as queries?

 

[boblarson]

Is there an easy way to do mass-replacements within tables in the same way as queries?

What do you mean "mass replacements?" It would only be ONE record in a preferences table for the pathway for your mdb. You would need to modify your queries so that they used a lookup to the table instead of the direct path. After you have that done the first time, then you would only need to change the path in the one table in the one record in the one field and all of it is done. I don't understand why you think there would be mass replacements needed in tables.

 

[Access9001]

The current network of databases is very automated and has many interlinkages -- exporting systems and so forth that have export paths listed as elements within tables and so forth.

But since this will need to go to a new server, all sorts of links will need to be updated. It's not a system I made.

 

[namliam]

You can rename Linked table sources much like the SQL above.

If you want to addapt values in queries it is update queries you are looking for.