The Access Adapter in FlowHeater is responsible for the direct connection to data in Microsoft Access databases. The Adapter makes it possible to import, export and update data in MDB, MDE, ACCDB and ACCDE Access databases. Support is provided for Microsoft Office Access versions 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013, 2016, 2019, 2021 and Microsoft 365.
Note: Note: Microsoft Access ADP and ADE database projects are not supported by the Access Adapter; you should use the SQL Server Adapter for these instead.
Points to consider about using a 32-bit or 64-bit FlowHeater variant for Access databases
Which variant you have to use depends on your system prerequisites. In principle, FlowHeater does not require an MS Office/Access installation in order to work with Access databases.
If no Office/Access is installed, you should generally be able to process Access (.MDB) databases using the 32-bit FlowHeater variant without any problems (including on 64-bit systems). No additional driver installation is needed with this variant.
However, should you prefer to use the 64-bit FlowHeater variant without an Office/Access installation, or you need to process Access .ACCDB databases, it is necessary in these cases to install a 64-bit version of the “Microsoft Database Access Engine” driver.
If on your system there is an MS Office or Access installed then you need to use exactly the same FlowHeater variant (32-bit or 64-bit) as your Office/Access installed: For a 64-bit Office/Access installation you require the 64-bit FlowHeater variant, conversely for a 32-bit Office/Access installation you need the 32-bit FlowHeater variant, even when you are using a 64-bit operating system!
Note: Both FlowHeater variants (32-bit / 64-bit) can be installed in parallel on the same computer and chosen for use as required.
General tab
Access Adapter - select .mdb|.accdb databaseYou specify the path (absolute or relative) to the MS Access database here. If the database is protected with a password, you can optionally enter it here. If you want to store the password it is necessary to check the "Save Password" option here. Note: The password is stored in the Definition data in encrypted form.
When the Adapter is being used on the WRITE side (right-hand side), in the lower half of this tab you can specify what actions the Access Adapter should take during an import (insert/update) into the MS Access database.
Common
Insert data: When this option is checked, SQL Insert statements are generated.
Ignore existing records: During an import and when this option is checked, records that already exist in the table are ignored.
Empty the table before importing: When this is checked you tell the Access Adapter to empty the contents of the table prior to running the import, effectively deleting all existing rows.
Update data: When this option is checked, SQL Update statements are generated. Note: If both the Insert and Update options are checked, the Access Adapter checks whether an SQL Update or Insert should be generated in each instance, by reference to the PrimaryKey. Tip: If you are certain there is only data to insert then avoid checking the Update option, as this will make the process significantly faster.
Remove records (Delete): This will attempt to delete existing records by reference to the fields of the index “Primary Key”. Note: This option cannot be used together with the INSERT or UPDATE options.
No updates or inserts to data, only generate an SQL script: When this option is checked, it signals the Access Adapter to make no inline changes to the database, but instead to store an SQL script with Insert and/or Update statements. This is useful for testing during development and for subsequent application to the database. If this option is checked you should also check the option below and enter the filename that the SQL statements are to be stored in.
Generate SQL script: This option instructs the Access Adapter to store the change statements (Insert, Update) as an SQL script file with the specified name and path.
Fields / Data types tab
Access Adapter - fields and data types propertiesSQL: data available varies according to the side of the Adapter in use:
On the READ side: here you can choose from Tables and Views.
On the WRITE side: only Tables are available.
On the READ side you also have the possibility to enter complex SQL statements in the text field. Table joins must be defined by hand. In the second combo box the tables and views are listed that are available in the specified Access database.
Fields: When you click the Load Schema button, information is retrieved from the database schema (field names, field sizes, data types, primary key, etc.) for the SQL statement above. The information about the fields is then loaded into the field list to the left of this button.
Note: The fields in the field list can be ordered in any sequence required. Fields that are not required can either be temporarily disabled here (tick removed) or simply deleted.
Field properties: How the properties of Primary Key and Auto Increment for the currently highlighted field are to be interpreted are adjusted. This information is only required on the WRITE side. No changes are needed here generally, since the correct information is usually obtained directly from the schema.
A PrimaryKey field is used in an Update to identify record that possibly exists.
Auto Increment fields are neither assigned nor amended in Insert/Update statements.
Warning: If you make changes here it can result in more than one record being updated with an Update!
Data types not supported
The Access OLE Object data type is not supported by the Access Adapter. The Long Integer data type is only supported for 31 signed bits (decimal integers ranging from -2,147,483,648 to +2,147,483,647). If you do expect values that exceed this range, which is generally unusual in MS Access, you must set the data type of the respective field to Decimal in the Access Adapter manually. Note: In so doing you will probably want to zero the decimal place accuracy in the numeric format settings. For an example of this see the description of the Format Heater.
Advanced tab
Access Adpater - extented propertiesAutomatically strip spaces from the start and end of content: If you check this option an auto trim of whitespace characters will be carried out on STRING data types. This means that any spaces, tabs and line break characters are automatically removed from the leading and trailing parts of strings.
Use TRUNCATE TABLE (=quicker) instead of DELETE FROM: When the option “Empty the table before importing” under the General tab is checked “TRUNCATE TABLE” instead of “DELETE FROM” (=default) is used to empty the table content.
Connection timeout: Timeout in seconds while waiting for connection to be established. If no connection has been made after this period, then the import/export run is aborted.
Command timeout: Timeout in seconds while waiting for an SQL command to complete. By entering a zero value here, effectively disables the option altogether. In that case SQL commands will never timeout and are awaited endlessly. This option makes sense when you select massive data volumes from a database on the READ side and the Access database takes a long time to prepare its result.
Use database transactions: This allows you to control how data is imported. According to the default settings Access Adapter uses a single “large” transaction to secure the entire import process. When importing extremely large volumes you may have to adapt transactional behavior to your needs using the “Run AutoCommit after writing every X records”.
Use UNICODE strings: This option is not available for the Access Adapter.
Continue running in the face of SQL errors: This option allows you to instruct FlowHeater not to abort a run when it encounters an Access SQL database error. Warning: This option should only be used in exceptional circumstances, because database inconsistencies could result.
Connection initialization
User-defined SQL instructions for initializing the connection: You can place any user-defined SQL commands here, which will be used for initializing the connection after a connection to the MS Access database has been established.
Adapter settings in the Format tab
Examples
- Importing a CSV text file into Access
- Generating mass test data for an MS Access database table
- Importing several CSV text files at the same time in a batch
Microsoft®, Windows®, Access® are registered trademarks of Microsoft Corporation
