Change Table name with Find/Replace

[QuantifyRisk]

I have a DB with 40 or so tables. They were linked to tblPrem and I renamed it to tblPremFinal and now the queries are all messed up. This name change needs to happen but there's too many queries to do manually. Does anyone know how to code it in VBA to do this:
1. Open query as SQL
2. Replace all instances of [tblPrem] with [tblPremFinal]
3. Go to next query

I know it can be done because I have seen it but I am not sure of the coding.

[aytee111]

Access should do it for you - Options>Current Database>Name AutoCorrect Options

[QuantifyRisk]

Yeah, I thought it would. This setting is checked but it's not doing it for some reason.

[Micron]

Table, query and field names are not corrected with Name Auto Correct. Nor does it handle form, report or control name changes.
Life might be easier if you just fell back to the original table name. You can always add notes/details to table properties if you need a reminder of what it's for instead of changing the name to something that does that for you.

I guess if you search, you'll find code for such a task, but I'd need a good reason to not fall back to the original name. This is going to need you to reference the queries collection, loop through them all while (one at a time) creating a QueryDef and using the Replace function to over-write the table name, then saving the qdef. Repeat as necessary. One wrong character, missing space/comma/whatever, and you are no further ahead. You would be wise to back up the db before executing such a procedure.

[ssanfu]

Try this on a copy of your dB.
I say again: Try this on a COPY of your dB.

Public Sub ExportLookaheads()
    Const sTableOldName As String = "tblPrem"
    Const sTableNewName As String = "tblPremFinal"

    Dim qdf As QueryDef
    Dim tmpSQL As String


    'loop through Query Collection
    For Each qdf In CurrentDb.QueryDefs
        tmpSQL = vbNullString   'clear variable
        If Left(qdf.Name, 1) <> "~" Then   'ensure the query is not deleted
            tmpSQL = qdf.SQL   'get query SQL

            'check to see if the SQL contains sTableOldName but not sTableNewName
            If InStr(1, tmpSQL, sTableOldName) And Not InStr(1, tmpSQL, sTableNewName) Then
                tmpSQL = Replace(tmpSQL, sTableOldName, sTableNewName)
                qdf.SQL = tmpSQL  'save the modified SQL
                Debug.Print qdf.Name & " -- " & tmpSQL    'print changed SQL to immediate window
            End If

        End If
    Next qdf

    Set qdf = Nothing

End Sub

I did say to try this on a copy of your dB.... didn't I??????

Good luck