Unable to use the Insert activity to append data from a datatable into an Access Database

Hi Friends,

I am using the UiPath.Database.Activities package.

  1. I have used the Connect Activity to successfully connect to a “YY_SLA Database.mdb” file.
  2. I have a DataTable with 19000 rows. The column headers perfectly match the column names in a “CLIN Detail” table in that database.
  3. Using the “Insert” activity from the package, I am attempting to insert the data from the Datatable into this table in Access.
  4. Then I use the Disconnect Activity to disconnect from the database using the same connection from step 1 above.

What I have observed is that no data was added. The number of records added that I am capturing is 0. I checked the table in Access, no rows were added there either.

I can’t understand why the 19000 odd records were not added.

Please help.

Here’s what I tried:

  1. I tried to rename the column names in Access database as well as in the Excel file by replacing blanks with underscore. I suspected that might be the cause.
  2. I had the Continue on Error property to True (in error) and that’s the reason for not displaying the error message and was failing silently.

Now the error message that is displayed is:

RemoteException wrapping System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Server stack trace:

at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr)

at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbCommandBuilder.GetSchemaTable(DbCommand sourceCommand)

at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)

at System.Data.Common.DbCommandBuilder.GetInsertCommand(DataRow dataRow, Boolean useColumnsForParameterNames)

at System.Data.Common.DbCommandBuilder.GetInsertCommand()

at UiPath.Database.DatabaseConnection.InsertDataTable(String tableName, DataTable dataTable)

at UiPath.Database.Activities.InsertDataTable.<>c__DisplayClass29_0.b__0()

at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object args, Object server, Object& outArgs)

at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]:

at UiPath.Database.Activities.InsertDataTable.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)

at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)

at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

Anyone?

This snippet of the trace may be trying to tell us something.

I’m not sure if you must rename columns in the database to suit the methods of data insert.

Did you try a transaction? Would a rollback error show the error is more clarity?

Are you suggesting transactions because there is a lot of data? In that case, I tried with just one row in the input DT. Same error.

Because this is a table with over 35 columns, I tried a similar operation with a smaller table with just 3 columns and two rows. The input file is a direct export of this table, and then using that to import back into the same table, has errors too.

Have you or anyone had success with Insert activity, at all, before?

Been a while since we used Access at such a scale. I was suggesting a transaction as a rollback may result in a more meaningful error.
Have you tried the extended attributes of the OLEDB driver? Is there a packet size or memory size that would limit you from submitting large amounts of data?

Yes. Access in small number of records doesn’t fail much.