• Microsoft ODBC Driver for Access, version 4.0

SUMMARY

Installing Microsoft Data Access Components (MDAC) 2.1 installs a new major version of the Microsoft Access ODBC driver. This new Access ODBC driver uses the Microsoft Jet 4.0 database engine which allows access to Microsoft Access 2000 format databases as well as all previous Microsoft Access database formats. The previous version of the Access ODBC driver that shipped with MDAC 1.5 and 2.0 used the Microsoft Jet 3.5 database engine to manipulate the Microsoft Access database file.

Due to design changes in Microsoft Jet 4.0 as well as backward compatibility issues in the new ODBC driver, installing the updated Access ODBC driver can break existing, installed ODBC applications that use the Access ODBC driver forcing the developer to re-code the application in some cases.

MORE INFORMATION

Overview of MDAC 2.1 Access ODBC Driver Issues

Listing of Backward Compatibility Issues with the Microsoft Access ODBC Driver that Ships with MDAC 2.1, MDAC 2.1 SP1, and MDAC 2.1 SP2

1. The MDAC 2.1 version of the Access ODBC Driver does not write to older format-replicated databases. If you have an Access design master or replica database in Access 97 format (or Access 95 format), the MDAC 2.1 Access ODBC driver can open this database but only in read only mode. In order to both read and write with ODBC, you need to use the Microsoft Access ODBC driver that shipped with MDAC 2.0. The error reported when attempting to update an older format replica database is "Operation not supported on replicable databases that have not been converted to the current version." Workarounds include converting the database to Access 2000 format, dropping back to the MDAC 2.0 SP2 version of the ODBC driver, or un-replicating the database.
   
   
2. The Paradox ODBC driver that ships with MDAC 2.1 is read-only unless BDE (the Borland Database Engine) is installed. See the following article in the Microsoft Knowledge Base article for more information:
   
   

   Q230126 ACC2000: Using Paradox Data with Access 2000 and Jet

3. The dBase ODBC driver that ships with MDAC 2.1 is read only unless BDE (the Borland Database Engine) is installed. See the following article in the Microsoft Knowledge Base article for more information:
   
   

   Q230125 ACC2000: Using dBASE Data with Access 2000 and Jet

4. Installing MDAC 2.1 breaks all applications that use the Microsoft FoxPro ODBC driver. Developers need to switch to the Visual FoxPro ODBC driver. See the following article in the Microsoft Knowledge Base article for more information:
   
   

   Q235357 PRB: FoxPro ODBC Driver Replaced by Visual FoxPro ODBC Driver

5. Customers have reported several major performance issues with the Microsoft Access ODBC driver that ships with MDAC 2.1. Performance drops up to 400% between the Jet 3.5-based ODBC driver and the Jet 4.0-based ODBC driver have been reported.
   
   Reported issues include slower connection opens (SQLConnect), slower SQL command executions (SQLExecDirect), slower record insertions when using date/time fields, slower scrolling (SQLSetPos), and slower data retrieval (SQLGetData) performance. The following Knowledge Base article discusses these issues:
   
   

   Q168686 PRB: Performance Loss When Upgrading Jet ODBC Driver

6. The newer Access ODBC driver now follows the ANSI 92 SQL specification because Jet 4.0 follows this specification. This can cause SQL statements that worked properly with the Jet 3.5 based ODBC driver to fail to produce the desired results. The following Knowledge Base articles detail these issues:
   
   

   Q237992 PRB: Testing for NULL in Access Not Returning Records w/Jet 4.0

7. There is a known bug in SQLBindCol where a memory leak occurs with the MDAC 2.1 version of the Access ODBC Driver. This issue is fixed in MDAC 2.1 SP2.
   
   
8. Inserting data into a Bit (Yes/No) field in a Microsoft Access database results in the bit field is always False, even when a value of True was specified. This issue is fixed in MDAC 2.1 SP2. See the following Knowledge Base article for more details:
   
   

   Q221184 FIX: Access Bit Fields and MDAC 2.1

9. After installing the newer Access ODBC driver, deleting a record from a keyset or dynamic cursor results in the row position getting out of sync. For example, after a delete you are supposed to be on record 5 but you end up on record 10. This issue is fixed in MDAC 2.1 SP2. See the following Knowledge Base article for more details:
   
   

   Q230131 Access ODBC Keyset Cursor Becomes Corrupt After a Delete

10. The MDAC 2.1 version of the Access ODBC driver reports unexpected column lengths for text columns when calling SQLColumns. See the following Knowledge Base article for more details:
    
    

    Q236871 BUG: SQLColumns Return Incorrect BUFFER_LENGTH/CHAR_OCTET_LENGTH

11. The MDAC 2.1 Access ODBC driver is not compatible with earlier versions regarding the usage of SQL_ATTR_NOSCAN (SQL_NOSCAN). The MDAC 2.1 Access ODBC driver continues to pre-parse ODBC escape sequences even if it is turned off by ODBC API calls.
    
    
12. The MDAC 2.1 Access ODBC driver does not support the usage of literal GUIDs in SQL statements. The MDAC 2.0 Access ODBC driver supports literal GUIDs in SQL statements when ODBC escape sequence pre-parsing is turned off. See the following Knowledge Base article for more details:
    
    

    Q170117 HOWTO: Use GUID Fields in Access from Visual C++

13. The MDAC 2.1 Access ODBC Driver breaks backward compatibility with usage of the LIKE clause. This is a rare issue where a text field contains the caret character ('^') and you try to select the field using a like clause. For example, if a field f1 contains the value 'C^C' and you attempt to select the record using the following SQL statement the record is not be selected:
    
    

    SELECT * FROM Test WHERE f1='C^C' 

14. With the MDAC 2.1 Access ODBC driver, double quotes can no longer be used as string literals in SQL statements. For example, the following SQL statement would work properly with the MDAC 2.0 driver and return an error ([Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.) when using the MDAC 2.1 driver:

    SELECT "Test" AS F1 FROM Test 

    Using single quotes for string literals works with both drivers.
    
    
15. Customers have reported database bloating issues with the MDAC 2.1 version of the Access ODBC driver. Jet 4.0 now stores all text data as Unicode, and this can require twice as much space as ANSI text (two times the amount of bloating due to Unicode storage of data). Also, there is a record locking issue with MDAC 2.1 that is fixed by installing MDAC 2.1 SP2. See the following Knowledge Base article for more details:
    
    

    Q239527 ACC2000: Database Bloats When Importing Large Text File

16. The MDAC 2.1 Access ODBC driver may return invalid ordinals when calling SQLColumns. If you create a table in Access and later remove a column, the column ordinals reported by the Access ODBC driver when calling SQLColumns are not in sequence. For example, if you create a table with 4 columns and then delete column 2, the reported column ordinals are 1,3,4 rather than 1,2,3. According to the ODBC specification (SQLColumns, ORDINAL_POSITION), the ordinal positions of the columns must start with 1 and must be in sequence.
    
    
17. The MDAC 2.1 Access ODBC driver renames some native data types from the previous version. If you call SQLGetTypeInfo, the following type names are renamed:
    
    

        MDAC 2.0 Name   MDAC 2.1 Name
        -------------   -------------
         LONGTEXT        LONGCHAR
         LONG            INTEGER
         TEXT            VARCHAR 

18. The MDAC 2.1 Access ODBC driver reported an incorrect column length when calling SQLDescribeCol on a calculated textual field. For example, if you submitted the following SQL statement SQLDescribeCol returns 2147483598 as the length of calculated column and not the expected 255 (standard length of text column reported by the MDAC 2.0 Access ODBC driver):

    SELECT [CompanyName] + [ContactName] AS x FROM Customers 

    This bug is fixed in MDAC 2.1 SP1 and all later MDAC 2.1 service packs.
    
    
19. When using the MDAC 2.1 Access ODBC driver with SQLConfigDataSource and REPAIR_DB, the call fails if the database name is not enclosed in double quotes. With the MDAC 2.0 driver the double quotes are not needed. This issue is fixed in MDAC 2.1 SP2.
    
    
20. When using an MDAC 2.1 version of the Microsoft Access ODBC driver and opening a Microsoft Access database file on a network share, an excessive amount of network packet activity occurs, even when the ODBC connection is idle. This is due to the newer driver interpreting the PageTimeout setting correctly (whereas the MDAC 2.0 driver interpreted it incorrectly).
    
    For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

    Q246560 BUG: Excessive Network Activity When Using Access ODBC Driver

21. With the MDAC 2.1 Access ODBC driver, using the SQL keyword TEXT without a length specifier in DDL now maps to a memo field (with the MDAC 2.0 driver, TEXT would map to TEXT(255)). For example, the following SQL statement create a TEXT(255) field with the MDAC 2.0 driver and a MEMO field when using the MDAC 2.1 driver:
    
    

    CREATE TABLE Test (f1 TEXT) 

22. When executing a parameterized query that contains a sub query with the MDAC 2.1 version of the Access ODBC Driver, parameter markers are processed in an unexpected order. Rather than processing the parameter markers from left to right as they appear in SQL, the parameter markers in the sub query are processed first, and then the main query parameter markers are processed. See the following Knowledge Base article for more information:
    
    

    Q244719 BUG: Parameter Mismatch with Sub queries When Using Access ODBC

23. When you open and close multiple statements under a single HDBC while using Microsoft Access ODBC Driver (MDAC 2.1 version), memory usage climbs until you close the parent HDBC. Also, when you open and close multiple rowsets under a single session while using Microsoft Jet OLE DB Provider 4.0, memory usage climbs until you release the parent session. The memory usage issue can be reproduced using ADO, ODBC, or OLE DB as the client API. See the following Microsoft Knowledge Base article for more information:
    
    

    Q247140 Memory Use Climbs with Multiple Recordsets Under a Single Jet Session

Additional query words:

Keywords : kbDatabase kbJET kbMDAC kbODBC kbOLEDB kbProvider kbGrpMDAC kbGrpODBC
Version : WINDOWS:4.0
Platform : WINDOWS
Issue type :
Technology : kbvcSearch

Article ID: Q237994

Last Reviewed:
August 15, 2000

Send to a friend

Provided by
Microsoft Product Support Services