Help With Find Replace in Access

[koupolga]

Hello!

Im trying to do something with my database and Im not even sure if its possible. I have a finished database with about 30 queries all updating the same table. I would like to make an exact copy of this database but I would like all of the new 30 queies to update a table with a different name. All Im trying to do is replace one table with another in all the 30 queries. Is that possible at all?

Thanks so much! Im starting to drive myself up the wall.

best wishes O.K.

[MrDeej]

Yes. In a way.

If your new tables overwrites the name of the old table, all queryes will point to new table.

[jimatqsi]

Try this:
1.Back up the entire database.
2. Verify that you backed up the entire database
3. Run this code

Dim intTbnameBegin As Integer
Dim strOldTbName As String
Dim strNewTbName As String
Dim strOldQryText As String
Dim strNewQryText As String
Dim strQryName As String
        
strOldTbName = "OldTableName"
strNewTbName = "NewTableName"

For Each qdf In CurrentDb.QueryDefs ' for every query
    strQryName = qdf.Name
    If Left(qdf.Name, 1) <> "~" Then  ' ignore systems stuff
        If intTbnameBegin <> 0 Then   ' if this qry has the table name replace it and rewrite it
            strNewQryText = Replace(strOldQryText, strOldTbName, strNewTbName)
            CurrentDb.QueryDefs.Delete (strQryName)
            Set qdfNew = CurrentDb.CreateQueryDef(strQryName, strNewQryText)
        End If
    End If
Next qdf

Let me know if it worked.

Jim