Access

Access is partially supported in ETLBox. Most of the components may work, but there are some limitations when connecting with Access. Below are these limitation described in detail. The good news is: You can make it work to integrate access either as source or as destination.

Odbc drivers #

You can connect to access only via Odbc.

The ODBC connection to Microsoft Access databases has some restrictions. ETLBox is based on .NET Standard and will run in your application as dependency. It now depends if you compile your application with 32bit or 64bit (some version of .NET Core only support 64bit). You will need to get the right Microsoft Access driver installed - either 32bit or 64bit. You can only install the 32bit driver if you have a 32bit Access installed, and vice versa. Also, make sure to set up the correct ODBC connection (there is a 64bit ODBC connection manager tool in windows and another one looking the same for 32bit).

Here some hints where to get the latest Odbc driver for access (please c)

• Driver Access >2016  
• Driver Access >2010  

Leave connection open #

It is recommended that you leave the LeaveOpen connection property set to true. If you manually override this, you may end up with some strange error messages   .

Creating the connection #

To create a connection to an Access Database, use the AccessOdbcConnectionManager and an OdbcConnectionString.

Please note that the AccessOdbcConnectionManager will create a temporary dummy table containing one record in your database when doing the bulk insert. After completion it will delete the table again. This is necessary to simulate a bulk insert with Access-like Sql.

DbDestination dest = DbDestination (
    new AccessOdbcConnectionManager(        
          "Driver={Microsoft Access Driver (*.mdb, *.accdb)}DBQ=C:\DB\Test.mdb"
    ), "DestinationTable"
);
dest.BatchSize = 1000;