Get an Excel in Access

Hello Everyone,

I am trying to get 4 different Excel sheets in MS Access. In 3 of them it works, but the 4. one isn’t working.

First, I connect to the Access-DB.
Second, I’ll read the Excel-Data (output is a variable)
Third, I’ll insert the variable in the specific Access-Data table

Now, I get the exception:

Source: Insert
Message: Syntaxerror in FROM-clause
Exception Type: System.Data.OleDb.OleDbException

Has anyone an idea?

Hi @veschka

Can I see a screenshot of the properties of the Insert activity? Looks like there is a problem where you are specifying the table name you are trying to insert the data

Thanks for your help.

The properties of the insert:

image

And OrderDB is the output variable of the Excel appl. scope:

image

@veschka Can you try providing the table name without the “”? Just give it as Order to see whether it works

But then i can not run the bot because of Compiler error:
image

Are you sure the table name is correct? “Order”? Just make sure whether there are not additional stuff in the table name…

yeah, it’s just “Order”…

try giving the table name as “[Order]”

image

The next error message :frowning:

Is it possible to share your workflow file so that I can have a look? If that is okay

Access_DB.xaml (25.2 KB)

the two activities on the right site are the one who are not working… if this could work, the next activities will do it right.

I’m wondering whether its because “order” is a keyword… :thinking:

This looks fine to me… Is there a possibility to change the table name and see?

1 Like

I’d recommend surrounding the table name with brackets as mentioned.

That allowed it to get past the first error, then a second one appeared in your insert statement. This is likely because it couldn’t find the correct column/field name in the [Order] table. Try surrounding each of the column names with square brackets similar to how you did the order table. If i were to guess, is there spaces in the column/field names for the [Order] table but not your other tables?

I had trouble with using the UiPath INSERT activity as it can’t handle errors like this. I instead used the SQL non-query activity and created my own parameterized Insert statement

1 Like

Hi @Dave: Do you have an example for this?

Sorry I was so slow getting back to you. Here is a an insert query we used on an access database. It was contained within a ‘For each row’ loop to insert 1 row at a time as that was we had issues that were hard to debug (probably Type issues) when trying to insert the uipath table into the access table in one single insert statement.

Notice how all the column names are surrounded by [square brackets] - this tells the query that it is a literal and so it treats things like spaces, #, and other symbols as text as long as it’s contained in the brackets

"Insert into Review_data
(
[Task_ID],
[Instance_ID],
[Process],
[Transaction],
[Transaction complete date],
[Contract/Policy/EPN],
[SS#/NPN#/Agent#],
[Processing Rep],
[Review Type],
[Issue State],
[Offshore],
[Onshore],
[RBQ Week],
[Line of Business],
[SubProcess],
[Transaction receive date],
[WMS Code],
[Residence State],
[BD/FMO],
[Peer Reviewer],
[Product],
[Beneficiary_Name]
)
Values 
(
@01Task_ID,
@02Instance_ID,
@03Process,
@04Transaction,
@05TransactionCompleteDate,
@06Contract_Policy_EPN,
@07SSN_NPN_AgentNum,
@08ProcessingRep,
@09ReviewType,
@10IssueState,
@11Offshore,
@12Onshore,
@13RBQWeek,
@14LOB,
@15SubProcess,
@16TransactionReceiveDate,
@17WMSCode,
@18ResidenceState,
@19BD_FMO,
@20PeerReviewer,
@21Product,
@22Bene_Name
)"