Overview

The ODBC Desktop Database Drivers 3.5 are designed for use on Microsoft Windows 95 or later, or Windows NT 3.51 or later. These drivers work with ODBC 2.5, but are shipped with ODBC 3.0. Only 32-bit applications are supported on Windows 95 or later; 16-bit and 32-bit applications are supported on Windows NT 3.51 or later. The ODBC drivers on Windows NT 3.51 or later support 16-bit applications by the use of thunking layers.

Note   For information about the version of ODBC to be used with these drivers, refer to the ODBC 2.0 Programmer’s Reference and SDK Guide, and release notes up to ODBC version 2.5. The ODBC 3.0 Programmer’s Reference should not be used as reference material for issues with these drivers.

The ODBC Desktop Database Drivers include 32-bit drivers for Microsoft Access, dBASE, Microsoft Excel, Paradox, and Text. No 16-bit drivers are included. The use of these drivers by 16-bit applications on Windows 95 is not supported.

A driver for Microsoft FoxPro 3.0 is available separately.


Driver ISAM Files

ODBC Desktop Database Drivers 3.5 include ISAM files. Note that the ISAM files have been renamed. Support for Microsoft Excel 97 is new. These files are as follows:

Driver	32-bit ISAM	Versions
Microsoft Access	None (Included in MSJET35.DLL) (1)	Microsoft Access version 7.0 and 97
Microsoft Access	MSRD2X35.DLL (2)	Microsoft Access versions 1.0, 1.1, 2.0
dBase	MSXBSE35.DLL (3)	dBase versions III, IV, and V (dBASE for Windows)
Microsoft Excel	MSEXCL35.DLL (4)	Microsoft Excel versions 3.0, 4.0, 5.0, 7.0, 97
Microsoft FoxPro	MSXBSE35.DLL (5)	Microsoft FoxPro versions 2.0, 2.5, 2.6
Paradox	MSPDOX35.DLL (6)	Paradox versions 3.5, 4.0, 4.5, and 5.0 (Paradox for Windows)
Text	MSTEXT35.DLL (7)	ASCII Text
(1)  Renamed MSJET35.DLL from MSJT3032.DLL.

(2)  Renamed MSRD2X35.DLL from MSRD2X32.DLL.

(3)  Renamed MSXBSE35.DLL from MSXB3032.DLL.

(4)  Renamed MSEXCL35.DLL from MSXL3032.DLL.

(5)  Renamed MSXBSE35.DLL from MSXB3032.DLL.

(6)  Renamed MSPDOX35.DLL from MSPX3032.DLL.

(7)  Renamed MSTEXT35.DLL from MSTX3032.DLL.

Microsoft FoxPro 3.0

The ODBC Desktop Database Drivers do not include a driver for Microsoft FoxPro 3.0, and the Desktop Database Drivers do not support access to Microsoft FoxPro 3.0 files. The Microsoft FoxPro 3.0 driver should be used for access to these files.


ODBC Component Files

The following table lists the files required by each component of ODBC Desktop Database Drivers 3.5. Some files are required by more than one component. These files must be redistributed if you intend to redistribute any of the ODBC Desktop Database Drivers with your commercial application.

The following files are common for each of the ODBC Desktop Database Drivers:

DS16GT.DLL DS32GT.DLL MSJINT35.DLL (1) MSJET35.DLL (2) MSJTER35.DLL (3) MSVCRT40.DLL (4) ODBC16GT.DLL ODBC32.DLL ODBC32GT.DLL ODBCCP32.CPL ODBCCP32.DLL  ODBCCR32.DLL ODBCINST.CNT	ODBCINST.HLP ODBCINT.DLL  ODBCJET.HLP ODBCJET.CNT ODBCJI32.DLL ODBCJT32.DLL ODBCJTNW.HLP ODBCJTNW.CNT ODBCTL32.DLL  VBAJET32.DLL VBAR332.DLL (5)  ODBCTRAC.dll (6) ODBCTRAC.tlb (6)
(1)  Renamed MSJINT35.DLL from MSJINT32.DLL.

(2)  Renamed MSJET35.DLL from MSJT3032.DLL.

(3)  Renamed MSJTER35.DLL from MSJTEER32.DLL.

(4)  Renamed MSVCRT40.DLL from MSVCRT20.DLL.

(5)  Renamed VBAR332.DLL from VBA2232.DLL.

(6)  New files for version 3.5.

The following files are unique to each driver:

Microsoft Access	MSRD2X35.DLL
dBASE	MSXBSE35.DLL ODDBSE32.DLL
Microsoft Excel	MSEXCL35.DLL ODEXL32.DLL
Microsoft FoxPro	MSXBSE35.DLL ODFOX32.DLL
Paradox	MSPDOX35.DLL ODPDX32.DLL
Text	MSTEXT35.DLL ODTEXT32.DLL


Hardware Requirement Changes

The ODBC Desktop Database Drivers version 3.5 require at least 16 MB of random-access memory (RAM).


Software Requirement Changes

All access to the Jet ISAM layer will be single-threaded for all applications using the Jet engine under Windows NT.

The ODBC Desktop Database Drivers version 3.5 work with ODBC version 2.5, even though the drivers ship with ODBC 3.0.

The use of these drivers by 16-bit applications on Windows 95 is not supported.


Data Source Conversion

The 32-bit data sources used with the ODBC Desktop Database Drivers version 3.0 are automatically converted to new 32-bit data sources for version 3.5 drivers. No conversion is provided for 16-bit data sources. To change a 16-bit data source to a 32-bit data source, create a new 32-bit data source, then (optionally) delete the old 16-bit data source. A 32-bit data source and a 16-bit data source cannot share the same name.


Product Support

Product support for ODBC is provided by Microsoft Product Support Services (PSS). Since many Microsoft products utilize ODBC as a core component, and also redistribute ODBC drivers, PSS considers ODBC to be a part of whatever product the user is using that incorporates ODBC as a component. Support for ODBC is provided in accordance with the support agreement of that product. Each of these Microsoft products has its own support offering, as described in the documentation that comes with the product. Please refer to this documentation to determine what support options are available.


Unsupported Data Formats

ODBC Desktop Database Drivers 3.5 do not support the following data sources:

•	Btrieve

•	EMS

•	Lotus 1-2-3

•	Microsoft FoxPro 3.0 (earlier versions of Microsoft FoxPro are supported).


Buffer Size

(This control is displayed only for the Microsoft Access driver.)

The size of the internal buffer, in kilobytes, that is used by Microsoft Access to transfer data to and from the disk. For the version 3.5 drivers, the default buffer size is 512K (displayed as 512), not 256K, as it was for the version 3.0 drivers. Any integer value divisible by 256 can be entered. Note that this option applies to all data sources that use the ODBC driver.


Extensions List

(This control is displayed only for the Text driver.)

When Default (*.*) is unchecked, the list of extensions defaults to *.asc, *.csv, *.tab, and *.txt.

When the Text driver is used, a file with no extension is created when the CREATE TABLE statement is executed with a name that has no extension. Other drivers create a file with a default extension when no extension is provided. To create a file with a .TXT extension, the extension must be included in the name. To display files without extensions in the Define Text Format dialog box, “*.” must be added to the Extensions List.


ImplicitCommitSync

(This new control is displayed only for all drivers, but applies only to the Microsoft Access driver.)

Determines whether the Microsoft Access driver will perform internal or implicit commits asynchronously. This value is initially set to “Yes”, which means that the Microsoft Access driver will wait for commits in an internal/implicit transaction to be completed.

This option is included in the Advanced Options dialog box for the Microsoft Access driver.


Network Directory

The Network Directory control for the Paradox driver has been replaced by the Select Network Directory control.


Net Style

(This new control is displayed only for the Paradox driver.)

The network access style to use when accessing Paradox data: either “3.x” for Paradox 3.x or “4.x” for Paradox 4.x or 5.x. Can be set to “3.x” or “4.x” if the version is Paradox 4.x or 5.x; if the version is Paradox 3.x, the style must be “3.x”.

To set this option dynamically, use the PARADOXNETSTYLE keyword in a call to SQLConfigDataSource.


Page Timeout

(This control is displayed only for the dBASE, Microsoft Access, Microsoft FoxPro, and Paradox drivers.)

Specifies the period of time, in tenths of a second, that a page (if not used) remains in the buffer before being removed. For the Microsoft Access driver, the default has been changed to 5 tenths of a second (0.5 seconds). For the dBASE, Microsoft FoxPro, and Paradox drivers, the default remains 600 tenths of a second (60 seconds).

The page timeout cannot be 0 because of an inherent delay. The page timeout cannot be less than the inherent delay, even if the page timeout option is set below that value.

To set this option dynamically, use the PAGETIMEOUT keyword in a call to SQLConfigDataSource.


Rows to Scan

(This control has been added for the Microsoft Access driver and deleted for the Text driver.)

The default value of this control defaults has been changed from 1 to 8; if it is set to 0, all rows are scanned. (A number outside the limit will return an error.)

For the Text driver, the value will always default to 25, not 1.


Select Network Directory

(This new control is displayed only for the Paradox driver, and replaces the Network Directory control.)

The full path of the directory containing a Paradox lock database, because it contains either the PDOXUSRS.net file (in Paradox 4.x) or the PARADOX.net file (in Paradox 5.x). If the directory does not contain one of these files, the Paradox driver creates one. For information about these files, see the Paradox documentation.

Before you can select a network directory, you must enter your Paradox user name in the User Name text box. Use the Select Network Directory button to select a network directory.

To set this option dynamically, use the PARADOXNETPATH keyword in a call to SQLConfigDataSource.


Select Workbook

(This control is displayed only for Microsoft Excel 5.0, 7.0, or 97.)

For Microsoft Excel 5.0, 7.0, or 97 files, this option displays the currently selected workbook. Before you add the data source, you must use the Select Workbook button to select a directory.

This control replaces the Workbook control.


Show Deleted Rows

(This control is displayed only for the dBASE and Microsoft FoxPro drivers.)

If unchecked, deleted rows are not displayed; if checked, deleted rows are treated the same as non-deleted rows. The default is unchecked.


Sort Order

The following languages have been added to the Sort Order drop down: Japanese, Korean, Taiwanese, and PRC (People’s Republic of China). Spanish has been changed to Traditional Spanish.


Threads

(This new control is displayed only for all drivers, but changeable only for the Microsoft Access driver.)

The number of background threads for the engine to use. For the Microsoft Access driver, this value defaults to 3, but can be changed. For the dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, and Text drivers, this value is 3, and cannot be changed.

To set this option dynamically, use the THREADS keyword in a call to SQLConfigDataSource.


UserCommitSync

(This new control is displayed only for all drivers, but applies only to the Microsoft Access driver.)

Determines whether the Microsoft Access driver will perform user-defined transactions asynchronously. This value is initially set to “Yes”, which means that the Microsoft Access driver will wait for commits in a user-defined transaction to be completed.

Setting this option to False can have unpredictable consequences in a multi-user environment.

This option is included in the Advanced Options dialog box for the Microsoft Access driver.

To set this option dynamically, use the USERCOMMITSYNC keyword in a call to SQLConfigDataSource.


Define Text Format
Defining Text Format

See Also

The following changes have been made to the Define Text Format dialog box, which enables you to define the format for columns in a selected file when the Text driver is used. Note that the Text driver does not change the format of an existing text file to match the format defined in this dialog box, but returns an error when it uses the format, such as when it attempts to retrieve data from the text file.

Control	Information
Add	New control. Adds a column using the values in the Data Type, Name, and Width fields from the dialog box, and the Date Separator value from SCHEMA.ini.
Characters	This defaults to OEM if the format of the item selected in the Tables list has not been previously defined by this dialog box
Column NameHeader	This defaults to FALSE if the format of the item selected in the Tables list has not been previously defined by this dialog box.
Columns	This list is enabled if a file has been selected in the Tables list.
Data Type	Can be BIT, BYTE, CHAR, CURRENCY, DATE, FLOAT, INTEGER, LONGCHAR, SHORT, or SINGLE (for the version 3.0 drivers, was could be CHAR, DATE, FLOAT, or INTEGER).
Delimiter	The delimiter can only be one character in length.
Format	This defaults to CSV Delimited if the format of the item selected in the Tables list has not been previously defined by this dialog box.
Guess	This functionality only works on columns that are less than 64,513 bytes.
Modify	New control. Modifies the selected column using the values in the Data Type, Name, and Width fields.
Remove	New control. Deletes the selected column.
Rows to Scan	This defaults to 25 if the format of the item selected in the Tables list has not been previously defined by this dialog box.
Tables	When <default> is selected, and one of the following is true, then the values of the table attributes in the Tables group are written to schema.ini. No other entries in schema.ini are touched. • There is no schema.ini in the specified directory. • The schema.ini file exists, but there is no section in schema.ini for one of the Text files (with the specified extension) in the directory. • The section for a Text file exists in schema.ini, but the body is empty.
Width	The width of the column may be changed for CHAR or LONGCHAR columns. The width defaults to 1 if the format of the item selected in the Tables list has not been previously defined by this dialog box. For other data types, the width control is disabled, and no value is displayed.


SCHEMA.ini File

The Text ISAM will obtain initial values from the registry, not from SCHEMA.ini. If the values in the registry are different from the values in SCHEMA.ini, the values in the registry will be overwritten by the values from SCHEMA.ini. The following changes have been made to the SCHEMA.ini file.

Section	Information
ColNameHeader	Defaults to FALSE.
Format	Defaults to CSVDELIMITED.  Fixed-length columns may contain blanks on either the left or right side of the value; blanks will be stripped off of the right side of the value. The values in a table that is in a delimited format can have no blanks before or after the value.  If the table format is FIXEDLENGTH, there must be column entries in SCHEMA.ini, or an error is generated by the ISAM.
MaxScanRows	This defaults to 25 (not 1, as with the version 3.0 driver); if set to 0, all rows are scanned.
DateTimeFormat CurrencySymbol CurrencyPosFormat CurrencyDigits CurrencyNegative   Format DecimalSymbol NumberDigits NumberLeadingZeros	Cannot be changed by the user in the Text driver setup dialog box, but can be changed in the SCHEMA.INI file.
Col1, Col2,...	columnName: The column name from the header or generated by text setup.  INTEGER is the same as LONG) DATE date format is the same as DATETIME Width: Applicable only if the data type selected is CHAR or LONGCHAR. Defaults to 1. For Date data types, the date separator can be “-“, “.”, or “/”. It cannot be changed via setup.


Text File Format (Advanced)

No blanks may occur before or after delimited values.


Password Entry

When the Paradox driver is used, a password is only supported if entered in the connection string. That password is valid whenever a table is opened. If no password is passed in the connection string, no password is established for a table. If tables have different passwords, they cannot both be opened in the same session, nor can the tables be joined.


Opening Microsoft Excel Tables

A BIFF3-version table is created if the version of the data source through which the connection was made is “Excel”, i.e., Microsoft Excel 3.0 or 4.0. A worksheet is created in the workbook that is connected to if the version of the data source was “Excel 5.0”, “Excel 7.0”, or “Excel 97”.

All Microsoft Excel tables (spreadsheets) that are created and opened for inserting are opened exclusive by default, and can be opened by only one user at a time. The user must explicitly choose to open Microsoft Excel tables as shared.


Connection Strings (Advanced)

The FIL keyword (File type) can be one of the following:

•	MS Access for Microsoft Access

•	dBase III, dBase IV, or dBase5

•	Excel 3.0, Excel 4.0, Excel 5.0, Excel 7.0, or Excel 97 for Microsoft Excel

•	FoxPro 2.0, 2.5, or 2.6 for Microsoft FoxPro

•	Paradox 3.x, 4.x, or 5.x

•	Text

The UID driver-specific keyword is initially set to “Admin”.


Failing after Repeated Connections

If an ODBC application working on Microsoft Windows 95 connects to and disconnects from the server repeatedly (over 50-60 times), the connection may fail with the driver returning SQLSTATE 01000 (General warning) and the error message “Failed to get the expression service”. This may particularly be a problem for Internet web server applications. This failure is resolved by updating the rpcrt4.dll file in the \\WINDOWS\SYSTEM directory to release QFE 324. For more information, contact Microsoft Product Support Services.


Create Database (Advanced)

When the Microsoft Access driver is used, the Create button may be pressed to create a new database.

The New Database dialog box contains the following fields:

Control	Description
Database Name	Name of the database file with an .mdb extension. Changed from File Name.
Old format (2.x)	New control. Creates an ODBC 2.x-compatible database.
Sort Order	Sets a default sort order for the database. New sort orders are Japanese, Korean, Taiwanese, PRC (People’s Republic of China).
System Database	Creates a system database.


Database Compaction (Advanced)

The sort order can be changed for the destination database.

An error is returned if the name of the file to compact into is the same as another existing file.

Compacting password-protected files may have unpredicted results.


SQL-92 Compliance (Advanced)

The ODBC Desktop Database Drivers and the underlying Microsoft Jet engine are not SQL-92 compliant. They support many features that have been defined in SQL-92. Some features supported in the driver are not supported in SQL-92. For more information, see the Microsoft Jet Database Engine Programmers’s Guide. The following are the major differences between the two:

•	The SQL used by the Desktop Database Drivers supports more powerful expressions than those specified by SLQ-92.

•	Different rules apply to the BETWEEN predicate.

•	The SQL used by the Desktop Database Drivers and ANSI SQL supports different keywords.

The following SQL-92 features are not supported by Microsoft Jet SQL:

•	Security statements, such as GRANT and LOCK.

•	DISTINCT with aggregate function references.

The following features are enhancements in the SQL used by the Desktop Database Drivers that are not specified by SQL-92:

•	The TRANSFORM statement providing support for crosstab queries.

•	Additional aggregate functions (StDev and VarP).


ALTER TABLE Statement Limitations

ALTER TABLE statements are not supported for the Microsoft Excel or Text driver.


BETWEEN Predicate

The syntax:

expression1 BETWEEN expression2 AND expression3

returns true only if expression1 is greater than or equal to expression2 and expression1 is less than or equal to expression3.

The semantics of this syntax are different for the Desktop Database Drivers and the Microsoft Jet engine. In Microsoft Jet SQL, expression2 can be greater than expression3 so that the statement will return TRUE only if expression1 is greater than or equal to expression3, and expression1 is less than or equal to expression2.


Column Name Limitations

When the Microsoft Excel driver is used, if column names are present, they must be in the first row. A name that in Microsoft Excel would use the “!” character must use the "$" character instead, because the “!” character is not legal in an ODBC name, even when the name is enclosed in back quotes “`”. All other valid Microsoft Excel characters can be used in a column name, including spaces. A delimited identifier must be used for a Microsoft Excel column name to include a space. Unspecified column names will be replaced with driver-generated names, i.e., “Col1” for the first column.

The pipe character (|) cannot be used in a column name, whether the name is enclosed in back quotes or not.


DELETE Statement Limitations

Neither the dBASE nor the Microsoft FoxPro driver support packing a table to remove “deleted” values.


DROP INDEX Statement Limitations

The DROP INDEX statement is not supported for the Microsoft Excel or Text driver.


INSERT Statement Limitations

When the Microsoft Excel driver is used, if an empty string is inserted into a column, the empty string is converted to a NULL. A searched SELECT statement that is executed with an empty string in the WHERE clause will not succeed on that column.

When the Paradox driver is used, if a single row is inserted in an empty table that does not have a unique index, an application cannot create a unique index or insert additional data after the single row has been inserted.


LIKE Predicate Limitations

The Desktop Database Drivers support SQL-92 LIKE pattern matching.

A LIKE comparison should not be performed on a column containing data of a numeric or float data type. The results may be unpredictable. For more information, see the Microsoft Jet Database Engine Programmers’s Guide.


String Limitations

The pipe character (|) cannot be used in a string, whether the character is enclosed in back quotes or not.

For maximum interoperability, applications should pass strings in parameters, rather than passing quoted strings.


Table Name Limitations

If a name in Microsoft Excel includes the “!” character, it will automatically be translated to the ‘$’ character instead.


UPDATE Statement Limitations

When the Microsoft Excel driver is used, it is possible to update values, but a row cannot be deleted from a table based on a Microsoft Excel spreadsheet. As a result, the UPDATE statement is not considered officially supported by the Microsoft Excel driver. Only the INSERT statement is considered supported.


Microsoft Access Data Types (Advanced)

The Microsoft Access driver does not support the Microsoft Access GUID data type.

Even though a Microsoft Access NUMBER field with a FieldSize equal to BYTE is unsigned, a negative number can be inserted into the field when using the Microsoft Access driver.

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter (“#”). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error. For example, the date “March 5, 1996” must be represented as {d ‘1996-03-05’} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.

A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.


Microsoft Excel Data Types (Advanced)

The value in a LOGICAL column is returned in a SQL_C_CHAR buffer as either 0 or 1.

If an integer column is created, numbers that are too big for the integer data type can be entered, and data containing non-integer values can be inserted, with the result that the column may be converted to SQL_DOUBLE.


Paradox Data Types (Advanced)

If you insert NULL into a binary column with the Paradox 5 driver, it is changed to 0.


Text Data Types (Advanced)

The following table shows new limitations on Text data types.

Data type	Description
CHAR	In delimited files, a CHAR column may or may not have double quotation-mark delimiters at the beginning and the end; in fixed-length files, double quotation marks are not used as delimiters.
DATETIME	Mixed date separators are not allowed within a table. The Text ISAM formats a DATETIME field in the American or European format, depending upon the International setting in the Windows Control Panel.
FLOAT	NULL values are represented by a blank padded string in fixed-length files, and are omitted in delimited files. Float data may be padded with leading blanks.
INTEGER	The maximum width includes a sign. The maximum width of an INTEGER column is 11, although the width can be greater due to blanks that are allowed in fixed-format tables.
LONGCHAR	The theoretical limit on the width of a LONGCHAR column in either a fixed-length or delimited table is 65500K. The text ISAM is more likely to provide reliable support up to about 32K.


SQLGetInfo Returned Value Changes

The following table lists the C-language #defines for the fInfoType argument of SQLGetInfo for which the returned values have changed for the Desktop Database Drivers version 3.5.

SQL_BOOKMARK_PERSISTENCE

SQL_DATABASE_NAME

SQL_DBMS_VER

SQL_DEFAULT_TXN_ISOLATION

SQL_DRIVER_HDBC

SQL_DRIVER_HENV

SQL_DRIVER_HLIB

SQL_DRIVER_HSTMT

SQL_DRIVER_VER

SQL_IDENTIFIER_CASE

SQL_KEYWORDS

SQL_MAX_CHAR_LITERAL_LEN

SQL_MAX_COLUMN_NAME_LEN

SQL_MAX_COLUMNS_IN_TABLE

SQL_MAX_TABLE_NAME_LEN

SQL_NON_NULLABLE_COLUMNS

SQL_ODBC_SAG_CLI_ CONFORMANCE

SQL_QUALIFIER_NAME_SEPARATOR

SQL_QUALIFIER_TERM

SQL_STRING_FUNCTIONS

SQL_TXN_CAPABLE

SQL_TXN_ISOLATION_OPTION


SQL_BOOKMARK_PERSISTENCE

SQL_BP_SCROLL |
SQL_BP_UPDATE (1)

(1) SQL_BP_UPDATE has been changed such that bookmarks persist after a commit, but do not persist after a rollback.


SQL_DATABASE_NAME

Filename (Microsoft Access, Microsoft Excel 5.0/7.0/97)

Current database directory (dBASE, Microsoft Excel 3.0/4.0, Microsoft FoxPro, Paradox, Text)
     (This return value is new for version 3.5.)


SQL_DBMS_VER

"1.0", "1.1", "2.0", or "3.0"  (Microsoft Access)

(There is no difference in the data format in Microsoft Access versions 3.0, 7.0, and 97.)

"3.0", "4.0", or "5.0"  (dBASE)
"3.0", "4.0", "5.0", "7.0", or "97"  (Microsoft Excel)
"2.0", "2.5", or "2.6"  (Microsoft FoxPro)
"3.x", "4.x", or "5.x" (Paradox)
"1.0"  (Text)


SQL_DEFAULT_TXN_ISOLATION

SQL_TXN_READ_COMMITTED [Microsoft Access]

0 [dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, Text]
     (This return value is new for version 3.5.)


SQL_DRIVER_HDBC

Handled by the Driver Manager. (This return value is new for version 3.5.)


SQL_DRIVER_HENV

Handled by the Driver Manager. (This return value is new for version 3.5.)


SQL_DRIVER_HLIB

Handled by the Driver Manager. (This return value is new for version 3.5.)


SQL_DRIVER_HSTMT

Handled by the Driver Manager. (This return value is new for version 3.5.)


SQL_DRIVER_VER

" 3.50.nnnn"  (nnnn specifies the build date.)


SQL_IDENTIFIER_CASE

SQL_IC_MIXED
(Microsoft Access, Microsoft Excel, Microsoft FoxPro, Paradox, Text)

SQL_IC_UPPER (1)
(dBASE)
     (This return value is new for version 3.5.)

(1) The qualifier is returned in mixed case so that Windows NT can locate the directory.


SQL_KEYWORDS

COUNTER, DISALLOW, IGNORE, and IMAGE are new. BINARY, INT, MEMO, NOTE, and REAL have been deleted. The complete list of keywords supports is:

ALPHANUMERIC, AUTOINCREMENT, BOOLEAN, BYTE, COUNTER, CURRENCY, DATABASE, DATABASENAME, DATETIME, DISALLOW, DISTINCTROW, DOUBLEFLOAT, FLOAT4, FLOAT8, GENERAL, IEEEDOUBLE, IEEESINGLE, IGNORE, IMAGE, INTEGER1, INTEGER2, INTEGER4, LOGICAL, LOGICAL1, LONG, LONGBINARY, LONGCHAR, LONGTEXT, MONEY, NUMBER, OLEOBJECT, OWNERACCESS, PARAMETERS, PERCENT, PIVOT, SHORT, SINGLE, SINGLEFLOAT, STDEV, STDEVP, STRING, TABLEID, TEXT, TOP, TRANSFORM, UNSIGNEDBYTE, VAR, VARBINARY, VARP, YESNO


SQL_MAX_CHAR_LITERAL_LEN

255  (Microsoft Access)
254  (dBASE)
255  (Microsoft Excel 3.0/4.0/5.0/7.0)
65535 (Microsoft Excel 97) (new return value)
254  (Microsoft FoxPro)
255  (Paradox)
255  (Text)


SQL_MAX_COLUMN_NAME_LEN

64  (Microsoft Access)
10  (dBASE)
64  (Microsoft Excel 5.0/7.0/97)  (Added versions 7.0 and 97.)
30  (Microsoft Excel 3.0/4.0)
10  (Microsoft FoxPro)
25  (Paradox)
64  (Text)


SQL_MAX_COLUMNS_IN_TABLE

255

When using the Microsoft Excel driver, a CREATE TABLE statement may allow 256 columns, but the 255 column limit is still valid, and an insert into column number 256 will fail.


SQL_MAX_TABLE_NAME_LEN

64  (Microsoft Access)
12  (dBASE)
31  (Microsoft Excel 5.0/7.0/97)  (Added version 7.0 and 97.)
12  (Microsoft Excel 3.0/4.0)
12  (Microsoft FoxPro)
12  (Paradox)
12  (Text)


SQL_NON_NULLABLE_COLUMNS

SQL_NNC_NON_NULL (changed from SQL_NNC_NULL)


SQL_ODBC_SAG_CLI_CONFORMANCE

SQL_OSCC_COMPLIANT (changed from SQL_OSCC_NOT_COMPLIANT)


SQL_QUALIFIER_NAME_SEPARATOR

"."
(Microsoft Access, Microsoft Excel 5.0, 7.0, or 97)  (Added versions 7.0 and 97.)

"\"
(dBASE, Microsoft Excel 3.0 or 4.0, Microsoft FoxPro, Paradox, Text)


SQL_QUALIFIER_TERM

"Database"  (Microsoft Access)
"Directory"  (dBASE)
"Workbook"  (Microsoft Excel 5.0/7.0)/97  (Added versions 7.0 and 97.)
"Directory" (Microsoft Excel 3.0/4.0)
"Directory"  (Microsoft FoxPro)
"Directory"  (Paradox)
"Directory"  (Text)


SQL_STRING_FUNCTIONS

SQL_FN_STR_ASCII |
SQL_FN_STR_CHAR |
SQL_FN_STR_CONCAT |
SQL_FN_STR_LCASE |
SQL_FN_STR_LEFT |
SQL_FN_STR_LENGTH |
SQL_FN_STR_LOCATE |
SQL_FN_STR_LOCATE_2  (added)
SQL_FN_STR_LTRIM |
SQL_FN_STR_RIGHT |
SQL_FN_STR_RTRIM |
SQL_FN_STR_SPACE |
SQL_FN_STR_SUBSTRING |
SQL_FN_STR_UCASE


SQL_TXN_CAPABLE

SQL_TC_ALL
(Microsoft Access)

SQL_TC_NONE  (changed from 0)
(dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, Text)


SQL_TXN_ISOLATION_OPTION

SQL_TXN_READ_COMMITTED
(Microsoft Access)

0
(dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, Text)
     (New return value)


SQLColAttributes

The following attributes in the result set returned by SQLColAttributes have been changed.

Attribute	Comments
SQL_OWNER_NAME	An empty string (“”) is returned in this column, since owner name is not supported.
SQL_QUALIFIER_NAME	The path to a database file is returned for Microsoft Access; the path to a directory is returned for dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, or Text.
SQL_COLUMN_SEARCHABLE	Fixed-length and variable-length binary and character data types are seachable, even though LONGVARBINARY and LONGVARCHAR are not.


SQLColumns

The following columns in the result set returned by SQLColumns have been changed.

Column	Comment
TABLE_QUALIFIER	The path to a database file is returned for Microsoft Access; the path to a directory is returned for dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, or Text.
TABLE_OWNER	NULL is returned in this column, since owner name is not supported.
NULLABLE	SQL_NO_NULLS is returned for columns that participate in a primary key or unique index.


SQLConfigDataSource

The following keywords used in SQLConfigDataSource are either new or modified.

Keyword	Description
COLLATINGSEQUENCE (new keyword)	The sequence in which the fields are sorted. When the dBASE or Microsoft FoxPro driver is used, the sequence can be: ASCII (the default) or International. When the Paradox driver is used, the sequence can be: ASCII (default), International, Swedish-Finnish, or Norwegian-Danish.  This sets the same option as the Collating Sequence control in the setup dialog box.
CREATE_DB	For the Microsoft Access driver, creates a database file. Has the following format: CREATE_DB=<path name><sort order>, where the path name is the full path to a Microsoft Access database. An error will be returned if the path name specifies an existing database. The sort order will be as set up in the New Database dialog box displayed when the Create button is pressed in the Microsoft Access Setup dialog box. When using the CREATE_DB keyword, if the pathname of the Microsoft Access database to be created contains one or more spaces, then the entire pathname must be enclosed by double quotation marks, as shown in the following examples: “C:\PROGRAM FILES\COMMON FILES\ MyAccess.mdb” “C:\PROGRAM FILES\Access2.mdb” CREATE_DB=C:\TEMP\test.mdb (no quotation marks needed)
CREATE_SYSDB (new keyword)	For the Microsoft Access driver, creates a system database file. Has the following format: CREATE_SYSDB=<path-name><optional-sort-order>, where the path name is the full path to a Microsoft Access database. An error will be returned if the path name specifies an existing database. The sort order will be as set up in the New Database dialog box displayed when the Create button is pressed in the Microsoft Access Setup dialog box. If no sort order is specified, General is used.
CREATE_V2DB (new keyword)	For the Microsoft Access driver, creates a database file that is compatible with Microsoft Access 2.0. Has the following format: CREATE_V2DB=<path-name><optional-sort-order>, where the path name is the full path to a Microsoft Access database. An error will be returned if the path name specifies an existing database. The sort order will be as set up in the New Database dialog box displayed when the Create button is pressed in the Microsoft Access Setup dialog box. If no sort order is specified, General is used.  When using the CREATE_V2DB keyword, if the pathname of the Microsoft Access database to be created contains one or more spaces, then the entire pathname must be enclosed by double quotation marks, as shown in the following examples: “C:\PROGRAM FILES\COMMON FILES\ MyAccess.mdb” “C:\PROGRAM FILES\Access2.mdb” CREATE_V2DB=C:\TEMP\test.mdb (no quotation marks needed)
DELETED  (new keyword)	For the dBASE or Microsoft FoxPro driver, specifies whether or not rows that have been marked as deleted can be retrieved or positioned on. If set to 1, deleted rows are not displayed; if set to 0, deleted rows are treated the same as non-deleted rows. The default is unchecked.  This sets the same option as the Show Deleted Rows control in the setup dialog box.
DRIVERID	An integer ID for the driver.   25 (Microsoft Access)   21 (dBASE III)   277 (dBASE IV)   533 (dBASE 5.0)   534 (Microsoft Excel 3.0)   278 (Microsoft Excel 4.0)   22 (Microsoft Excel 5.0/7.0)   790 (Microsoft Excel 97) (new value)   24 (Microsoft FoxPro 2.0)   280 (Microsoft FoxPro 2.5)   536 (Microsoft FoxPro 2.6)   26 (Paradox 3.x)   282 (Paradox 4.x)   538 (Paradox 5.x)   27 (Text)
FIL	File type     MS Access for Microsoft Access    dBase III, dBase IV, or dBase 5    Excel 3.0, 4.0, 5.0, 7.0, or 97 for Microsoft Excel    FoxPro 2.0, 2.5, or 2.6 for Microsoft FoxPro    Paradox 3.x, 4.x, or 5.x)    Text.
FIRSTROWHASNAMES (new keyword)	For the Microsoft Excel driver, indicates whether the cells of the first row of the range contain the column names for the table (1) or not (0).
IMPLICITCOMMITSYNC (new keyword)	Determines whether the Microsoft Access driver will perform internal or implicit commits asynchronously. This value is initially set to “Yes”, which means that the Microsoft Access driver will wait for commits in an internal/implicit transaction to be completed.  This sets the same option as the ImplicitCommitSync control in the setup dialog box.
MAXBUFFERSIZE  (new keyword)	The size of the internal buffer, in kilobytes, that is used by Microsoft Access to transfer data to and from the disk. The default buffer size is 512K (displayed as 512). Any integer value divisible by 256 can be used. Note that this option applies to all data sources that use the ODBC driver. This sets the same option as the Buffer Size control in the setup dialog box.
MAXSCANROWS	For the Microsoft Access, Microsoft Excel, or Text driver, the number of rows to be scanned when setting a column's data type based upon existing data. A number from 1 to 16 can be entered for the rows to scan. The value defaults to 8 (changed from 1); if it is set to 0, all rows are scanned. (A number outside the limit will return an error.) For the Text driver, you can enter a number from 1 to 32767 for the number of rows to scan; however, the value will always default to 25. (A number outside the limit will return an error.)  This sets the same option as the Rows to Scan Control in the setup dialog box.
PAGETIMEOUT  (new keyword)	Specifies the period of time, in tenths of a second, that a page (if not used) remains in the buffer before being removed. For the Microsoft Access driver, the default is 5 tenths of a second (0.5 seconds). For the dBASE, Microsoft FoxPro, and Paradox drivers, the default is 600 tenths of a second (60 seconds). Note that this option applies to all data sources that use the ODBC driver.  This sets the same option as the Page Timeout control in the setup dialog box.
PARADOXNETPATH (new value)	For the Paradox driver, the full path of the directory containing a Paradox lock database, because it contains either the PDOXUSRS.net file (in Paradox 4.x) or the PARADOX.net file (in Paradox 5.x). If the directory does not contain one of these files, the Paradox driver creates one. For information about these files, see the Paradox documentation. Before a network directory can be selected, a Paradox user name must be entered.  This sets the same option as the Select Network Directory control in the Paradox setup dialog box.
PARADOXNETSTYLE (new keyword)	For the Paradox driver, the network access style to use when accessing Paradox data: either “3.x” for Paradox 3.x or “4.x” for Paradox 4.x or 5.x. Can be set to “3.x” or “4.x” if the version is Paradox 4.x or 5.x; if the version is Paradox 3.x, the style must be “3.x”.  This sets the same option as the Net Style control in the Paradox setup dialog box.
PARADOXUSERNAME (new keyword)	For the Paradox driver, the Paradox user name.  This sets the same option as the User Name control in the Paradox setup dialog box.
PWD	For the Paradox driver, this is an optional keyword and will never be written to the file by the driver. It is used in a call to SQLDriverConnect against password-secured Paradox files.
STATISTICS (new keyword)	For the dBASE or Microsoft FoxPro driver, determines whether table size statistics are approximated. Note that this option applies to all data sources that use the ODBC driver.  This sets the same option as the Approximate Row Count control in the setup dialog box.
THREADS (new keyword)	The number of background threads for the engine to use. For the Microsoft Access driver, this value defaults to 3, but can be changed. For the dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, and Text drivers, this value is 3, and cannot be changed.  This sets the same option as the Threads control in the setup dialog box.
USERCOMMITSYNC (new keyword)	Determines whether the Microsoft Access driver will perform user-defined transactions asynchronously. This value is initially set to “Yes”, which means that the Microsoft Access driver will wait for commits in a user-defined transaction to be completed. The value of this option should not be changed without careful consideration of the consequences. For more information on the option, see the the Microsoft Jet Database Engine Programmers’s Guide. This sets the same option as the UserCommitSync control in the setup dialog box.


SQLGetCursorName

SQLGetCursorName is supported, but can only be used when the Cursor Library is used, because positioned operations are not supported in the driver.


SQLGetStmtOption

The bookmarks returned by an fOption of SQL_GETBOOKMARK are only valid while the query is open and are invalidated when the query is reissued. Persistent bookmarks are not supported.


SQLGetTypeInfo

SQL_ALL_EXCEPT_LIKE will be returned in the SEARCHABLE column for the Byte, Counter, Double, Single, Long, and Short data types. (The LIKE capability can be achieved by converting the value to a character using the ODBC canonical conversion functions, then performing the comparison.)

When the Microsoft Excel driver is used, the ODBC type names are returned in the TYPE_NAME column that is returned by SQLGetTypeInfo.


SQLProcedureColumns

(Supported for Microsoft Access DSNs only.)

Application developers should look for driver-defined columns starting at the end of the result set and proceeding backwards.

The driver-specific ORDINAL column has been added to the result set returned by SQLProcedureColumns.


SQLProcedures

SQLProcedures will only return rows for those procedures that have at least one argument. Procedures that have no arguments are treated as views.

The following columns in the result set returned by SQLProcedures have been modified.

fOption	Comment
PROCEDURE_QUALIFIER	The path to the database file.
PROCEDURE_OWNER	NULL
PROCEDURE_NAME	Undelimited procedure name


SQLSetConnectOption

The following connection options have been modified.

fOption	Comment
SQL_ACCESS_MODE	The SQL_ACCESS_MODE fOption can be set to either SQL_MODE_READ_ONLY or SQL_MODE_READ_WRTE. However, the driver does not prevent updates if SQL_ACCESS_MODE is set to SQL_MODE_READ_ONLY.
SQL_AUTOCOMMIT	When the Microsoft Access driver is used, the SQL_AUTOCOMMIT option may be set to either SQL_AUTOCOMMIT_ON or SQL_AUTOCOMMIT_OFF, because the Microsoft Access driver supports transactions (1).
SQL_LOGIN_TIMEOUT	Not supported.
SQL_PACKET_SIZE	Not supported.
SQL_QUIET_MODE	Not supported.
SQL_TXN_ISOLATION	When the Microsoft Access driver is used, SQL_TXN_ISOLATION is always SQL_TXN_READ_COMMITTED. This fOption is not supported for the other drivers.

(1)	When committing a transaction using the Microsoft Access driver, a finite delay exists between the time the transaction is committed and the time the values are written to disk. This delay is determined by a delay inherent in the Microsoft Jet engine. The page timeout will not be less than a minimum value, even if the PageTimeout option is set below that value. As a result, there is no guarantee that committed data is stable, since changes may be made during the delay.


SQLSetCursorName

SQLSetCursorName can only be used when the Cursor Library is enabled and the application is using SQLExtendedFetch.


SQLSetPos

The bulk-model semantics for SQLSetPos calls with the irow argument equal to 0 are supported.


SQLSetScrollOptions

An fConcurrency argument of SQL_CONCUR_ROWVER is not supported.


SQLSetStmtOption

The following statement options have been modified.

fOption	Comment
SQL_ASYNC_ENABLE	Asynchronous processing is not supported. The SQL_ASYNC_ENABLE fOption will return SQLSTATE S1C00 (Driver not capable).
SQL_KEYSET_SIZE	The only valid keyset size is 0, because mixed and dynamic cursors are not supported. If this value is set to any other number, it will be changed to 0 and the call will return SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed).
SQL_MAX_ROWS	The only valid rowset size is 0, because the Desktop Database Drivers do not support limiting the number of rows that are returned. If this value is set to any other number, it will be changed to 0 and the call will return SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed).
SQL_QUERY_TIMEOUT	Not supported.
SQL_ROW_NUMBER	Not supported.
SQL_SIMULATE_CURSOR	Not supported.


SQLSpecialColumns

All row IDs have a scope of SQL_SCOPE_CURROW.


SQLStatistics

The following columns in the result set returned by SQLStatistics have been modified.

Column	Comment
TABLE_QUALIFIER	The path to a database file is returned for Microsoft Access; the path to a directory is returned for dBASE, Microsoft Excel, Microsoft FoxPro, Paradox, or Text. Pattern matching is not supported in the szTableQualifier argument.
TABLE_OWNER	NULL is returned in this column, since owner name is not supported.
TABLE_NAME	Undelimited table name. Pattern matching is not supported in the szTableName argument.
INDEX_QUALIFIER	NULL is always returned.
INDEX_NAME	Index-dependent.
TYPE	Only SQL_TABLE_STAT or SQL_INDEX_OTHER will be returned for TYPE.
SEQ_IN_INDEX	Index-dependent.
COLUMN_NAME	Index-dependent.
COLLATION	Index-dependent.
CARDINALITY	Returned for Microsoft Access only.
PAGES	NULL is always returned.


SQLTables

The following parameters to SQLTables have been modified.

Parameters	Comment
szTableOwner	The only valid argument for szTableOwner is NULL, since none of the drivers support owner names. With szTableOwner set to NULL, all tables are returned. NULL is returned in the TABLE_OWNER column.
szTableQualifier	In the TABLE_QUALIFIER column, SQLTables will return either the path to a database file (for Microsoft Access or Microsoft Excel 5.0, 7.0, or 97) or the path to a directory (dBASE, Microsoft Excel 3.0 or 4.0, Microsoft FoxPro, Paradox, or Text).
szTableType	For Microsoft Excel 97 files, “SYSTEM TABLE” is returned for sheet names (tables with a "$" on the end), and "TABLE" is returned for tables within worksheets (as with Microsoft Excel 5.0/7.0 files).


Read-Only Status

When an application performs a Save As command on Microsoft Excel data through the Microsoft Excel driver, the application should create a new table and insert the data to be saved into the new table. Inserts result in an append to the table. No other operations can be performed on the table until it is closed and reopened. Once the table is closed, no subsequent insert can be performed, since the table is then a read-only table.

It is possible to update values when using the Microsoft Excel driver, but a row cannot be deleted from a table based on a Microsoft Excel spreadsheet, so updates are not considered officially supported by the Microsoft Excel driver.