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_ |
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:
|
|
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:
|
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 or writer (depending on where the SQL statement is executed) 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 or writer (depending on where the SQL statement is executed) 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