QueryDef Destination DB (2003 SP2)

Topic

New Reply

Is there a way to access (view) the “Destination DB” property of the QueryDef object through VBA code?

I relinked some tables to a backend database that’s now in a different location. My action queries that update these tables still reference the old location. I’d like to scan all the queries via VBA to make sure I’ve got the Destination DB property fixed (rather than stepping through each of them in Design Mode and looking at the Property list). Or is there another way to efficiently update this property?

Thanks.

Reply | Quote

Replies

You can view the SQL of the query, it’ll contain a part

IN 'path+filename'

You can edit the path and filename manually, or you can use DAO to loop through the QueryDefs collection and update the SQL, something like this:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
strSQL = qdf.SQL
strSQL = Replace(strSQL, “old path and filename”, “new path and filename”)
qdf.SQL = strSQL
Next qdf
Set qdf = Nothing
Set dbs = Nothing

Reply | Quote

That’ll work… I’ll do the loop-through-the-QueryDefs thing — that’s what I was going to do with the Destination DB property anyway (until I couldn’t find it!).

Thanks, Hans!

Reply | Quote