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

Help
1

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.

2

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)

3

Anyone?

4

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?

5

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?

6

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.

7

@savantsa
Were you able to resolve this issue? I am facing the same issue and similar requirement as yours.

8

Yes. Access has a way of importing data from an excel file to a table. So I created a macro that does that and then made sure I call that macro after the file has the data. It inserted all the 19000 rows in just 3 to 5 seconds. The only caveat is the filename has to be hardcoded into the macro and can’t be sent as a parameter. Hope that helps.

1 Like
9

Hello, can you please provide the macro code here

10

Welcome to UiPath forum.
About the macro, I created it from Access using the Create Macro wizard and saved it within the Access database file itself. You can do that too.

Hope that helps.