Function to Copy and Rename Queries

[cleech]

Hello all:
I'm working on a function to copy several queries meeting certain
criteria. The following is where I ended up so far with a few
variations. Idealy I would just like to replace AA with BB in the new
query.

I usually get an error message for a type mismatch on the CopyOject
line. Should the NewName variable be declared as querydef or
something else?

Dim db As Database
Dim qdf As QueryDef
Dim NewName As String
Dim ctr As Integer
ctr = 1

Set db = CurrentDb
For Each qdf In db.QueryDefs
If InStr(1, qdf.Name, "_AA") > 2 Then
NewName = "BB" & ctr
DoCmd.CopyObject NewName, acQuery, qdf.Name
Debug.Print qdf.Name
End If
ctr = ctr + 1
Next qdf

Set db = Nothing

Any help is greatly appreciated.

Thanks

 

[Douglas J. Steele]

Instead of the CopyObject method, try:

Dim db As Database
Dim qdf As QueryDef
Dim qdfNew As QueryDef
Dim NewName As String
Dim ctr As Integer

ctr = 1

Set db = CurrentDb
For Each qdf In db.QueryDefs
If InStr(1, qdf.Name, "_AA") > 2 Then
NewName = "BB" & ctr
Set qdfNew = db.CreateQuerydef(NewName, qdf.SQL)
qdfNew.Close
Set qdfNew = Nothing
Debug.Print qdf.Name
End If
ctr = ctr + 1
Next qdf

(the qdfNew.Close and Set qdfNew = Nothing may not be 100% necessary, but
there's no harm in including them)

 

[Marshall Barton]

I'm working on a function to copy several queries meeting certain
criteria. The following is where I ended up so far with a few
variations. Idealy I would just like to replace AA with BB in the new
query.

I usually get an error message for a type mismatch on the CopyOject
line. Should the NewName variable be declared as querydef or
something else?

Dim db As Database
Dim qdf As QueryDef
Dim NewName As String
Dim ctr As Integer
ctr = 1

Set db = CurrentDb
For Each qdf In db.QueryDefs
If InStr(1, qdf.Name, "_AA") > 2 Then
NewName = "BB" & ctr
DoCmd.CopyObject NewName, acQuery, qdf.Name
Debug.Print qdf.Name
End If
ctr = ctr + 1
Next qdf

The only thing that jumps out is the missing comma before
NewName.

 

[Klatuu]

If what you want is to change _AA to _BB instead of queries named BB1, BB2,
BB3, use Doug's method with this modification:

If InStr(1, qdf.Name, "_AA") > 2 Then
NewName = Replace(qdf.Name, "_AA", "_BB")

 

[John Spencer]

If all you want to do is to change the query name then all you really need
to do is something like the following
currentdb().QueryDefs("query1").Name = "aaQuery1"

If what you want is to add a NEW query then use Doug Steele's method.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

John, I agree Doug's method is the best.
My point was that as written, the queries will benamed
BB1, BB2, BB3...
My suggested mod was to retain the original name with BB replacing AA.

 

[John Spencer]

My concern was that Doug's code (and your mod) ADDED a new query to the
querydef collection. If what the poster wanted was to rename the existing
query then the poster only could use your suggestion and just set the name.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

The way I read the post was he wanted to copy queries:

"copy several queries meeting certain criteria"

Replacing AA with BB

" would just like to replace AA with BB in the new query."

Thus my comment.

 

[cleech]

The way I read the post was he wanted to copy queries:

"copy several queries meeting certain criteria"

Replacing AA with BB

" would just like to replace AA with BB in the new query."

Thus my comment.
--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

Thank you all for the excellent responses. The code worked out great.