CODEWITHSUNDEEP
vbaoledbisam
user20291594
user20291594

Reputation: 1

Microsoft ACE OLEDB 12.0 to 16.0

There is an excel macro which uses oledb 12.0, worked fine but now some users experiencing issues. It turned out they don't have version 12.0 anymore, only 16.0. Upon changing the code from 12.0 to 16.0 there is a new error message popping up: "Could not find installable ISAM"

This is the original piece of code:

Dim connection As Object 'New ADODB.connection
Dim rs As Object 'New ADODB.Recordset
Dim query As String '
Dim where_filter As String
Dim counter As Integer

Set connection = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")



connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fullpath & _
                ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
   
                

query = "SELECT * FROM [Sheet1$]"

Upon changing to: connection.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & fullpath & _ ";Extended Properties=""Excel 16.0;HDR=Yes;"";"

we face the "could not find installable isam" error message.

For the users who has version 12.0 the original code works and the connection string at the locals looks like this: ConnectionString : "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Folder\excelfile.xlsx;Mode=Share Deny None;Jet OLEDB:System database="""

But for the users who have only 16.0 the revised code gives the following connection string at the Locals: ConnectionString : "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\Folder\excelfile.xlsx;" : String

How I should revise the code to work with version 16?

Thank you!

Upvotes: 0

Views: 3028

Answers (1)

Tomek
Tomek

Reputation: 59

Use ACE.OLEDB.16.0 but Excel 12.0 must stay. As the driver version has little to do with Excel version :).

Watch my code:

Set @SQL = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.16.0'', 
                                ''Excel 12.0; HDR=NO; IMEX=1; Database=' + @ExcelFile + ''', 
                                ''SELECT 
                                        F1  as  [F1],
                                        F2  as  [F2] 
                                  FROM [Sheet1]'')'

Upvotes: 0

Related Questions