topic Re: Access DB Macro | Execution fails in Product Forum

Access DB Macro | Execution fails

AbhilashM — Mon, 22 Aug 2022 14:46:46 GMT

Hello Dear Blue Prism Community,

I have been sitting on an issue for quite some time now. The issue is straight forward.

The bot tries to execute a macro available in an access database and following is the error:-

ERROR [IM010] [Microsoft][ODBC Driver Manager] Data source name too long

I have tried using OLEDB, ODBC VBOs but still fail to fix the issue.
Drivers have been verified and installed.

Connection String : Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FullDBPath];Persist Security Info=False;

Any inputs in this regard will sure be helpful and appreciated.

Thank you
Abhilash M
RPA Developer
Lowe's Companies Inc

Re: Access DB Macro | Execution fails

ewilson — Mon, 22 Aug 2022 18:36:04 GMT

Hello @AbhilashM,

So you're passing in your connection string. Can you show how you're building the connection string? Are you doing something like this:

Or do you build it in the actual Set Connection action like this:

Cheers,
Eric

Re: Access DB Macro | Execution fails

AbhilashM — Mon, 22 Aug 2022 18:56:55 GMT

Hi Eric,

Here is the connection string formulation and the error I get from the Data - OLEDB object.

Error Message:-

Internal : Could not execute code stage because exception thrown by code stage: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Thanks
Abhilash M

Re: Access DB Macro | Execution fails

ewilson — Mon, 22 Aug 2022 19:18:01 GMT

@AbhilashM,

Ok, so you call Set Connection first and then did you call the Open action next, or did you go straight to Execute? You need to call the Open action to actually create the connection to the data source. And then when you're done, make sure to call Close.

Cheers,
Eric

Re: Access DB Macro | Execution fails

AbhilashM — Tue, 23 Aug 2022 12:26:14 GMT

Hi Eric,

Yes I am following the sequence as below:-

Set Connection
Open Connection
Execute
Close Connection

Thanks
Abhilash M

Re: Access DB Macro | Execution fails

PvD_SE — Wed, 24 Aug 2022 06:27:46 GMT

Hi A,

Which of the actions throws you mention (see list below) the error you get?
Set Connection
Open Connection
Execute
Close Connection

Are you trying to run a macro in Access?
or
Are you trying to run an SQL query in Access?

Re: Access DB Macro | Execution fails

AbhilashM — Wed, 24 Aug 2022 11:55:14 GMT

Hi Paul,

I have implemented using the different VBOs available at our disposal - the error message and at which action is described below.

The bot is trying to execute a macro in the access DB.

Using VBO Data OLEDB

At Execute step : Internal : Could not execute code stage because exception thrown by code stage: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Using ODBC Database Connection
At Open : ERROR [IM010] [Microsoft][ODBC Driver Manager] Data source name too long

Using Data OLEDB Extended
At Execute :- Internal : Could not execute code stage because exception thrown by code stage: The Microsoft Access database engine cannot find the input table or query 'Macro_CorpWrite_UpdateBook'. Make sure it exists and that its name is spelled correctly.

The Macro is actually available and is named as is in the DB.

Re: Access DB Macro | Execution fails

PvD_SE — Wed, 24 Aug 2022 13:11:05 GMT

Hi Abhilash,

OleDB already provides the answer: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

OleDB does not do any other tricks than running an SQL. If you can redefine and reformat your Macro as an SQL, you might be able to apply it. But only the Macro as-is cannot be run on OleDB. Dunno if there's another object that might be able to do that.

Can you provide the Macro code so we can see if it would be viable to make it into an SQL?

Happy coding!
---------------
Paul
Sweden