Breaking One Query into Two, renaming, deleting, etc.. without causing problems?

[wcrimi]

I need some advice on the appropriate technique for renaming a query without destroying existing linkages, losing joins, causing circular references or creating other problems.

Here's the latest example.

I had a very large query that was using 7 tables and queries as input. It was drawing fields from each, doing a bunch of calculations, and then passing all the relevant information onto other queries. I had to add another table to it. I decided it was getting too large and cumbersome. So I decided to break it into 2 queries. One would gather all the information and the other do all the calculations.

Let's call the original large one "A".



I made 2 copies of "A". Call them "B" and "C". B would gather information. C would use B, do calculations, and pass on results and other pertinent fields

I worked inside each of B and C until they are were doing what I wanted. I compared the results. They were perfect.

THis is what I wanted.

1. The new C query to be called A (so all the queries beneath it didn't have to change their input table name or anything else)

2. The new B query to be called something brand new as the first step in the sequence.

3. Get rid of the original A (preferably renaming it to keep as backup...just in case).


The problem was that if I renamed the original "A" to "Backup", all the queries beneath it that used A simply starting referring to "Backup". I couldn't bring teh new ones in.

So what I decided to do was delete the original A, rename the new C to A, and rename B to something else.

The end result was that the deletion essentially broke all the linkages from A to downstream and also caused some kind circular reference and joining problem that messed up my ability to work within my new calculation query. To make a long story short, I had a total disaster on my hands. Nothing was working and I didn't have a good way to change it.

Fortunately, I had good specifications and was able to recover eventually by deleting and rebuilding the calculation query and making some changes to everything that used it downstream. (no small task)

So this gets me to my question.

Given what I was trying to do, what is the correct approach for changing, renaming, or deleting queries so that you don't wind up destroying linkages, losing joins, creating circular references etc... This is not the first time I wanted to change a name or break something up and it caused me major grief.

[ranman256]

You dont.
once you names queries/tables/macros, etc, renaming destroys the links.

NOW, Ive had to do what you need also. So to help in my edit....
i wrote this to help change all my queiries if I renamed a table, without breaking my queries.

!!!!!BACKUP YOUR DB BEFORE YOU RUN THIS!!!!!

The code below will find and replace a word in every query, with out the need to
find and edit every query in the database.
You can run this in the DBUG WINDOW. ( CTL-G )
usage:
ReplaceInSql "findTxt", "replaceWithTxt" 'updates ALL queries in db

IF YOU WANT TO REPLACE ONLY certain types of queries use these code letters in the command:
"A" 'append query
"U" 'update query
"S" 'select
"D" 'delete
"M" 'make
"N" 'UNION
"X" 'crosstab

usage:
ReplaceInSql "findTxt", "replaceWithTxt", "U" 'only update queries
ReplaceInSql "findTxt", "replaceWithTxt", "M" 'only MAKE TABLE queries

Public Sub ReplaceInSql(pvFind, ByVal pvReplaceWith, Optional ByVal pvQType)
Dim db As Database
Dim qdf As QueryDef
Dim vFind, vQnum
Dim sSql As String
Const kQS = 0
Const kQD = 32
Const kQU = 48
Const kQA = 64
Const kQM = 80
Const kQN = 128
Const kQX = 16


If IsMissing(pvQType) Then pvQType = ""
Select Case pvQType
   Case "A"  'append
     vQnum = kQA
   Case "S"  'select
     vQnum = kQS
   Case "D"  'delete
     vQnum = kQD
   Case "U"  'update
     vQnum = kQU
   Case "M"  'make
     vQnum = kQM
   Case "N"  'UNION
     vQnum = kQN
   Case "X"  'crosstab
     vQnum = kQX
   Case Else
     vQnum = ""
End Select

vFind = pvFind   '"tRptDefectsBlocked"

Set db = CurrentDb
Debug.Print "--Start qry replace on:" & vFind

For Each qdf In db.QueryDefs
    sSql = qdf.SQL
    If Left(qdf.Name, 1) <> "z" And Left(qdf.Name, 1) <> "~" Then
        If InStr(sSql, vFind) > 0 Then
        
           If pvQType = "" Then  'replace ALL queries
                  GoSub REPLACEIT
           Else
                If qdf.Type = vQnum Then
                  GoSub REPLACEIT
                End If
           End If
        End If
    End If
Next
Debug.Print "--End qry replace"
Set qdf = Nothing
Set db = Nothing
Exit Sub

REPLACEIT:
    Replace2 sSql, pvFind, pvReplaceWith
    qdf.SQL = sSql
     Debug.Print getQryTypeNam(qdf.Type); " "; qdf.Name
    qdf.Close
Return
End Sub

[June7]

I use Rick Fisher's Find and Replace add-in when I have to do renaming. Costs about $50. Saved my sanity more than once.

[wcrimi]

Do you have a link to that add-in?

Any issues with the install?

[June7]

No, but a quick web search should find it. I have used it since Access 2003 without issues. There are other products, even a freebie. https://www.accessforums.net/databas...ign-41421.html

[wcrimi]

Thanks for the insights. THis forum has been so helpful. Is there a donation area or something?

[June7]

Not that I know of. You could try the Contact Us link at bottom of page and ask there.

[ssanfu]

Another option.....

If the column names for qryC are EXACTLY the same as the column names in qryA (so you don't break links), you could open both queries in SQL view,
copy the qryC SQL and paste the SQL into qryA. Immediately save qryA.

No renaming necessary. You made a back up of qryA first....right??

[wcrimi]

Another option.....

If the column names for qryC are EXACTLY the same as the column names in qryA (so you don't break links), you could open both queries in SQL view,
copy the qryC SQL and paste the SQL into qryA. Immediately save qryA.

No renaming necessary. You made a back up of qryA first....right??

I'll keep that in mind.

Yes, I did have a backup initially.