We have an Access 2007 application that is used for collecting data on shippiing and receiving. The data is then uploaded to Oracle and an AS400 application.
We are also using the database table as a datasource for Excel pivottables.
All the pivottable does is use the Access file as a data source.
The pivot table should be set to manual refresh only, because, apparently, if the pivot table is refreshing at the same moment that the Access application is actually writing to the Access file, it can make the Access application crash and that can lead to corrupt data (we think). If all users of the pivot table have it set to run automatic refresh every 5 minutes, then the conflict is more likely to occur.
Hard to imagine that these two wonderful software programs from Microsoft don’t get along better.
I am not sure if the Access application could impose file or record locking to prevent the Excel pivottable from reading the data while the Access application is doing its thing. Or perhaps the Access application can make a periodic copy of the data into a separate table that could become a new data source for the pivot table? Or is there a timeout parameter in Access that can be set so that Access won't crash? The refresh of the Excel pivottable only has to read a few thousand records and it only takes a second or two.