Connect to ms access data base from Azure sql data base RRS feed

  • Question

  • I'm trying to export data from an Azure SQL Database to an MS Access database using Data Factory, MS access, 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. However, our requirement is to move data OUT of our ASQLDB and into the Access db. Which is where the problem occurs when debug the copy data activity. I cannot write data from our Azure SQLDB to the Access database, and receive the following error:

    Operation on target Copy data1 failed: ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.,Source=ACEODBC.DLL,'

    Assistance would be greatly appreciated.

    Monday, October 12, 2020 11:45 AM