Make Table Queries - Another Database -- How to relink?

[mmogul]

I'm working on a database where the previous designer created a series of make table queries that create a table in "Another Database". I need to move the back end databases for development and testing purposes. To do that, I have a process that relinks all tables. But my process does not look at the internals of each make table query to see if it points to a specific database so it can be changed. Does anyone have suggestions on how to approach this issue?

My thoughts are to write a process that goes through each make table query, reads the SQL, finds the IN portion of the statement (this specifies the database)that follows the INTO, and then replace that part of the statement with the new location of the database.

This could take a while to build. Perhaps there are better approaches -- or existing code.

Thanks.

 

[lameid]

Something like below ought to work... I just wrote it here so forgive me if I messed up parameter orders without intellisense. And don't forget to Add a reference to DAO if you don't have it already.

Sub ReplaceExtDBinQuery (strFileOldLoc as string, strFileNewLoc as string)

Dim db as DAO.database
dim qrys as DAO.querydefs
dim qry as DAO.querydef

set db= currentdb

set qrys = db.querydefs

For Each qry in qrys
     If instr(1,qry.SQL,strFileOldLoc) > 0 Then
          qry.SQL = Relplace(qry.SQL, strFileOldLoc, strFileNewLoc)
     End if
Next qry

End sub

 

[mmogul]

Thanks. I'll give it a try.

 

[mmogul]

Hey LameID -- thanks for your help. I got it to work. The code below was modified so it will work when the previous File Location is not known and has to be determined in code.

Public Function SetBackEndLocation_Queries(sNewPathName As String) As Boolean
'**  Changes the back end location for Make Table queries that point to an external database
Dim db As DAO.Database
Dim qrys As DAO.QueryDefs
Dim qry As DAO.QueryDef
Dim sSQL As String
Dim iStartFolder As Integer
Dim iLastSlash As Integer 'The location of the end of the original make table folder name
Dim sOldFolderName As String
Dim iCounter As Integer

Set db = CurrentDb

Set qrys = db.QueryDefs

For Each qry In qrys
'    If qry.Name = "qryTestFolderLocation" Then
 '       MsgBox "found it"
    
        sSQL = qry.SQL
        If InStr(1, sSQL, "IN '") > 0 Then 'if true, this is a make table to an external database
            iStartFolder = InStr(1, sSQL, "IN '") + 4
            iLastSlash = InStrRev(sSQL, "\")
            sOldFolderName = Mid(sSQL, iStartFolder, iLastSlash - iStartFolder + 1)
            sNewPathName = TrailingSlash(sNewPathName)
            qry.SQL = Replace(qry.SQL, sOldFolderName, sNewPathName)
            iCounter = iCounter + 1
        Else
        End If
            
 '   Else
    
 '   End If
    
    
Next qry
'MsgBox "Updated " & iCounter & "queries."
Set db = Nothing

End Function