Make-Tables and global changes to path destinations

[Massinova]

I have an access database with many "make-table" queries that have accumulated in size/number over time. All queries originate from this one access database and the "new-tables" are directed to another database. I have run into new situation, wherby I need to redirect/change the make-table paths to other databases.

Is there away to globally change the destination paths to the new data-bases that will receive the new-tables, without individually editing each of the make-table queries?

 

[AlexMidd]

Are the destination tables linked to within the database where the queries reside, or how are they referenced at the moment?

Have fun! )

Alex Middleton

 

[Massinova]

The destination tables are not linked or joined to the "primary" database. The "Make-Table" queries have one thing in common, and that is the destination path to the outgoing database are the all the same.

The only visible solution I can see at the moment is to edit the MsSysQueries table, but I don't think that is possible ...at least I haven't been able to do it.

 

[Massinova]

I'm presuming from the lack of responses, that there is no solution to this problem?

 

[JerryKlmns]

Massinova,

You could try VBA. Add a module, on Tools->References add DAO 3.6 Library. Paste the following code. Press Ctrl+g and type there
Call NewPathy {Press Enter}

Sub NewPathy()

Dim myDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim myOldPath As String
Dim myNewPath As String
Dim strSQL As String

myOldPath = "\\OldServer\MyFolder\TheDB.mdb"
myNewPath = "\\NewServer\MyFolder\TheDB.mdb"

Set myDB = CurrentDb

For Each qdf In myDB.QueryDefs
    strSQL = qdf.SQL
    strSQL = Replace(strSQL, myOldPath, myNewPath)
    qdf.SQL = strSQL
Next

Set myDB = Nothing

End Sub

After the execution of the code all

 

[Massinova]

Thanks a million Jerry, I'll give this a shot ...I'll let you know how it works

 

[Massinova]

Using the above code, I 'm getting a run-time error '3067': "Query input must contain at least one tbale or query"

I'm not exactly sure how or where to make the code find the queries that need to be changed (which is happens to be all). Any suggestions?