Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    QueryDef Destination DB (2003 SP2)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: QueryDef Destination DB (2003 SP2)

    You can view the SQL of the query, it'll contain a part
    <code>
    IN 'path+filename'
    </code>
    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

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QueryDef Destination DB (2003 SP2)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •