"Could not find installable ISAM" error message when refreshing pivot

[tokenas]

Hi all,

First time posting here, bear with me!

So I have a scheduled task set up on an older machine (XP, running Office 2010) that opens an Access database and runs a macro that opens a file in Excel, refreshes a pivot table, saves it as a new file name, and closes Excel. It worked perfectly until just recently.

Now, whenever the process runs (or I try to refresh manually), I get a "Could not find installable ISAM" error message. When I clear the error, it asks if I want to connect to the data source; clicking "Yes" causes the pivot table to refresh normally. Setting system warnings to off in Access, and using the Docmd.setwarnings False VBA command in the module that opens and refreshes the pivot, does not resolve the issue.

If I run the process or refresh manually on my usual machine (Win7, Office 2013), there are no issues. However, the process was originally set up and the connections made on the older machine.

From poking around online, I've found that the connection string might be the culprit; I don't offhand see any syntax issues, but I've recreated it below in case anyone sees anything.

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\Financial Analysts\GL Pivot Tables\GL Pivot Refresh\GL Pivot Tables.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;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;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

If anyone can point me in the right direction, I'd be very grateful. Thanks!!

 

[krausr79]

I've also gotten installable isam errors before, but not exactly as you have. My situation occurred when a spreadsheet pulling data from Access was opened and refreshed in excel 2010, then opened and refreshed by someone else in excel 2007. The connection string would end up somewhat different after the 2010 machine used it, though i forget the particulars. In the end, I made a 'repair connections' macro which would cut off the extra connection string stuff added by 2010. I advise looking at the connection string of something that works on the 2010 machine, and then again after running it on the 2010 machine. Then repeat for the 2013 machine and look for any differences.

 

[tokenas]

Perfect! Thanks, krausr - you're right, looks like the problem emerged when I modified the template on the 2013 machine. I didn't do anything to the connection string, but saving it under the new version was enough to break it. I found the discrepancy and got rid of it, and it seems to be working fine now.

Thanks for the speedy and useful response!

 

[sub150]

Perfect! Thanks, krausr - you're right, looks like the problem emerged when I modified the template on the 2013 machine. I didn't do anything to the connection string, but saving it under the new version was enough to break it. I found the discrepancy and got rid of it, and it seems to be working fine now.

Thanks for the speedy and useful response!

How can I modify the connection string? I have the same problem when I open it up in 2013 and save a user in 2010 gets the ISAM error.

 

[sub150]

How can I modify the connection string? I have the same problem when I open it up in 2013 and save a user in 2010 gets the ISAM error.

bump. Do I do this in a Macro?

 

[krausr79]

Here are two macros I use to work with the connection strings. The first removes the last 42 chars, which is what gets added by 2010 that creates the error. (At least that's how it works for me). The second allows for renaming of the source database since we periodically had to do so. The lengths of the connection strings are hard-coded, so if the structure of yours is different it may not work. Back up the worksheet before modifying.

Sub RepairConnections()
'if the data was refreshed in excel 2010 and then refreshed in excel 2007, the connection strings would
'be changed and the mode would switch from read-only to share-write-deny which would lock things up
'most everyone is on 2010 now
Dim cn As WorkbookConnection
Dim oledbCn As OLEDBConnection
Dim i As Long
i = 0
For Each cn In ThisWorkbook.Connections
Set oledbCn = cn.OLEDBConnection
If Right(oledbCn.Connection, 42) = "Jet OLEDB:Bypass UserInfo Validation=False" Then
i = i + 1
oledbCn.Connection = Left(oledbCn.Connection, Len(oledbCn.Connection) - 43) 'take that last part off
End If
Next
If i = 0 Then
MsgBox ("Done. No connections needed repair.")
Else
MsgBox ("Done. " & i & " connection(s) needed repair.")
End If
End Sub
Sub ChangeConnections()
'if the data was refreshed in excel 2010 and then refreshed in excel 2007, the connection strings would
'be changed and the mode would switch from read-only to share-write-deny which would lock things up
'most everyone is on 2010 now
Dim cn As WorkbookConnection
Dim oledbCn As OLEDBConnection
Dim NewBE As String
For Each cn In ThisWorkbook.Connections
Set oledbCn = cn.OLEDBConnection
If NewBE = "" Then 'get the new BE name if we haven't already
NewBE = InputBox("Enter new Back End name to connect to or blank to cancel.", "", Mid(oledbCn.Connection, 138, 30))
If NewBE = "" Then Exit Sub
End If
oledbCn.Connection = Left(oledbCn.Connection, 137) & NewBE & Right(oledbCn.Connection, 519)
Next
MsgBox ("Connections Repaired")
End Sub

 

[kaempfe]

Is there a way to fix this without a macro? We have multiple people using this file so we would need to change the file to a XLSM file. Is this the only fix?