locked
ODBC to MSAccess database in Windows 7 or Vista RRS feed

  • Question

  • I have been using ODBC to access a MSAccess database by a script language called CSL and it has always worked excellent. I set up a ODBC key (lets say "SomeKey") pointing at a MSAccess .mdb file using the Microsoft MSAccess driver that comes with Windows.I then connect from the script by

    daxConnect('ODBC','SomeKey');

    This works and has always worked fine untill I  use it in Vista or Windows 7.  When I use it in Vista or 7 I get

    ODBC error in SQLSetGetInfo:

    [Microsoft][ODBC Microsoft Access Driver] Invalid string or buffer length

     

    If I use the in controlpanel datasources(ODBC) tracker i get

     

    With an OS that works

     

    Engine          7a8-2f4 ENTER SQLConnectW
      HDBC                00E01830
      WCHAR *             0x00E02698 [      -3] "SomeKey\ 0"
      SWORD                       -3
      WCHAR *             0x74624EC8 [      -3] "******\ 0"
      SWORD                       -3
      WCHAR *             0x74624EC8 [      -3] "******\ 0"
      SWORD                       -3

    Engine          7a8-2f4 EXIT  SQLConnectW  with return code 0 (SQL_SUCCESS)
      HDBC                00E01830
      WCHAR *             0x00E02698 [      -3] "SomeKey\ 0"
      SWORD                       -3
      WCHAR *             0x74624EC8 [      -3] "******\ 0"
      SWORD                       -3
      WCHAR *             0x74624EC8 [      -3] "******\ 0"
      SWORD                       -3

    Engine          7a8-2f4 ENTER SQLGetInfoW
      HDBC                00E01830
      UWORD                       46 <SQL_TXN_CAPABLE>
      PTR                 0x02CEF780
      SWORD                        0
      SWORD *             0x02CEF702

    Engine          7a8-2f4 EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
      HDBC                00E01830
      UWORD                       46 <SQL_TXN_CAPABLE>
      PTR                 0x02CEF780 (2)
      SWORD                        0
      SWORD *             0x02CEF702 (2)

    Engine          7a8-2f4 ENTER SQLSetConnectAttr
      SQLHDBC             00E01830
      SQLINTEGER                 102 <SQL_ATTR_AUTOCOMMIT>
      SQLPOINTER          0x00000000
      SQLINTEGER                   0

    Engine          7a8-2f4 EXIT  SQLSetConnectAttr  with return code 0 (SQL_SUCCESS)
      SQLHDBC             00E01830
      SQLINTEGER                 102 <SQL_ATTR_AUTOCOMMIT>
      SQLPOINTER          0x00000000
      SQLINTEGER                   0

    Engine          7a8-2f4 ENTER SQLAllocHandle
      SQLSMALLINT                  3 <SQL_HANDLE_STMT>
      SQLHANDLE           00E01830
      SQLHANDLE *         00A889F8

    Engine          7a8-2f4 EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
      SQLSMALLINT                  3 <SQL_HANDLE_STMT>
      SQLHANDLE           00E01830
      SQLHANDLE *         0x00A889F8 ( 0x00e01f80)

    Engine          7a8-2f4 ENTER SQLPrepare
      HSTMT               00E01F80
      UCHAR *             0x00A74A78 [      -3] "SELECT count(*) FROM USER WHERE Pin ='1111'\ 0"
      SDWORD                    -3

    With Vista or 7

    Engine          8e4-b74 ENTER SQLConnectW
      HDBC                0x0161DD30
      WCHAR *             0x0168D190 [      -3] "SomeKey\ 0"
      SWORD                       -3
      WCHAR *             0x6C018634 [      -3] "******\ 0"
      SWORD                       -3
      WCHAR *             0x6C018634 [      -3] "******\ 0"
      SWORD                       -3

    Engine          8e4-b74 EXIT  SQLConnectW  with return code 0 (SQL_SUCCESS)
      HDBC                0x0161DD30
      WCHAR *             0x0168D190 [      -3] "SomeKey\ 0"
      SWORD                       -3
      WCHAR *             0x6C018634 [      -3] "******\ 0"
      SWORD                       -3
      WCHAR *             0x6C018634 [      -3] "******\ 0"
      SWORD                       -3

    Engine          8e4-b74 ENTER SQLGetInfoW
      HDBC                0x0161DD30
      UWORD                       46 <SQL_TXN_CAPABLE>
      PTR                 0x0320F744
      SWORD                        0
      SWORD *             0x0320F6B4

    Engine          8e4-b74 EXIT  SQLGetInfoW  with return code -1 (SQL_ERROR)
      HDBC                0x0161DD30
      UWORD                       46 <SQL_TXN_CAPABLE>
      PTR                 0x0320F744
      SWORD                        0
      SWORD *             0x0320F6B4

      DIAG [HY090] [Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer length  (84)

    Engine          8e4-b74 ENTER SQLGetDiagRecW
      SQLSMALLINT                  2 <SQL_HANDLE_DBC>
      SQLHANDLE           0x0161DD30
      SQLSMALLINT                  1
      SQLWCHAR *          0x0320F3EC
      SQLINTEGER *        0x0320F448
      SQLWCHAR *          0x01629870
      SQLSMALLINT                513
      SQLSMALLINT *       0x0320F438

    Engine          8e4-b74 EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
      SQLSMALLINT                  2 <SQL_HANDLE_DBC>
      SQLHANDLE           0x0161DD30
      SQLSMALLINT                  1
      SQLWCHAR *          0x0320F3EC [       5] "HY090"
      SQLINTEGER *        0x0320F448 (84)
      SQLWCHAR *          0x01629870 [      73] "[Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer length "
      SQLSMALLINT                513
      SQLSMALLINT *       0x0320F438 (73)

    What can be the trouble and where should I start looking for the problem. Microsoft claims that the new ODBC environment is backward compatible with older ones but that seems to be not true.

    We have incorporated the CSL script language in our system because it works excellent, is Open source, very simple and comes with an real-time inline error report tool and a powerful scripteditor that is easy to adapt to your unique hardware instructions.

    Everything except the ODBC works fine also for Vista and 7 so I really want to solve the problem.

    Thursday, May 20, 2010 8:13 AM

All replies

  • Hi,

    Since the issue is mainly related to SQL Server, you’d better post your question in SQL Forums for a specific solution.

    Best Regards

    Dale

     

    • Marked as answer by Dale Qiao Wednesday, May 26, 2010 6:27 AM
    • Unmarked as answer by sTf_paPs Friday, October 29, 2010 12:58 PM
    Tuesday, May 25, 2010 1:53 AM
  • Hi Dale

    No it has nothing to do with SQL server. I try to connect to an MSAccess database on a Windows 7 pro x32 OS using ODBC that comes with the OS.

    I have tried also with other databses that works fine if the OS is up to WinXP 32 or 64 bit with the same result.

    I believe that there is a "bug" in the ODBC platform itself in Vista and W 7 or that Microsoft has added or changed something without considering compatibility, not the first time that happens. :)

    If something is changed or there is a "bug" and someone can tell me what it is, I can fix it

    Only for the record, I am a Windows fan and have worked with Microsoft OS driven PCs since -84.

    (I still hate when design talibans has been give free hands causing a lot of useless things rendering sleepless nights for the users and technical support for no purpose at all.)

    Friday, June 11, 2010 12:04 PM
  • I tried the SQL forum but my thread was locked down because the moderator said it was not a SQL question.

    Well since no one at Microsoft seems to bother I have investigated more and now I know that the error is in the MSAccess ODBC driver that comes in MDAC3.8 (with Vista and 7).

    The only answers I have recieved so far is that MDAC 3.8 is backward compatible with 3.5 and it is true except for the MSAccess ODBC driver.

    Luckely enogh I have found a way around the problem and that is to skip Microsoft and use MySQL instead. I have checked with MSSQL also and it works fine but as a simple small stand alone application database it is much to expensible. I have tested other databases as well and it works fine.

    There is a downside for our customers and us to use MySQL instead of MSAccess and that is that they do not share exactly the same SQL language definitions so we must keep two versions of our software also the MySQL works really good but is more difficult to set up properly at installation time by our field technicians (they are not IT/computer people)

    Regards
    Mikael


    sTf_paPs
    • Edited by sTf_paPs Friday, October 22, 2010 11:27 AM spelling
    Friday, October 22, 2010 11:26 AM
  • I see the same problem with the Microsoft Excel Driver version 6.01.7600.16385 on Windows 7. The call to SQLGetInfo with SQL_CATALOG_LOCATION was working with the Excel Driver 4.00.6305 and 12.00.06423.00, these are on Windows XP with sp3. When running the same code on the windows 7 box with Microsoft Excel Driver version 6.01.7600.16385, the code no longer works.

    The SQL.LOG contains the following text:

    launcherstart   fbc-14d8    ENTER SQLGetInfoW
            HDBC                0x00A2C610
            UWORD                      114 <SQL_CATALOG_LOCATION>
            PTR                 0x0026CDB4
            SWORD                        0
            SWORD *             0x0026CD88

    launcherstart   fbc-14d8    EXIT  SQLGetInfoW  with return code -1 (SQL_ERROR)
            HDBC                0x00A2C610
            UWORD                      114 <SQL_CATALOG_LOCATION>
            PTR                 0x0026CDB4
            SWORD                        0
            SWORD *             0x0026CD88

            DIAG [HY090] [Microsoft][ODBC Excel Driver]Invalid string or buffer length  (84)

    Can someone at Microsoft address this backward INcompatibility?

    Thanks,

    Tommy

    Wednesday, October 27, 2010 4:31 PM
  • Hi Tommy

    You get exactly the same error as I do so obviosly the same problem exist in the Excel ODBC driver.

    The strange thing is that when I use ODBC in a Visual Studio 6 created software I also get the error but it works. My guess is that in this case the error is ignored  but if you use some other development environment the error is actually handled as an error and because of this it does not work.

    I have tried a lot to get some answers but no reaction so far. If I search the internet for the error code I get some 55000+ hits so it is not only you and me that have the problem.

    Regards

    Mikael


    sTf_paPs
    Wednesday, October 27, 2010 8:44 PM
  • No it is not SQL related. It is some incompatibility in the Win 7 MSAccess and apparently also in MSExcel ODBC driver.

    This is the Windows compability forum is it not ?

    Our solution so far is either demand XP or skip MSAccess as database. We are using MySQL as replacement until you fix this.

    We are also seriously thinking about totally skip Microsoft databases for future software development due to your reluctans to take the problem seriously.

    regards

    Mikael


    sTf_paPs
    Wednesday, January 26, 2011 2:59 PM
  • Hi Tommy

     

    The solution to the problem has been around for a while on Microsoft web site but not esy to find.

     

    http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

    Use the x32 version if your aplication is x32 (on both x32 and x64 OS)

    Use the x64 version if your appplication is x64 and you have a x64 OS

    Works fine for me on XP SP3, Vista x32/x64 and 7 x32/x64

    Strange that not a single Microsoft Moderator could tell that instead of locking down my threads.


    sTf_paPs
    Tuesday, February 8, 2011 4:17 PM
  • Why could not any of you Microsoft guys show this ???

     

    http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

     

    It is the solution to the problem.

     

    And why does it not come with Windows/Microsoft Update ????  It would have saved endles hours of customer support, software walk around  trials, surfing the web, irritation,..........

     

    /Mikael

     


    sTf_paPs
    Tuesday, February 8, 2011 4:22 PM