Prevent Excel 2007 Pivottable with Access 2007 datasource from corrupting Access database?

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.
Who is Participating?
If just viewing the data in a pivot table from Access, I would use MS Query to retreive the data.

Data > Get External Data > From Other Sources > From Microsoft Query.
When you use this first time, untick the "use the Query Wizard to create/edit queries.
Then be sure to de-select the auto refresh button.
It is very similar to access to build the query.
This will always set up a ReadOnly connection, so should fix your issue.
Perhaps add a level or "robustness" by moving the tables to SQL Express (free) and connecting Excel to this.

Chris B
OK, if you set up the pivot table using DataGet External Data from Access > PivotTable

Thedefault connection string is:
Mode=Share Deny Write;

Change this to:

Select DataConnectionsPropertiesDefinitions Tab > Then change connection string.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

What is the size of your access database?
And I'm right that the access database is stored on a network drive?
jkasavanAuthor Commented:
Bembi - yes the database is on a network drive and is 84MB.

The_Barman - the connection string does not include a Mode parameter - can I just add it on to the end?

DSN=MS Access Database;DBQ=Q:\lllll\cccccc\File.mdb;DefaultDir=Q:\lllll\ccccc;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

DSN=MS Access Database;DBQ=Q:\lllll\cccccc\File.mdb;DefaultDir=Q:\lllll\ccccc;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;Mode=Read;
You will find the 'Mode' as part of your DSN setup.

Go to C:\Windows\System32\odbcad32.exe (32bit)
or C:\Windows\SysWOW64\odbcad32.exe (64 bit)

In there you will find the ability to set the ReadOnly property of the connection.

Or oyu could set up a new pivottable and use the data > add Access as described above with Mode set to read.
Just a hint what maybe a reason.

It is a known issue and problem of the past, that Access Databases can run into trouble, if they are stored on a network drive and no WINS service is running in that network.

We had such issues in the past with databases of 15MB and above...
This need not to be the reason, but it is a try....
jkasavanAuthor Commented:
Hi, The_Barman - I am looking at the User DSN tab and selecting the "MS Access Database" data source.

1. Select configure.
2. Select Advanced
3. Scroll to options ReadOnly which has a value of 0

Do I set to ReadOnly value to 1? or what?
Setting from ReadOnly 0 (False) to ReadOnly 1 (True) may fix the problem as it could be locking the database.

How was the pivot table created?
There are many ways to set up the pivot table and so many ways to leave the connection locked.
Just another comment from my side....
Setting the connection to read only is a good idea anyway....
Nevertheless, as far as I'm right, this solves the problem only partly. And I assume, this is not the only Access Database in your network.
At least for a test you may just make sure you have WINS active in the network to see, if you can reproduce the problem again. If it doesn't resolve the problem, you can uninstall it without not problems.
If you have a WINS active, keep my comment out of scope.
jkasavanAuthor Commented:
Bembi - I do not see WINS as a started service in the Services list. How do I check to see if it is active in the network?
WINS is installed on a domain controller, like DNS or DHCP
You can use IPConfig /all on the client to see, if WINS Servers are configured.
jkasavanAuthor Commented:
Thanks to The_Barman!! Setting the Mode parameter seems to have done the trick!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.