id=54920 When looking into this issue, the largest thing to note is: The JET provider has been deprecated as of 2002. In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC/WDAC releases do not contain Microsoft Jet, the Microsoft Jet OLE DB Provider, the ODBC Desktop Database Drivers, or Jet Data Access Objects (DAO).
NET, or a distributed component running under COM services.
Another workaround would be to update the below registry key to point to an old copy of the DLL file: [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet.0\Engines\Excel\Win32] To get an old copy of the DLL, uninstall the patch KB4041681, copy the DLL "msexcl40.dll" from C:\Windows\Sys WOW64\msexcl40to a new location say "C:\msexcl\msexcl40.dll".
You can now modify the registry key [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet.0\Engines\Excel\Win32] to point to the new DLL location "C:\msexcl\msexcl40.dll" (by default it’d be pointing to C:\Windows\Sys WOW64\msexcl40.dll) There is a public forum discussion where many customers found various ways to work around this issue.
I've successfully created and OLE DB "IBMDASQL" connection, and am able to pull data some data, but i'm running into an issue when i try to pull data from a very large table This runs fine, and returns a count of 170 million: I'm a Microsoft guy, but my AS/400 guy says that there is an index on the 'processed' column and that locally, that query run instantaneously. I found a table with only 68 records in it, and was able to run this query in about a second: I have had to fight this battle many times. 1) Stage your data from the AS400 into SQL server where you can optimize your indexes 2) Ask the AS400 folks to create logical views which speed up data retrieval, your AS400 programmer is correct, index will help but I forget the term they use to define a "view" similar to a sql server view, I beleive its something like "physical" v/s "logical". Thirdly, 170 million is a lot of records, even for a relational database like SQL server, have you considered running an SSIS package nightly that stages your data into your own SQL table to see if it improves performance?
I recommend following the suggestions in the IBM Redbook SQL Performance Diagnosis on IBM DB2 Universal Database for i Series to determine what's really happening.