Hello All,
I'm a newbie in the visualcron development, but today I faced with a very interesting problem. We have an access database and into access there are several linked tables and queries which are filtering the data. Also there is a macro which combine all the queries into a some strange logic and when I start the macro from Access everything is working correctly. But when I start the macro from VisualCron I receive the following error:

ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 167)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A0C4F): ODBC--connection to '|' failed.
at Microsoft.Office.Interop.Access.DoCmd.RunMacro(Object MacroName, Object RepeatCount, Object RepeatExpression)
at TaskOfficeMacro.OfficeMacro.kT54kK6m5() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed.
ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 167)->Unhandled execution error: System.Runtime.InteropServices.COMException (0x800A0C4F): ODBC--connection to '|' failed.
at TaskOfficeMacro.OfficeMacro.kT54kK6m5()
at TaskOfficeMacro.OfficeMacro.AFhXyIkg5UTKR8uKs7()
at TaskOfficeMacro.OfficeMacro.fpJUqcoBG()
at k7XvBKEgpi0yGhnVl8.u6Hlqv0eL9L3Va8MEl.p4siBD3ri(String[] )
Exception in Task: ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 167)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A0C4F): ODBC--connection to '|' failed.
at Microsoft.Office.Interop.Access.DoCmd.RunMacro(Object MacroName, Object RepeatCount, Object RepeatExpression)
at TaskOfficeMacro.OfficeMacro.kT54kK6m5() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed.
ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 167)->Unhandled execution error: System.Runtime.InteropServices.COMException (0x800A0C4F): ODBC--connection to '|' failed.
at TaskOfficeMacro.OfficeMacro.kT54kK6m5()
at TaskOfficeMacro.OfficeMacro.AFhXyIkg5UTKR8uKs7()
at TaskOfficeMacro.OfficeMacro.fpJUqcoBG()
at k7XvBKEgpi0yGhnVl8.u6Hlqv0eL9L3Va8MEl.p4siBD3ri(String[] )

The office package is installed on the server, so the problem is not coming from the office.
Do you have any ideas why the job failed with ODBC connection? What could be reason and it would be great if you post your ideas how to fix it.
The workaround with SQL Task is not acceptable 🙂

Thanks!

It is probably related to permssions. If no Credential is selected the Task will run as the SYSTEM account. If your ODBC connection requires a certain user to be logged on it will probably fail.

---

Thanks for reply Henrik,
We resolve the issue!

I want to ask you something which is related again to Access macro but this time, the macro is appending excel files into a table. ( I don't have ODBC Connections ).
When I run the macro from VC, the task failed with the following error:
"ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 357)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A0D15): You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.
at Microsoft.Office.Interop.Access.DoCmd.RunMacro(Object MacroName, Object RepeatCount, Object RepeatExpression)
at TaskOfficeMacro.OfficeMacro.kT54kK6m5() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed."

And again when we run the macro manually everything is fine.
Also, I want to ask is there another way to put excel data into access table through VC ?

I don't use Access myself, but if I had to, I would probably do something like this:

1)Create Excel task that converts the data to a textfile
2)Write some .NET code that reads the textfile and inserts it into Access.

if you want to try this way, I can probably give you some hints about the code to write, if you need it.

Thomas

Hello Thomas,
your idea seems to be excellent for our goal. If it possible could you give me the hints that you mentioned ? I think I will face difficulties when inserting data into access table.
Ivan

Here is a simple example in C# to get you started. Assume an Access database called DbTest. It has a Table called Table1, and this table has one column called Col1.
The code below reads the content of a file, and inserts each record into the table:



If you are going to run this as a .NET task, you need to click on 'Edit references' and add the System.Data.dll library. And remember to include the using statements that you see at the top of the code. The filepath would be a variable in VisualCron. I tested it locally and it worked fine, but we don't have office installed on our server, so I haven't been able to test it in VisualCron.

Thomas

This is a post mentioning the same problem.

---