Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links to Access tables via MS Query (Excel 2000 SR-1)

    I have inherited a number of Excel workbooks which are linked to external Access tables via MS Query.
    How can I determine what database (.mdb) and what path this query is referring to.

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

    Re: Links to Access tables via MS Query (Excel 2000 SR-1)

    The following macro, copied into a module and run, will list the connection string for each querytable in the active workbook.
    Before you run it, adapt the destination worksheet wsDestination, or make sure that column A in the first worksheet is empty.

    Sub ListQueries()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim wsDestination As Worksheet
    Dim i As Integer
    ' Adapt as needed
    Set wsDestination = ActiveWorkbook.Worksheets(1)
    For Each ws In ActiveWorkbook.Worksheets
    For Each qt In ws.QueryTables
    i = i + 1
    wsDestination.Cells(i, 1) = qt.Connection
    Next qt
    Next ws
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to Access tables via MS Query (Excel 2000 SR-1)

    Thanks Hans,

    It works like a dream!

    The follow up questions are
    1. Can I also identify the table or query?
    2. If I need to repoint to another database how can I do it without recreating the query/table in the Worksheet?
    Regards

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

    Re: Links to Access tables via MS Query (Excel 2000 SR-1)

    MSQuery creates a query, even if you selected a table in the source database. You can add a line

    wsDestination.Cells(i, 2) = qt.CommandText

    to the loop to put the SQL of the query into column B. It will not look attractive, because line ends are not displayed correctly, but you will be able to identify the source. The Connection property of the QueryTable object is read/write, so you can retrieve it, replace the name of the database (use the Replace function) and set the new value in the QueryTable object. Something like this:

    Sub ChangeDatabase()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Const stOldDatabase = "C:AccessTest.mdb"
    Const stNewDatabase = "P:ProjectWork.mdb"
    For Each ws In ActiveWorkbook.Worksheets
    For Each qt In ws.QueryTables
    qt.Connection = Replace(qt.Connection, stOldDatabase, stNewDatabase)
    Next qt
    Next ws
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to Access tables via MS Query (Excel 2000 SR-1)

    Thanks Hans,
    This has saved me a lot of headaches.
    Regards

Posting Permissions

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