none
Disconnect MS Query/Excel Pivot Table after Refresh RRS feed

  • Question

  • Hi,

    How do I disconnect an Excel Pivot table, which gets data via an MS Query, from an Access 2003 database?

    I have an Excel pivot table that is populated from an Access database, via MS Query. Once the user right clicks the pivot tabe and refreshes the data it seems to still have a connection to the database.

    In case I am looking at this the wrong way, my problem is that once a user refreshes the pivot table, any code I run which uploads records into a table in the database, using a recordset, errors. "runtime error 3045, file already in use".

    Any ideas?



    • Edited by Gazza101 Friday, August 10, 2012 3:00 PM
    Friday, August 10, 2012 11:25 AM

Answers

  • I have discovered already that if I change mode to Read, it seems to be ok.

    Is this the same as what you have suggested (Mode=Share Deny Write) or are there some differences?

    thanks

    • Marked as answer by Gazza101 Friday, September 28, 2012 9:54 AM
    Thursday, September 27, 2012 9:26 AM
  • Yes.The mode you used will allow to read and mine will allow share but deny write.Both means same.

    All the best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Gazza101 Friday, September 28, 2012 9:55 AM
    Thursday, September 27, 2012 11:17 AM
    Answerer

All replies

  • I suppose you are using saved Query(.qry,.odc etc) .In that case data directly input to Pivot Table.If you disconnect How Pivot Table will get data.It is not possible also in Excel.

    But if you import data to worksheet table via query and using that data, then it is possible

    Select any cell of table->Click connection under connection->Click Remove tab( It is in right pan.)

    Now the table is disconnected and any Pivot Table dependent upon it also disconnected.

    [If you are using VBA then if possible share the code we shall make necessary changes]


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, August 11, 2012 6:25 AM
    Answerer
  • Sorry for delay in coming back.

    I create the pivot table by clicking on the following in Excel 2010:

    Data, From Access, select the Access database, select table in the database, Pivot Table Report.

    Tuesday, September 25, 2012 8:36 AM
  • Select any cell of that pivot->Go to Data->Connections Group->Property->Definition Tab->Connection String.

    In connection string you will find Mode argument.Change the mode to

    Mode=Share Deny Write;

    Or you can paste the string,I shall edit.

    Hope this will solve the issue....


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, September 26, 2012 12:01 PM
    Answerer
  • I have discovered already that if I change mode to Read, it seems to be ok.

    Is this the same as what you have suggested (Mode=Share Deny Write) or are there some differences?

    thanks

    • Marked as answer by Gazza101 Friday, September 28, 2012 9:54 AM
    Thursday, September 27, 2012 9:26 AM
  • Yes.The mode you used will allow to read and mine will allow share but deny write.Both means same.

    All the best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Gazza101 Friday, September 28, 2012 9:55 AM
    Thursday, September 27, 2012 11:17 AM
    Answerer
  • that's great, thanks for you assistance.
    Friday, September 28, 2012 9:54 AM