How to replace a query from one DB to another

[ammodog6942]

Can anyone help me, I would like to open a db that has the code or macro to run on start up. I would like to have this code delete a query on C:\Program Files\database1\database.mdb and insert a query from C:\Program Files\database2\database.mdb into C:\Program Files\database1\database.mdb. In essense replace the query. Any help is appreciated.

 

[dhookom]

Are you changing the query name or just the SQL property of the query? If you are only changing the SQL, you can use some DAO code to create 2 database objects and then set the SQL property of the target query.

This function will change the SQL property of a query in another MDB file:

Function ChangeSQLOtherMDB(strMDBOther As String, strQuery As String, strSQL As String) As String
    Dim dbOther As DAO.Database
    Dim qd As DAO.QueryDef
   On Error GoTo ChangeSQLOtherMDB_Error

    Set dbOther = OpenDatabase(strMDBOther)
    Set qd = dbOther.QueryDefs(strQuery)
    ChangeSQLOtherMDB = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set dbOther = Nothing

   On Error GoTo 0
   Exit Function

ChangeSQLOtherMDB_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ChangeSQLOtherMDB of Module SystemUtilities"
End Function

Duane
Hook'D on Access
MS Access MVP