rename mass of queries.

  • Thread starter Thread starter Carter Harvey
  • Start date Start date
C

Carter Harvey

I have a ton of append- & make-table queries that need
renamed.. They all start "qry SR" and I need them
renamed "SR qry" for sorting purposes..

I imagine this will be a loop
"For each query in queries" ..
However, I have no idea how to address objects at this
level..

Suggestions appreciated,
Carter
 
Carter said:
I have a ton of append- & make-table queries that need
renamed.. They all start "qry SR" and I need them
renamed "SR qry" for sorting purposes..

I imagine this will be a loop
"For each query in queries" ..
However, I have no idea how to address objects at this
level..
Click to expand...

I think you can create a Sub procedure in a standard module
that you can then run from the debug window. Then general
idea of the procedure would be something like this DAO air
code:

Public Sub RenameQueries()
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb()
For Each qdf In db.QueryDefs
If Left(qdf.Name, 6) = "qry SR" Then
qdf.Name = "SR qry" & mid(qdf.Name, 7)
End If
Next qdf
Set db = nothing
End Sub
 
When I'm doing this kind of thing, it occurs to me to wonder whether "For
Each" is 'safe'.
i.e. if the process that occurs within the For Each loop actually changes
the (position of the current item in the) sequence.

In this case, since it is a rename, it is at least possible that not all of
the queries will get 'hit'. It depends on the loop processing logic of
Access.

Being a coward, I would write it as a dual loop ...
- first loop: For Each to get all the names into an array - using ReDim
Preserve to grow the array
- second loop: Do/Loop based on the array: actually do the renames, using
UBound to limit the loop

CD
 
When I'm doing this kind of thing, it occurs to me to wonder whether "For
Each" is 'safe'.
i.e. if the process that occurs within the For Each loop actually changes
the (position of the current item in the) sequence.
Click to expand...

By definition, it should be. The documentation for the Collection objects'
For Each stipulates that every object in the collection is iterated, even
when things are deleted.

The trouble comes when some Access objects don't actually obey the rules,
and this is not unusual. It's why the textbox doesn't have a Text value
unless it has the focus, and has no hWnd property.

It appears that For Each in the tabledefs collection is actually doing a

For i = 0 to Tabledefs.Count -1

which is vulnerable to the collection being reordered, which it will be if
one is deleted. And contrary to the COM rules.

In the end, there is not always logic to what works and what doesn't. After
a while, you just recognise what to use and where to steer clear, and code
defensively when you are not sure.

All the best

Tim F
 
Back
Top