Can't change pivot table's Access data source - bug in Excel 2000 SP3?

Question

I have a set of Excel 2000 SP3 worksheets that have Pivot Tables that get data from an Access 2000 SP3 database created by a contractor who left our company. Unfortunately, he did all his work on his private area on the company (Novell) network and now that he has left us, the drive spec has been deleted and is invalid.

We were able to get the database files restored to our network area by our IT Service Desk people, but we now have to re-link everything to point to our group area instead of the now-nonexistent private area.

If I follow the advice given elsewhere on this site (open wizard, click 'Back' to get to 'Step 2 of 3', click 'Get Data...' I get a message that the old filespec is an invalid path and I need to check that the path name is invalid and that I am connected to the server on which the file resides.

I then click on OK and get a Login dialog with a 'Database...' button on the right. I click this and get a 'Select Database' dialog which allows me to choose the appropriate database in its correct new location.

I then click OK, which takes me back to the 'Login' screen. I can confirm that it has accepted my new location by clicking on 'Database...' as before and the NEW location is still shown.

So far so good - but if I then click on OK I get two unhelpful messages - first I get one saying that Excel 'Could not use '|'; file already in use.' - although no other files are in use. Clicking on OK takes me back to the 'Login' dialog.

Clicking OK again gives me the same message as before telling me that the OLD filespec is invalid (as if I hadn't changed anything) - but clicking on the 'Database...' button shows that the correct (NEW) database location is still selected.

Can anyone tell me a way of using VBA to change the link information without having to spend hours fighting the PivotTable Wizard - preferably similar to this way you update an Access Tabledef:-

db.TableDefs(strLinkName).Connect = strNewLink
db.TableDefs(strLinkName).RefreshLink

Thanks!

Answer 1

Install Pivot play plus (free add-in).

Edit: If that does not work, and IF your can make your new path the same lenght as the old path, you can also try to open the xls with a binary editor, and update the path manually. I once did that a long time ago when a company changed his servers and mapping, and I found it wwas the quickest way to do the job. However, it doesn't work if you change the lenght of the path string, probably because the lenght of the string is stored somewhere.
I think I used Catch22 Hexedit at the time.

Answer 2

It looks like this AddIn really does need Excel 2002 or later to work properly.

Does anyone have any other suggestions that will work for Excel 2000 SP3?

Answer 3

Select a cell within the pivot table, open the vbe (Alt+F11), go the Immediate Window (Ctl+G) and type

?activecell.PivotTable.PivotCache.Connection

This will return the connection string used by the PT currently (the wrong one). If you change the Connection property, you can point to the right place. You can probably decipher the connection string and change it yourself. But if not, there is another option. Open a new workbook, Data - External Data - New Query (or similar, I don't have 2000 anymore). Create a QueryTable that points to the database you want. Then go back to the Immediate Window and type

?Activesheet.QueryTables(1).Connection

to see what the connection string is supposed to look like. Make the PivotCache connection the same as the QueryTable connection, then Refresh.

See also http://dicks-clicks.com/excel/ExternalData5.htm#ChangeConn although that doesn't deal with Pivot Tables, many of the techniques are the same.

Answer 4

Well, after some hacking to eliminate the XL2002(+) commands, the PivotPlayPlus Add-In finally worked for me in XL2000! I've now migrated 2 workbooks successfully.

A key issue is explained in http://www.pcreview.co.uk/forums/thread-1003719.php , namely that there is an undocumented limit of 255 chars when setting the CommandText property in XL2000. My altered CommandText was over 500 chars long, so I had to shorten it.

The most obvious way of doing that is to replace all the explicitly referenced SELECT parameters in the CommandText with SELECT * and remove the now-superfluous alias name from the end of the query - but when double-checking if the parameters returned from Access had a 1:1 match with what was expected by the pivot table's query, I found that one of the columns had been missing from the Access query - so the backup-restored database wasn't quite compatible after all!

So, my method that seems to be successful with XL2000 is to

1. Make a back-up of the spreadsheet you are updating.

1a. (first time only). Install the add-in, alter all error-handling to "On Error Goto 0" so that it will definitely break immediately on error, then run the Add-in and progressively comment out all XL2002(+) commands as they are encountered, choosing paths where variables default to False if an If command is encountered.

1. Use the Add-in to show the Query SQL in the CommandText, identify the query being used, then go to the Access database and use the following code to identify the bad external links and manually correct them one-by-one.

Sub PrintLinks()

Dim i As Integer

For i = 0 To CurrentDb.TableDefs.Count - 1
    Debug.Print CurrentDb.TableDefs(i).Name, CurrentDb.TableDefs(i).Connect
Next

End Sub

Sub RefreshLink()

Dim strLinkName As String
Dim strNewLink As String
Dim db As Object
Set db = CurrentDb

strLinkName = [the "CurrentDb.TableDefs(i).Name" item to be altered]
strNewLink =  [the new value of CurrentDb.TableDefs(i).Connect]

Debug.Print db.TableDefs(strLinkName).Connect
db.TableDefs(strLinkName).Connect = strNewLink
db.TableDefs(strLinkName).RefreshLink
Debug.Print db.TableDefs(strLinkName).Connect

End Sub

1. Run the Access query with the corrected paths (within Access) and see what columns are returned.

2. Compare these column names with the query CommandText shown in the Add-In. If they don't match then do whatever is necessary to make the Access query columns exactly match the pivot's CommandText columns.

3. You are then free to alter the SELECT part of the pivot table's CommandText query to SELECT * and remove the now-superfluous alias name from the end of the query, and also change its path to the new one (this text should now be less than 255 chars), then change the paths in the other edit box in the Add-In too.

4. Click Save in the Add-In and exit. Now test that the spreadsheet can be refreshed using "[RightClick]Refreshdata" on the screen object itself. Save it.