Writer Directives

The directives processed by the Microsoft Access Writer are listed below. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the Microsoft Access writer is MDB_ADO.

DATASET

Required/Optional: Required

This is the name of the destination Microsoft Access Database file.

Workbench Parameter: Destination Microsoft Access Database File

PASSWORD

Required/Optional: Optional

The password used to access the database. For existing databases, it may be omitted for Access databases without password protection. If the database does not exist, then the newly created Microsoft Access database will be protected by this password.

Please note that databases associated with a Microsoft Access workgroup are not supported.

MDB_ADO_PASSWORD moneypenny

Workbench Parameter: Password

DEF

Required/Optional: Required

Each Microsoft Access table must be defined before it can be written. The general form of a Microsoft Access definition statement is:

MDB_ADO_DEF <tableName>	\
 	[mdb_update_key_columns <keyColumns>]\
	[mdb_drop_table (yes|no)]\								
 	[mdb_truncate_table (yes|no)]	\								
	[mdb_table_writer_mode (inherit_from_writer|insert|																					
			 update|delete)] \
	[<fieldName><fieldType>[,<indexType>]]+

When the destination table does not exist, the table definition specifies the table that will be created. When the destination table exists and is not being dropped, the fields and types listed should match those of the existing table.

Fields in the table definition that do not match any existing columns will be ignored by the writer. Where <fieldType> is specified, it should be a field type supported by the target database.

The configuration parameters present on the definition line are described in the following table:

Parameter

Contents

tableName

The name of the table to be written. If a table with the specified name exists, it will be overwritten if the mdb_drop_table DEF line parameter is set to YES, or it will be truncated if the mdb_truncate_table DEF line parameter is set to YES. Otherwise the table will be appended. Valid values for table names include any character string devoid of SQL-offensive characters and less than 128 characters in length.

mdb_table_writer_mode

The the default operation mode of the feature type in terms of the types of SQL statements sent to the database.Valid values are INSERT, UPDATE, DELETE and INHERIT_FROM_WRITER. Note that INSERT mode allows for only INSERT operations where as UPDATE and DELETE can be overwritten at the feature levels. INHERIT_FROM_WRITER simply indicates to take this value from the writer level and not to override it at the feature type level.

Default:INHERIT_FROM_WRITER

mdb_update_key_
columns

This is a comma-separated list of the columns which are matched against the corresponding FME attributes’ values to specify which rows are to be updated or deleted when the writer mode is either UPDATE or INSERT.

For example:

mdb_update_key_columns ID

would instruct the writer to ensure that the FME attribute is always matched against the column with the same name. Also, the target table is always the feature type specified in the DEF line.

Each column listed with the mdb_update_key_columns directive must be defined with a type on the DEF line, in addition to the columns whose values will be updated by the operation.

mdb_drop_table

This specifies that if the table exists by this name, it should be dropped and replaced with a table specified by this definition.

Default: NO

mdb_truncate_table

This specifies that if the table exists by this name, it should be cleared prior to writing.

Default: NO

fieldName

The name of the field to be written. Valid values for field name include any character string devoid of SQL-offensive characters and less than 128 characters in length.

fieldType

The type of a column in a table. The valid values for the field type are listed below:

yesno

memo

hyperlink

replicationid

oleobject

integer

byte

long

autonumber

datetime

decimal(width,decimal)

single

double

currency

text(width)

indexType

The type of index to create for the column.

If the table does not previously exist, then upon table creation, a database index of the specified type is created. The database index contains only the one column.

The valid values for the column type are listed below:

indexed: An index without constraints.

unique: An index with a unique constraint.

VERSION

Required/Optional: Required

This statement tells the Microsoft Access writer what version of database should be created. If the database file already exists, the writer will automatically detect and use the correct version.

Parameter

Contents

<version>

The version of Microsoft Access database file to create. The valid values are listed below:

2000/2002/2003

95/97

2.0

Default: 2000/2002/2003

Example:

MDB_ADO_VERSION 2000/2002/2003

Workbench Parameter: Version

START_TRANSACTION

Required/Optional: Optional

This statement tells the Microsoft Access writer module when to start actually writing features into the database. The Microsoft Access writer does not write any features until the feature is reached that belongs to <last successful transaction> + 1. Specifying a value of zero causes every feature to be output. Normally, the value specified is zero – a non-zero value is only specified when a data load operation is being resumed after failing partway through.

Parameter

Contents

<last successful transaction>

The transaction number of the last successful transaction. When loading data for the first time, set this value to 0.

Default: 0

Example:

MDB_ADO_START_TRANSACTION 0

Workbench Parameter: Start transaction at

TRANSACTION_INTERVAL

Required/Optional: Optional

This statement informs the FME about the number of features to be placed in each transaction before a transaction is committed to the database.

If the MDB_ADO_TRANSACTION_INTERVAL statement is not specified, then a value of 500 is used as the transaction interval.

Parameter

Contents

<transaction_interval>

The number of features in a single transaction.

Default: 500

If the MDB_ADO_TRANSACTION_INTERVAL is set to zero, then feature based transactions are used. As each feature is processed by the writer, they are checked for an attribute called fme_db_transaction. The value of this attribute specifies whether the writer should commit or rollback the current transaction. The value of the attribute can be one of COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE. If the fme_db_transaction attribute is not set in any features, then the entire write operation occurs in a single transaction.

Example:

MDB_ADO_TRANSACTION_INTERVAL 5000

Workbench Parameter: Transaction interval

WRITER_MODE

Required/Optional: Optional

Note: For more information, see Database Writer Mode.

This directive informs the Microsoft Access writer which SQL operations will be performed by default by this writer. This operation can be set to INSERT, UPDATE, or DELETE. The default writer level value for this operation can be overwritten at the feature type or table level. The corresponding feature type DEF parameter name is called mdb_table_writer_mode. It has the same valid options as the writer level mode and additionally the value INHERIT_FROM_WRITER which causes the writer level mode to be inherited by the feature type as the default for features contained in that table.

The operation can be set specifically for individual features as well, using the fme_db_operation attribute. Note that when the writer mode is set to INSERT this prevents the mode from being interpreted from individual features and all features are inserted.

If the MDB_ADO_WRITER_MODE statement is not specified, then a value of INSERT is given.

Parameter

Contents

<writer_mode>

The type of SQL operation that should be performed by the writer. The valid list of values are below:

INSERT

UPDATE

DELETE

Default: INSERT

Example:

MDB_ADO_WRITER_MODE INSERT

Workbench Parameter: Writer Mode

BEGIN_SQL{n}

Sometimes, you must execute some SQL statements prior to opening a table. For example, it may be necessary to ensure that a view exists before attempting to read from it.

Upon opening a connection to read from a database, the reader looks for the directive <ReaderKeyword>_BEGIN_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

Required/Optional

Optional

Workbench Parameter

Begin SQL

END_SQL{n}

Sometimes you must execute some SQL statements after a set of tables has been read. For example, it may be necessary to clean up a temporary view after creating it in BEGIN_SQL.

Just before closing a connection on a database, the reader looks for the directive <ReaderKeyword>_END_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

Required/Optional

Optional

Workbench Parameter

End SQL

COMPRESS_AT_END

When this directive is set to “YES”, the Microsoft Access writer will compress the database after all features have been written. This makes use of the existing MDB database option to compact. If COMPRESS_AT_END is not present the database will not be compressed.

Required/Optional

Optional

Values

Yes | No (default)

Mapping File Syntax

<WriterKeyword>_COMPRESS_AT_END YES

Workbench Parameter

Compress Database When Done

OVERWRITE_FILE

Required/Optional: Required

If set to YES, deletes the existing database before writing.

Workbench Parameter

Overwrite Existing Database