Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Breaking link to Acces database (2003 sp2)

    Hello,

    I have a workbook that has 3 links to a MS Access database. I created them with ms query. Now I need to create a "frozen" situation.
    This means that the data will still be available but the possibility of updating the data should be removed.

    The data is the source of several pivot tables. Since the data is based on an import which is overwritten every month I need to have a way to keep the output of a specific month and still keep the flexibility of the pivot tables.
    Does anyone know if this is possible?

    I don't mind if this solution is in VBA but my knowledge on this subject is limited.

    Regards Marcel

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Breaking link to Acces database (2003 sp2)

    If I understand you correctly, you can right click the query tables, choose Data Range Properties, and uncheck the Save query definition option.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Breaking link to Acces database (2003 sp2)

    Hi Rory,

    I had a great laugh. What a simple solution.

    But... I need this automated so I thought well lets record a macro. It turns out the macro did not record anything at all. Can you help me on the VBA code for this?

    Regards Marcel

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Breaking link to Acces database (2003 sp2)

    I use this:
    <pre>Sub UnhookTables()
    Dim qt As QueryTable, wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
    For Each qt In wks.QueryTables
    qt.Delete
    Next qt
    Next wks
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Breaking link to Acces database (2003 sp2)

    Thanks Rory,

    Works great.

    Marcel

Posting Permissions

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