ODBC export to Excel fails under Windows 7, Window

问题:

I just created some code (at the bottom) from scratch that shows a simple Excel export. The code fails with an exception when database.OpenEx is called.

The exception shown is:

Reservierter Fehler (-5016); es gibt keine Meldung für diesen Fehler.
Ungültiges Attribut für die Verbindungszeichenfolge. CREATE_DB
Ungültiges Attribut für die Verbindungszeichenfolge. CREATE_DB
Ungültiges Attribut für die Verbindungszeichenfolge. CREATE_DB
Ungültiges Attribut für die Verbindungszeichenfolge. CREATE_DB
Allgemeine Warnung Registrierungsschlüssel 'Temporary (volatile) Jet DSN for process 0x844 Thread 0x1850 DBC 0xab824c Excel' kann nicht geöffnet werden.
Ungültiges Attribut für die Verbindu

The english translation would be something like "Reserved Error" and "Invalid connection string attribut"!

We can repro this on Windows 7, Windows 8.1 and Windows 10. We suggest that there is a problem with a Windows security update, but we are not sure. Similar code worked for years.

Can anybody see failures in the connection string?

Can anybody repro this problem?

EDIT: Windows 7 seams to be affected too.

The following security patches causes this problems:

Windows 7   KB4041681
Windows 8.1 KB40416393
Windows 10  KB4040724
            KB4041676

Here the code (the code is just a fast copy from Codeproject). My only change was to make it unicode compatible.

CDatabase database;
CString sDriver = _T("MICROSOFT EXCEL DRIVER (*.XLS)"); // exactly the same name as in the ODBC-Manager
CString sExcelFile = _T("demo.xls");                // Filename and path for the file to be created
CString sSql;

TRY
{
  // Build the creation string for access without DSN
  sSql.Format(_T("DRIVER={%s};DSN='';READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),
            sDriver.GetString(), sExcelFile.GetString(), sExcelFile.GetString());

  // Create the database (i.e. Excel sheet)
  if (database.OpenEx(sSql,CDatabase::noOdbcDialog))
  {
    // Create table structure
    sSql = _T("CREATE TABLE demo (Name TEXT,Age NUMBER)");
    database.ExecuteSQL(sSql);

    // Insert data
    sSql = _T("INSERT INTO demo (Name,Age) VALUES ('Bruno Brutalinsky',45)");
    database.ExecuteSQL(sSql);

    sSql = _T("INSERT INTO demo (Name,Age) VALUES ('Fritz Pappenheimer',30)");
    database.ExecuteSQL(sSql);

    sSql = _T("INSERT INTO demo (Name,Age) VALUES ('Hella Wahnsinn',28)");
    database.ExecuteSQL(sSql);
  }

  // Close database
  database.Close();
}
CATCH_ALL(e)
{
    e->ReportError();
    e->Delete();
}
END_CATCH_ALL;

回答1:

The problem arises in fact due to a bug in the security updates. Currently I see no other solution than to uninstall, the security patch or using another export format.

Affected patches are:

Windows 7 SP1 and Windows Server 2008 R2 SP

KB4041681 -- 2017-10 Security Monthly Quality Rollup for Windows 7 for x86-based Systems KB4041678 -- 2017-10 Security Only Quality Update for Windows Embedded Standard 7 for x64-based Systems

Windows 8.1 and Windows Server 2012 R2

KB4041693 -- 2017-10 Security Monthly Quality Rollup for Windows 8.1 for x86-based Systems KB4041687 -- 2017-10 Security Only Quality Update for Windows 8.1 for x86-based Systems

Windows 10 and Windows Server 2016 (version 1607)

KB4041691 -- 2017-10 Cumulative Update for Windows 10 Version 1607 and Windows Server 2016

Windows 10 and Windows Server 2016 (version 1703)

KB4041676 -- 2017-10 Cumulative Update for Windows 10 Version 1703

There are multiple threads in other communities (Tectnet, Answers, Social MSDN) discussing the same problem without any workaround except uninstalling the patch.

Edit (2017-11-21): For Windows 10 the bug is fixed with KB4048955!

回答2:

I got exactly the same problem since the Windows update from 12/10/2017

Information below solve the problem on Win7 but problem is not solved on Win10. on Win10, it is ::SQLConfigDataSource(hwndParent, fRequest, sDriver, sAttributes) which generate "Unhandled exception"

SOLUTION for Win7:

I have additional parameters: FIL=Excel 2000,DriverID=790 DRIVER={Microsoft Excel Driver (*.xls)}

It seems to be solved using: FIL=Excel 12.0,DriverID=1046 DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}

This will work if you have a version of Excel compatible with these parameters. You can try versions between Excel 2000 and Excel 12.0, too.

For PC with only Excel 2000 so new parameters do not work at first : To solve the problem I install AccessDatabaseEngine_X64.exe from Microsoft Download; this enabled the use of the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

Example of parameters:

m_sDsn ="DRIVER={Microsoft Excel Driver (*.xls)};DSN='ODBC_NameXls';FIRSTROWHASNAMES=1;READONLY=TRUE;CREATE_DB="Excelfilename.xls";DBQ=Excelfilename.xls;FIL=Excel 2000;DriverID=790";

m_sDsn ="DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN='ODBC_NameXls';FIRSTROWHASNAMES=1;READONLY=TRUE;CREATE_DB="Excelfilename.xls";DBQ=Excelfilename.xls;FIL=Excel 12.0,DriverID=1046";

m_Database->OpenEx(m_sDsn, CDatabase::openReadOnly | CDatabase::noOdbcDialog);