Q:

Cannot Write to Access database from azure datafactory

I'm trying to export data from an Azure SQL Database to an MS Access database using Data Factory, ODBC, and ADF's Integration Runtime.  I’ve been able to register the destination Access database in ADF using the documented process of setting up a “self-hosted” Integration Runtime.  The actual connection from ADF to the Access database works as expected – i.e., I can successfully “Test Connection” and query tables that reside in the local Access database.  I can even move data FROM the Access db to the Azure SQL Database.  However, our requirement is to move data OUT of our ASQLDB and into the Access db. Which is where the problem occurs.  I cannot write data from our ASQLDB to the Access database, and receive the following error:
{ "errorCode": "2200", "message": "Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function,Source=ACEODBC.DLL,'", "failureType": "UserError", "target": "Assemblies_ASQL_to_MSA_VM" }

I’ve scoured Google, but have not been able to find someone who’s experiencing the same/similar issue trying to get data from ASQLDB to Access.  I’ve also tried tweaking settings (i.e., connection strings, folders, shares, etc.) as many ways as I could possibly see to do, but to no avail.  Bottom line, the connectivity to the Access database works as expected, but I absolutely cannot write data to the Access database from ADF.  Assistance would be greatly appreciated.

A:

jjones64. I have to say that Access DB is not supported as a sink dataset in the ADF,please see this support list:

My advice is you could transfer to sql db data into on-prem csv file with copy activity, then load csv file into Access DB following this tutorial:https://blog.ip2location.com/knowledge-base/how-to-import-csv-into-microsoft-access-database/

