Customizing the Microsoft Access Environment

In this section:

The Adapter for Microsoft Access provides several parameters for customizing the environment and optimizing performance.


Top of page

x
Specifying the Cursor Type

How to:

You can use the SET CURSORS command to specify the type of cursors for retrieval.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Specify the Cursor Type
ENGINE SQLMAC SET CURSORS [CLIENT|SERVER]

where:

CLIENT

Uses Microsoft Access client-side cursors for retrieving data. Client-side cursors normally demonstrate the best performance for data retrieval and benefit the Microsoft Access process. However, except in TRANSACTIONS AUTOCOMMITTED mode, using client-side cursors prevents a server agent from simultaneously reading more than one answer set from the same instance of Microsoft Access.

SERVER

Uses Microsoft Access server-side cursors for retrieving data. Server-side cursors demonstrate lower performance than client cursors. However, setting a high FETCHSIZE factor (100 is the adapter default) improves performance dramatically making them almost as fast as client-side cursors. Client-side cursors are recommended wherever possible to take the load off the Microsoft Access process.

blank

Uses client-side cursors in TRANSACTIONS AUTOCOMMITTED mode and server-side cursors otherwise. This value is the default.


Top of page

x
Activating NONBLOCK Mode

How to:

The Adapter for Microsoft Access has the ability to issue calls in NONBLOCK mode. The default behavior is BLOCK mode.

This feature allows the adapter to react to a client request to cancel a query while the adapter is waiting on engine processing. This wait state usually occurs during SQL parsing, before the first row of an answer set is ready for delivery to the adapter or while waiting for access to an object that has been locked by another application.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Activate NONBLOCK Mode
ENGINE SQLMAC SET NONBLOCK {0|n}

where:

SQLMAC

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is a positive numeric number. 0 is the default value, which means that the adapter will operate in BLOCK mode. A value of 1 or greater activates the NONBLOCK calling and specifies the time, in seconds, that the adapter will wait between each time it checks to see if the:

  • Query has been executed.
  • Client application has requested the cancellation of a query.
  • Kill Session button on the Web Console is pressed.

Note: A value of 1 or 2 should be sufficient for normal operations.


Top of page

x
Obtaining the Number of Rows Updated or Deleted

How to:

PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Obtain the Number of Rows Updated or Deleted
ENGINE SQLMAC SET PASSRECS {ON|OFF}

where:

SQLMAC

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.

OFF

Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.


Top of page

x
Controlling Transactions

How to:

You can use the SET The TRANSACTIONS command to controls how the adapter handles transactions.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.



x
Syntax: How to Control Transactions
ENGINE SQLMAC SET TRANSACTIONS {LOCAL|DISTRIBUTED|AUTOCOMMITTED}

where:

SQLMAC

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

LOCAL

Indicates that the adapter implicitly starts a local transaction on each of the connections where any work is performed. At the time of COMMIT or ROLLBACK, or at the end of the server session, the adapter commits or aborts the work on each connection consecutively. LOCAL is the default value.

DISTRIBUTED

Indicates that the adapter implicitly invokes Microsoft Distributed Transactions Coordinator (DTC) to create a single distributed transaction within which to perform all work on all the connections. At the time of COMMIT or ROLLBACK, or at the end of the server session, the adapter invokes DTC to execute the two-phase commit or rollback protocol. For this purpose, the DTC service must be started on the machine where the server is running and also on all the machines where involved instances of Microsoft SQL Server reside.

This mode is recommended for read-write applications that perform updates on multiple connections simultaneously.

AUTOCOMMITTED

Indicates that each individual operation with Microsoft Access is immediately committed (if successful) or rolled back (in case of errors) by the SQL Server. This is recommended for read-only applications for performance considerations. It is not recommended for read-write applications because in this mode it is impossible to roll back a logical unit of work that consists of several operations.


Top of page

x
Specifying the Transaction Isolation Level

How to:

You can specify the transaction isolation level from the Web Console or using the SET ISOLATION command.



x
Syntax: How to Specify Transaction Isolation Level From a SET Command

You can specify transaction isolation level by issuing the following command

ENGINE SQLMAC SET ISOLATION {RU|RC|RR|SE}

where:

RU

Sets the transaction isolation level to Read Uncommitted.

RC

Sets the transaction isolation level to Read Committed.

RR

Sets the transaction isolation level to Repeatable Read.

SE

Sets the transaction isolation level to Serializable Read.


WebFOCUS