Access DB Macro | Execution fails

[AbhilashM]

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

[ewilson]

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

[AbhilashM]

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

[ewilson]

@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​

[AbhilashM]

Hi Eric,

Yes I am following the sequence as below:-

Set Connection
Open Connection
Execute
Close Connection

Thanks
Abhilash M

[PvD_SE]

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?

[AbhilashM]

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.

[PvD_SE]

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