ODBC Driver Writer's Kit for Microsoft Access

Microsoft Corporation

ODBC API Calls Made by Microsoft Access

Description

This section contains a list of all of the open database connectivity (ODBC) application programming interface (API) calls used by Microsoft® Access®. Included with each API is a list of parameters that are passed to that API.

Access Use of ODBC

Unless otherwise noted, functions may be called in any valid manner, and parameters may be any value, including NULL, 0, -1, and so on.

SQLAllocConnect

SQLAllocEnv

SQLAllocStmt

SQLCancel

SQLColumns

Result set columns needed:

SQLDescribeCol

SQLDisconnect

SQLDriverConnect

fDriverCompletion

SQL_DRIVER_NOPROMPT

SQL_DRIVER_COMPLETE

SQL_DRIVER_COMPLETE_REQUIRED

SQLError

SQLExecDirect

SQLExecute

SQLFetch

SQLFreeConnect

SQLFreeEnv

SQLFreeStmt

fOption

SQL_CLOSE

SQL_DROP

SQLGetData

SQLGetInfo

fInfoType

SQL_ACTIVE_STATEMENTS

SQL_ODBC_API_CONFORMANCE (must be >= 1)

SQL_DBMS_NAME

SQL_DBMS_VER

SQL_STRING_FUNCTIONS

SQL_NUMERIC_FUNCTIONS

SQL_TIMEDATE_FUNCTIONS

SQL_SYSTEM_FUNCTIONS

SQL_OUTER_JOINS

SQL_EXPRESSIONS_IN_ORDERBY

SQL_CONCAT_NULL_BEHAVIOR

SQL_DATA_SOURCE_READ_ONLY

SQL_TXN_CAPABLE

SQL_CURSOR_COMMIT_BEHAVIOR

SQL_CURSOR_ROLLBACK_BEHAVIOR

SQL_IDENTIFIER_QUOTE_CHAR (expecting 1 char)

SQLGetTypeInfo

fSqlType

SQL_CHAR

SQL_VARCHAR

SQL_LONGVARCHAR

SQL_ALL_TYPES

Result set columns needed:

SQLNumResultCols

SQLParamData

SQLPrepare

SQLPutData

SQLRowCount

SQLSetConnectOption

fOption

SQL_AUTOCOMMIT

SQL_OPT_TRACEFILE

SQL_OPT_TRACE

SQL_LOGIN_TIMEOUT

SQLSetParam

SQLSetStmtOption

fOption

SQL_QUERY_TIMEOUT (need not succeed)

SQL_MAX_LENGTH

SQL_ASYNC_ENABLE (need not succeed)

SQLSpecialColumns

Result set columns needed:

SQLStatistics

Result set columns needed:

SQLTables

Result set columns needed:

SQLTransact

fType

SQL_COMMIT

SQL_ROLLBACK

ODBC SQL Grammar Used by Microsoft Access

Description

This section describes the elements of the ODBC SQL Grammar that are used by Microsoft Access. The ODBC SQL Grammar is described in full in Appendix C of the ODBC Programmer's Reference.

ODBC SQL Grammar

Statements

CREATE TABLE base-table-name (name type, ...)

DELETE FROM ... WHERE search-condition

DROP TABLE

INSERT INTO ... (...) VALUES (...)

SELECT [DISTINCT] select-list

FROM ...

WHERE ...

GROUP BY ...

HAVING ...

ORDER BY ...

UPDATE ... SET ... WHERE search-condition

That is, except for GROUP BY and HAVING, all statements are Minimum.

Elements

Minimum

all elements used

Core (NOT USED)

sub-query

cursor-name

exists-predicate

index-name

pattern-value ::= USER

quantified-predicate

query-specification

referenced-columns

referencing-columns

table-name.*

viewed-table-name

Extended (NOT USED)

binary-literal

date-literal

date-type

ODBC-std-esc-initiator

ODBC-std-esc-terminator

procedure

time-literal

time-type

Note   data-types and ODBC-esc-extensions (outer joins, and so on) are used only to the extent that the driver claims to support them. Exception: ODBC-date-time-extension is assumed to be supported.

Microsoft Access/ODBC Datatype Mappings

Description

This section describes the mapping of datatypes between Microsoft Access and ODBC.

How ODBC Datatypes Are Mapped to Access Types

When creating an attachment, Access calls SQLColumns(szRemoteTableName) to enumerate ODBC column information for each column in the remote table.

For each column, it returns:

Documentation for ODBC types and ODBC's idea of "precision" and "scale" is in Appendix D of the ODBC Programmer's Reference.

Access maps [fSqlType,lPrecision,wScale] to an Access datatype. This is the datatype stored in the attached table definition, and it is what the user sees. The ODBC type information is saved, per column, and fed back into ODBC whenever Access "uses" the column (SELECTing, UPDATEing, INSERTing the column, and making parameters from queries by it).

The type mapping is done as follows:

Anything not covered above is mapped to Text(Field Size==255).

How Access Datatypes Are Mapped to ODBC Datatypes

When executing a "SELECT INTO" query with an ODBC destination (this includes File Export in Access), Access maps each source column type to a destination column type. A CREATE TABLE statement and multiple INSERT statements are sent to the server using these destination types. Access calls SQLGetTypeInfo to get ODBC type information for all datatypes supported by the back-end. A collection of internal data structures is built, describing the type information in an Access-digestible format. The type mapping is done as follows.

(In the mapping below, replace SQL_SMALLINT with SQL_NUMERIC(5,0) if SQL_SMALLINT is not supported by the server. Replace SQL_INTEGER with SQL_NUMERIC(10,0) if SQL_INTEGER is not supported. Replace SQL_VARCHAR with SQL_CHAR if SQL_VARCHAR is not supported by the server. If SQL_CHAR is also not supported, the query fails.)

Microsoft Access Initialization File (MSACCESS.INI) Settings for ODBC

Description

This section contains a table of all of the valid entries that can appear in the Microsoft Access Initialization File, MSACCESS.INI.

Microsoft Access ODBC Spec-Compliance Error Codes

Description

This section contains a table of errors that will be returned when Access determines that a driver has failed to comply with the ODBC specification. The table contains an error number that will be returned by Access. Following the error number are two pieces of information:

• A description of the ODBC API call that was made, including any parameter values
  
  
• A description of the condition that caused the error

ODBC Spec-Compliance Errors

General Caveats when Using ODBC Drivers with Microsoft Access

Description

This section describes general caveats to keep in mind when using ODBC drivers with Microsoft Access. It includes assumptions that Access makes regarding driver behavior as well as giving some specific implementation details of Access functionality.

ODBC Driver Writer Caveats

Cursor commit behavior and transaction handling

Access calls SQLGetInfo for:

SQL_ACTIVE_STATEMENTS

SQL_TXN_CAPABLE

SQL_CURSOR_COMMIT_BEHAVIOR

SQL_CURSOR_ROLLBACK_BEHAVIOR

If ACTIVE_STATEMENTS = = 1, then Access will spawn a new real connection whenever it needs to execute a query that will have pending results, that is, that will not immediately be SQLFetched to the end. If ACTIVE_STATEMENTS != 1, then Access will share existing connections, even for queries that have pending results. The positions of these cursors is assumed to be stable, even across transactions.

If TXN_CAPABLE returns >= 1, then Access will simulate wrapping a transaction around each INSERT/UPDATE/DELETE statement.

• To simulate a BEGIN TRANSACTION, Access calls SQLSetConnectOption(AUTO_COMMIT, 0).
  
  
• To simulate a COMMIT TRANSACTION, Access calls SQLTransact(SQL_COMMIT) followed by SQLSetConnectOption(AUTO_COMMIT, 1).
  
  
• To simulate a ROLLBACK TRANSACTION, Access calls SQLTransact(SQL_ROLLBACK) followed by SQLSetConnectOption(AUTO_COMMIT, 1).

If TXN_CAPABLE returns 0, then Access will stay in AUTO_COMMIT mode permanently.

Let CURSOR_BEHAVIOR be the minimum of CURSOR_COMMIT_BEHAVIOR and CURSOR_ROLLBACK_BEHAVIOR. If CURSOR_BEHAVIOR < 1, then Access will pretend that the data source is READ_ONLY, and that transactions are not supported. If CURSOR_BEHAVIOR = = 1, and ACTIVE_STATEMENTS != 1, then Access will pretend that ACTIVE_STATEMENTS = = 1, in order to force a new connection for each pending-results-query. See comments on ACTIVE_STATMENTS.

Identifier quoting

Access calls SQLGetInfo for:

SQL_IDENTIFIER_QUOTE_CHAR

If this is a blank, then identifier quoting is assumed unsupported. If it is not a blank, then this char is used to prefix and suffix every owner, table, and column reference in every query that Access sends to the driver.

Identifying a unique index

When attaching an ODBC table, Access calls SQLStatistics to collect index information. The first unique index returned is marked as the "primary" index. Exception: Indexes on floating-point columns are not marked "primary." The determination of a column being a floating-point column is made after the ODBC type has been mapped to an Access type (see typemap.doc). Unless a "primary" index is found, the attached table will not be updatable.

Sending LIKE to the ODBC driver

Access calls SQLTypeInfo to determine the "searchability" of SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR. Unless the SEARCHABLE column of the result set is SQL_LIKE_ONLY or SQL_SEARCHABLE, Access will perform LIKE operations on such columns locally.

Sending expressions to the ODBC driver

Access calls SQLGetInfo for:

SQL_STRING_FUNCTIONS

SQL_NUMERIC_FUNCTIONS

SQL_TIMEDATE_FUNCTIONS

SQL_SYSTEM_FUNCTIONS

SQL_OUTER_JOINS

SQL_EXPRESSIONS_IN_ORDERBY

SQL_CONCAT_NULL_BEHAVIOR

The values returned are used to determine how much of a query Access may safely send to the driver for execution, and how much must be performed locally by the Access engine.

Datatype conversions

Access calls SQLGetData to retrieve result sets. The fCType parameter value is determined as follows: SQLColumns returns the ODBC type of each column in a table. SQLDescribeCol is also sometimes used. These are mapped to Access data types (see typemap.doc). These Access types are then mapped to fCType values.

Generally, these mappings will not result in "unreasonable" requests for driver conversions. But if SQLGetData returns the "driver cannot convert" error (sqlstate = = "S1C00"), then Access will use fCType = = SQL_C_CHAR for that column, for the remainder of the result set. If the driver cannot convert the data to SQL_C_CHAR, Access returns an error. Conversion of any type to char is not unreasonable.

Special characters contained in column names

A self-join query on a table with a column name containing ',' (comma) will fail, with a "syntax error". Also, column names containing '=' (equals) will cause updates/deletes to fail ("syntax error"), if NULL values are involved. If the primary key has a column name containing '=' (equals), then NULL values in this column will cause browsing to fail ("syntax error"). These behaviors are Access bugs, but may not be fixed for Access 1.1 because of their rarity, and the difficulty of handling these cases.

Tracing the SQL that is sent to the ODBC driver

Adding TraceSQLMode=1 in the [ODBC] section of the MSACCESS.INI file will cause a log file, "sqlout.txt", to be written in the Access directory. This file is always appended to, never overwritten. The lines beginning with "SQLExecDirect" and "SQLPrepare" indicate the SQL that Access is passing to the driver. The "SQLExecute" lines trace calls to SQLExecute on a previously prepared hstmt, and print only the general type of statement executed (for example, "UPDATE", "DELETE"). This log file often contains as much useful information as ODBC API tracing, but with much less chaff, especially in ASYNC mode.

Connection timeout

Access holds onto one or two connections, even when idle, so that you can quickly open and close datasheets/forms without constantly reconnecting. Two idle connections are held only if SQL_ACTIVE_STATEMENTS = = 1. These idle connections are closed after N seconds of idle time, controlled by the ConnectionTimeout value in the [ODBC] section of the MSACCESS.INI file (600 seconds, by default). All connections are closed when Access exits.

Connection management

Access implemented connection-sharing based on two things: SQL_ACTIVE_STATEMENTS (see above comment) and matching connect strings. Access will consider two connect strings to match if:

• The DSN parameter from both connect strings are the same; and
  
  
• The Database parameter is either nonexistent in both connect strings or the same in both connect strings.

Reading the remote connectivity configuration table

When starting a connection, Access executes the query

SELECT nValue FROM MSysConf WHERE Config = 101

against the data source. Any errors during execution/fetching of this query are ignored. This table is not required to exist in the data source, but allows for Access-specific configuration options.

Checking for out-of-date stored procedures on SQL Server

SQL_SUCCESS_WITH_INFO with sqlstate = = "S1000" is treated as an error. This prevents attaching SQL Server tables on a server whose ODBC Stored Procedures are out-of-date.

Distinguishing errors from warnings

Access calls SQLError immediately after any ODBC API that returns an error code other than:

SQL_SUCCESS

SQL_INVALID_HANDLE

SQL_NO_DATA_FOUND

SQL_STILL_EXECUTING

SQL_NEED_DATA

SQLError must return at least one sqlstate and error string. Otherwise, the "error" is assumed to be a harmless warning, and is ignored.

Sending "parameterized" queries to the ODBC driver

Access queries may contain parameters. If declared explicitly, and given a type, Access will try to send expressions containing these parameters to the driver for evaluation. Parameters are explicitly declared by adding them to the Parameter Dialog under the Query menu in Query Design. SQLSetParam is called for each query parameter sent, with values as follows:

Generally, these mappings will not result in "unreasonable" requests for driver conversions. But if SQLSetParam returns the "driver cannot convert" error (sqlstate = = "S1C00"), then Access will try again with fSqlType = = SQL_VARCHAR, cbColDef = = 255, and ibScale = = 0 (with fCType unchanged). If the driver still cannot convert, Access returns an error.

Asynchronous query execution

Access calls SQLSetStmtOption with SQL_QUERY_TIMEOUT and SQL_ASYNC_ENABLE, and ignores any errors that result.

Using SQL_DATA_AT_EXEC for non-NULL data

When updating/inserting/exporting ODBC data, Access always specifies SQL_DATA_AT_EXEC for all non-NULL columns of type SQL_LONGVARCHAR and SQL_LONGVARBINARY. In all other cases, SQL_DATA_AT_EXEC is not used.

INSERT INTO syntax

When inserting in a datasheet, Access uses:

INSERT INTO MyTable (col1, col2, ...) VALUES (?, ?, ...)

When exporting, Access uses:

INSERT INTO MyTable VALUES (?, ?, ...)

unless there are Memo or OLE Object columns, in which case Access again uses:

INSERT INTO MyTable (col1, col2, ...) VALUES (?, ?, ...)

Rolling back transactions

When simulating a ROLLBACK TRANSACTION, errors from SQLTransact are ignored, and are assumed to be of the "already rolled back" nature. This because triggers often do their own rollback, but Access cannot know this.

Special version columns

If SQLSpecialColumns(ROWVER) reports a ROWVER column (say, "RV"), Access uses it to implement optimistic concurrency during datasheet updates. On datasheet update/delete, the UPDATE/DELETE query is appended with "AND RV = ?", and the old value supplied.

If there is no ROWVER column, then all updatable columns are compared to their old values, as in:

UPDATE MyTable SET col1 = ?, col2 = ?, col3 = ?
WHERE PrimKey = ? AND col1 = ? AND col2 = ? AND col3 = ?

LONGVARCHARs and LONGVARBINARYs are excluded from the comparison.

In either case, the query is executed, and SQLRowCount is called. If crow = = 1, the operation succeeds. If crow = = 0, Access assumes that another user changed the row first, and the operation fails. If crow < 0, an error is assumed, and the operation fails. If crow > 1, then it is assumed that the primary index was dropped, and that rows with duplicate keys were added; the operation fails, after rolling back the multiple updates/deletes.

Using the Microsoft Access ODBC Test Application

Description

This section describes the Microsoft Access ODBC Test Application. It gives a brief overview of the purpose of the application and then describes the installation process. It then describes the functionality of the application and how you can customize the application for your ODBC driver.

Overview

The Microsoft Access ODBC Test Application is an Access database designed to verify that your ODBC driver works correctly when used with Microsoft Access. The database contains forms, reports, queries, macros, and modules that will test a variety of functions using the ODBC driver you have developed. In addition to the database, ODBCTEST.MDB, the application contains the SQL Pass-Through DLL, MSASP110.DLL. This DLL allows Access commands directly to an ODBC data source for processing.

The application presents the driver writer with a list of tasks to perform. You may choose any or all of the tasks, and have those tasks executed against data that is accessed via your driver. The application will keep track of the total number of tasks that have been executed, as well as the results of those tasks. You can even configure the application to execute tasks that you define yourself!

Installation

To install the Microsoft Access ODBC Test Application, simply copy the file from the Install diskette to the Access directory on your hard drive. To begin the application, open the database ODBCTEST.MDB from the FileOpenDatabase menu option in Access.

Setup Parameters Dialog

When the application is first started, you will be presented with the Setup Parameters dialog. The Setup Parameters dialog prompts for five pieces of information:

• Server Name: The name of the ODBC data source you will be testing against.
  
  
• Database Name: The name of the database, if necessary, in which to create tables.
  
  
• User ID: The log-in for the given data source.
  
  
• Password: The password for the specified User ID.
  
  
• Current DB: The name of the ODBC Test application database—ODBCTEST.MDB

Main Menu

After the Setup Parameters have been specified, choose OK. The application will connect to the data source and bring up the ODBC Test Database Main Menu. The Main Menu contains five options:

• Setup Parameters: Invokes the Setup Parameters dialog discussed in the section above.
  
  
• Select Tasks: Invokes the "Select Tasks To Run" Form, which allows you to choose the tests to execute.
  
  
• Add/Remove Tasks: Allows you to add or remove tasks from the list of tasks to run.
  
  
• Clear Results Table: Clears the table that contains the results of previous test execution.
  
  
• Close: Terminates the ODBC Test Application.

Select Tasks to Run

The Select Tasks to Run form presents a list of tests to be executed. The list of tests is in a multi-select scrolling subform. Choose the Select All button to select all of the tests or choose the Clear All button to de-select all of the tests. Once you have chosen the set of tests you would like to execute, choose the Go! button. This will begin the test execution. The Close button will return you to the Main Menu.

Table Status Form

The Table Status Form is present whenever the ODBC Test Application is active. It shows the current status of executing tests as well as the status of each of the tables used by the tests. Tables can be in one of three states: DOES NOT EXIST, DIRTY, or LOADED. You can force all tables to be marked as either DIRTY or DOES NOT EXIST. DOES NOT EXIST means that the table has not been created yet on the specified data source, and therefore must be exported before a test can be run against that table. DIRTY means that the table exists, but one or more records has been modified. This implies that the data needs to be re-exported before a test can be run against that table. LOADED means the table exists and is in an unmodified state. Tests can be run against this table without further operations being performed on it.

During test execution, the Table Status Form will display the name of the currently executing test. It will also display a running summary of the total number of tests that have passed and failed, as well as indicating the current progress of the tests—for example, Executing test 5 of 10.

Customizing the Application for Your Driver

Although the ODBCTEST.MDB is designed to be used "as is," it may be necessary to customize the application for data-source-specific features. There is an Access Basic module called "SQL Syntax Sensitive" that contains all of the SQL commands that are sent to the data source via the SQL Pass-through DLL. You should verify that the syntax being executed is the proper syntax for your driver. In addition, there is a function in the "SQL Syntax Sensitive" module called pf_DumpTransaction. If your data source does not support the concept of a transaction log (or you do not need to manually clear out the log), you should comment out the call to pf_DumpTransaction in the pf_BeginTest function of the "Testing Tasks" module.