none
LIKE query fails in MS Access 2016 x64 redistributable RRS feed

  • Question

  • Hi All,

    We have upgraded from access 2010 x64 redistributable to 2016 x64 distributable.

    After installing 2016 redistributable, query is failing with HRESULT = -2147217900 ( 0x80040E14)

    CDBErrorInfo GetAllErrorInfo gives below value for HRESULT code

    pbstrDescription- [Itra L uoainerr],  pbstrSource -  McootAcs aaaeEgn

    Table FRUITS

    ID    FRUIT_NAME  FRUIT_DESCR

    1      Apple            

    2      Banana

    3      Grape

    DELETE FROM FRUITS WHERE FRUIT_NAME NOT LIKE '#*%'

    Query is executed using

    CCommand<CNoAccessor, CNoRowset> cmdDelete;

    cmdDelete.Open(......);

    NOTE: Query succeeds if table is empty

    Trying simple queries like below also fails

    DELETE FROM FRUITS WHERE FRUIT_NAME LIKE 'Apple'

    SELECT * FROM FRUITS WHERE FRUIT_NAME LIKE 'Apple'

    Same query passes if I have MS Access 2010 x64 redistributable.

    Connection string used -  Provider=Microsoft.ACE.OLEDB.12.0

    Wednesday, December 12, 2018 8:11 AM

All replies

  • Can you share your code? I can't reproduce this:

    void DoCCommandTest()
    {
     CoInitialize(NULL);
     CDataSource ds;
     ds.OpenFromInitializationString(L"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=fruits.accdb");
     CSession session;
     session.Open(ds);
     CCommand<CNoAccessor, CNoRowset> cmdDelete;
     HRESULT hr = cmdDelete.Open(session, L"DELETE FROM FRUITS WHERE FRUIT_NAME NOT LIKE '#*%'");

     CoUninitialize();
    }

    Correctly deletes fruits that don't start with #* for me, using the 2016 x64 Ace Redist, and returns S_OK.


    Shane L. Groff (Microsoft Engineer)

    Wednesday, December 12, 2018 9:30 PM
  • Thanks for the response.

    Below is the code snippet:

    CString strDBConnect = _T("Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=True; User ID=Admin; Data Source=fruits.accdb");

    HRESULT hResult = CoInitializeEx(NULL, COINIT_MULTITHREADED);

    CDataSource* pDataSource = new CDataSource;
    hResult  = pDataSource->OpenFromInitializationString(strDBConnect);

    CSession* pSession = new CSession;
    hResult  = pSession->Open(*pDataSource);

    CCommand<CNoAccessor, CNoRowset> cmdDelete;
    CString strSQL = _T("DELETE FROM FRUITS WHERE FRUIT_NAME NOT LIKE '#*%'");
    hResult = cmdDelete.Open(*pSession, strSQL);

    // here hResult vaue is -2147217900

    CoUninitialize();

    NOTE: fruits.accdb is created using Microsoft office Access 2007

    Thursday, December 13, 2018 5:08 AM
  • The only significant difference I see between my code and yours is that you specify a User ID.  Was this database created with user-level security?

    Assuming this database doesn't contain any secret fruit, would you be able to give me a copy so I can take a look?

    Can you open this database in Access 2016?



    Shane L. Groff (Microsoft Engineer)

    Thursday, December 13, 2018 10:25 PM
  • Hi,

    We have identified that query works correctly in Access 2016 x64 only when below are installed:

    1.Microsoft SQL Server 2012 Command Line Utilities - 11.1.3000.0

    2. Microsoft SQL Server 2012 Native client - 11.1.3000.0

    However for Access 2010 x64, query worked correctly without above installers.

    Could you please clarify why this dependency is introduced for Access 2016 x64?

    Is there any other alternative to make query execute correctly in Access 2016 without SQL server 2012 installers?

    Thanks

    Friday, December 21, 2018 10:43 AM
  • Can anyone explain why dependency on SQL server is introduced with Access 2016 x64 ?

    Thanks

    Wednesday, February 13, 2019 3:50 AM
  • The Ace Redist and Access have no dependency on SQL server or its components.

    Your code works fine for me, with only the x64 2016 Ace Redist installed, and no SQL Server components installed.

    Have you tried this on other machines? 


    Shane L. Groff (Microsoft Engineer)

    Wednesday, February 13, 2019 4:49 AM
  • Yes I have tried on other machines, query works only when we have SQL Cmd utils and native client is installed
    Wednesday, February 27, 2019 3:59 AM
  • What version of Windows are you using?

    Shane L. Groff (Microsoft Engineer)

    Thursday, February 28, 2019 1:22 AM