OLEDB 12.0 Connection compatible with both Excel 2010 and Excel 2013

Question

I'm trying to make a connection to MS Access database via Microsoft ACE OLEDB 12.0. I'd like to make it work on both Excel 2010 and Excel 2013, so that the end user would just refresh connection to get new data. The problem is that Excel 2013 adds 2 properties to the connection string that makes the connection unusable for the Excel 2010 user. Is there a way to prevent Excel 2013 from adding those, or to make Excel 2010 ignore them? The file with the connection would be on network drive, and would be used by both Excel 2010 and 2013 users.

EXCEL 2010:

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=USER;
Data Source=path1.mdb;Mode=Read;
Extended Properties="";
Jet OLEDB:System database=path2.mdw;
Jet OLEDB:Registry Path="";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;
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

EXCEL 2013:

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=USER;
Data Source=path1.mdb;Mode=Read;
Extended Properties="";
Jet OLEDB:System database=path2.mdw;
Jet OLEDB:Registry Path="";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;
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

As you can see Excel 2013 add 2 more lines, that makes this connection unusable. I've considered adding VBA code to change connection string before refreshing data, but if there's a way not to get vba involved that'd be great.

EDIT: When used in Excel 2010 with those extra lines Excel gives "Could not find installable ISAM" error.

Are you getting an error? Normally, those lines don't cause any trouble, they're just passed to the Access Database Engine and your Excel installation shouldn't influence anything. — Erik A
when used in Excel 2010 with those extra lines it gives "Could not find installable ISAM" error — Tomek S
How are you generating and applying those connection strings? (I would have thought you could just edit and save them.) — Andy G
It is also worth pointing out to readers that you are using an Access 2003 file format database. — Andy G
@AndyG connection string is generated after completing setup of a connection. In this process I've only filled the User ID, Data Source and System database fields, the rest is set on default values. I know that removing those extra lines fixes the problem, but it's only temporary, when Excel 2013 user refreshes this connection, it adds them again. As mentioned in original post, the file with the connection is used by both E2010 and E2013 users, so everytime E2010 would use it after E2013 he'd have to remove those lines from definition of each of the connections — Tomek S

June7 June7 · Accepted Answer · 2019-02-12T20:35:11

Don't see how cannot involve VBA.

Conditionally run the 2 lines if Excel version is appropriate. Use Application.Version to determine Excel version. This returns 14.0 in Excel 2010. I think Excel 2013 is version 15.0.

If Application.Version > 14 Then
    Jet OLEDB:Limited DB Caching=False;
    Jet OLEDB:Bypass ChoiceField Validation=False
End If

OLEDB 12.0 Connection compatible with both Excel 2010 and Excel 2013

1 Answers