Tom has linked his PivotTables to a separate source spreadsheet in Excel 2007. When PivotTables refresh, the source workbook is locked. How can he get this to stop?
In short, he needs to have the workbook containing the PivotTables open constantly and be able to refresh data from the source. The source workbook is constantly updated by production areas through the day. Unfortunately, his company is not willing to switch to an Access Database. So using any database is not really a viable solution.
Here is one of the connection strings to the source workbook:
In short, he needs to have the workbook containing the PivotTables open constantly and be able to refresh data from the source. The source workbook is constantly updated by production areas through the day. Unfortunately, his company is not willing to switch to an Access Database. So using any database is not really a viable solution.
Here is one of the connection strings to the source workbook:
Provider=Microsoft.ACE.OLEDB.12.0; User ID=Admin;Data Source=F:\Production\Contoso-Scorecard\Contoso.xlsx; Mode=Read;Extended Properties="HDR=YES;"; Jet OLEDB:System database=""; Jet OLEDB:Registry Path=""; Jet OLEDB:Engine Type=37; Jet OLEDB:Database Locking Mode=0; Jet OLEDB:Global Partial Bulk Ops=2; Jet OLEDB:Global Bulk Transactions=1; Jet OLEDB:New Database Password=""; Jet OLEDB:Create System Database=False; Jet OLEDB:Encrypt Database=False; Jet OLEDB:Don't Copy Locale on Compact=False; Jet OLEDB:Compact Without Replica Repair=False; Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False
The ADO connection object initialization property which controls how the database is locked, while records are being read or modified is: Jet OLEDB:Database Locking Mode
Please note:
With 'Jet OLEDB:Database Locking Mode = 0', the source spreadshseet is locked, while PivotTables update. If the property is set to 1, the source file is not locked. Only individual records (Table rows) are locked sequentially, while data is being read.
Please note:
- The first user to open the database determines the locking mode to be used while the database remains open.
- A database can only be opened is a single mode at a time.
- For Page-level locking, set property to 0
- For Row-level locking, set property to 1
With 'Jet OLEDB:Database Locking Mode = 0', the source spreadshseet is locked, while PivotTables update. If the property is set to 1, the source file is not locked. Only individual records (Table rows) are locked sequentially, while data is being read.
