How to run an MS Access Macro or VBA Function? - VisualCron

jrtwynam
Free support Topic Starter

2018-06-15T16:28:58Z

Hi,

I'm currently evaluating Visual Cron in an attempt to convince my manager to buy it, so I'm trying to set up some jobs for real-world scenarios that we need to do. I have a couple of things running in an existing automation tool (not nearly as good as Visual Cron), so I'm trying to get them running in VC. One of the things that we do quite often is set up an MS Access database to do some processing, because it's an easy way to be able to connect to multiple different data sources and process stuff based on them. I tried setting this up in VC a couple of different ways.

First Attempt
I added an "Execute" task, which is supposed to open the MSACCESS.EXE file. I know for a fact that a command such as this works perfectly:

Quote:

msaccess.exe "AccessFilenameAndPath.accdb" /x MacroName

So I figured putting the path to MSACCESS.EXE in the Command field and the path to the file with the /x MacroName in the Arguments field would work, but no such luck. I've tried variations, such as enclosing the path in quotes or not, etc, but nothing worked.

Second Attempt
I tried using the "Office Macro" task. I selected Access as the type, and put the path in the Path field, and the macro name in the macro name field. I tried options such as quotes around the path, execute in 32 bit mode, etc. I also tried using an actual Access macro, which does exist in the file, as well as using the VBA function that the macro calls. Neither worked.

Any ideas?

Thanks.

jrtwynam
Free support Topic Starter

2018-06-15T17:13:46Z

#2

Just some more info here, this is the error it gives me:


ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacro.exe" 52)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A1EBA): Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file.
   at Microsoft.Office.Interop.Access.ApplicationClass.OpenCurrentDatabase(String filepath, Boolean Exclusive, String bstrPassword)
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed.

I have verified that the path I've supplied is correct, and the database isn't opened by another user at all (let alone exclusively). I'm not sure what it means by "it is not an ADP file". Also, MS Office is definitely installed.

Thanks.

jrtwynam
Free support Topic Starter

2018-06-18T13:44:12Z

#3

I made a bit of progress on this. Using the Execute task, I've pointed it to the MS Access executable and told it the path to the ACCDB file. Originally, I was running this in a hidden window, but just for testing purposes I made the window visible.

It does load MS Access, but it's giving me an error saying "Access can't change the working directory to C:\Windows\System32\config\systemprofile\Documents", and it's waiting for me to click OK. When I do, it complains that it can't find the database file. So why would it be trying to change the working directory? When I open the database file manually, on the same computer, it doesn't complain about this. I've tried a number of things, none of which worked:

In MS Access under Options, change the working directory to C: (all Windows machines should have that).
In VC in the task settings, there's a check box for Working Directory. I tried checking that and setting the directory in there to C:.
In VC, tried running it under different credentials.
In Windows, granted myself access to the System Profile's My Documents folder, thinking that it couldn't change it to that because I didn't have permission to access it.

I have verified that all paths I've tried to use are correct (and exist), and I have permission to access them. I'm not sure why it's trying to use the system profile's documents folder - I would expect VC to be running under whatever Windows login I've used to log into the computer, and therefore try to use that user's documents folder. In this case, that would be my own.

I still haven't had any luck getting it to run using the Office Macro task.

jrtwynam
Free support Topic Starter

2018-06-22T15:25:25Z

#4

I decided to try something different. I wrote a VBScript file, the thinking being that I'd call WScript.exe and give it the path to this file, and give it the path to the ACCDB file as well as the function name as parameters. Just as a test, I hard-coded the path and function name:


Dim lReturnCode
Dim sFilePath
Dim sFunctionName

lReturnCode = 0

sFilePath = "<my path>\Test.accdb"
sFunctionName = "TestOutput"

If lReturnCode = 0 Then

	lReturnCode = Main ()

End If

msgbox lReturnCode
WScript.Quit lReturnCode

Function Main()

	On Error Resume Next

	Dim oAccess, lErrCode

	Set oAccess = createobject("Access.application")

	oAccess.OpenCurrentDatabase sFilePath

	oAccess.visible = true

	lErrCode = oAccess.Run (sFunctionName)

	Set oAccess = Nothing

	Main = lErrCode
	
End Function

This script works perfectly when I run it manually through Windows Explorer, but it refuses to run through VC. It wants to give me an exit code 3151, and it doesn't output the file.


Exception in Task: Non zero exit code
Exit code (3151) description: VisualCronAPI.ExitCodes+ExitCodeNotFoundException: Exit code 3151 was not found in exit code collection 'Windows.
   at VisualCronAPI.ExitCodes.GetExitCodeInfo(ExitCodeCollectionClass ecc, Int32 intExitCode) in C:\sourcefiles\code\VisualCronAPI\Server\Properties\ExitCodes.vb:line 584
   at VisualCronService.ExitCodeAPI.GetDescription(String strCollectionId, Int32 intExitCode) in C:\sourcefiles\code\VisualCronService\Jobs\ExitCodes\apiExitCode.vb:line 281

Does anyone have any idea how I can get VC to run an MS Access macro or function? So far, I've tried 4 or 5 different methods, and none of them has worked.

Thanks.

jrtwynam
Free support Topic Starter

2018-06-22T15:42:59Z

#5

I should also point out that I'm running the VB Script using the same credentials in VC that I used to test the script manually, which are also the same credentials I've used to log into the computer that VC is running on. I've tried both CScript.exe and WScript.exe, both with shell mode turned on and off. Error code 3151 is "cannot connect to data source <my data source name", but I don't understand why. The data source is set up as a user DSN, but given that everything is running using the same credentials, I don't see how that would matter. What it looks like to me is that WScript.exe is running using the credentials I've specified in the task, but the VBScript file isn't seeing that, so when it opens the ACCDB file, it's running as SYSTEM, which can't see the DSN.

As another test, I added a System DSN and re-linked the query to that one. This time, it ran fine, but that still doesn't seem to make a lot of sense to me.

Support
Official support

2018-06-27T08:39:25Z

#6

You should use the Office macro Task for this. Please try that.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

jrtwynam
Free support Topic Starter

2018-06-27T11:46:06Z

#7

Hi,

I've already tried the Office Macro task, with variations on the parameters. With and without quotes around the path to the accdb file, with and without quotes around the macro name, and toggling the 32-bit mode checkbox. None of that has worked. This the error it gives me:


ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 8884)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A1EBA): Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file.
   at Microsoft.Office.Interop.Access.ApplicationClass.OpenCurrentDatabase(String filepath, Boolean Exclusive, String bstrPassword)
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed.
ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 8884)->Unhandled execution error: System.Runtime.InteropServices.COMException (0x800A09A3): The expression you entered refers to an object that is closed or doesn't exist.
   at Microsoft.Office.Interop.Access.ApplicationClass.CloseCurrentDatabase()
   at TaskOfficeMacro.OfficeMacro.tK7WqLoICa34PA6DeON(Object )
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H()
   at TaskOfficeMacro.OfficeMacro.hCmLhMCQZ()
   at ku8f4Ki7tn12qhZSXw.E0Asrfd6lbKLAQ8eLl.YEm1Dvumr(String[]  )

Edited by user 2018-06-27T11:59:37Z | Reason: Not specified

Support
Official support

2018-06-27T12:40:00Z

#8

Hi,

I've already tried the Office Macro task, with variations on the parameters. With and without quotes around the path to the accdb file, with and without quotes around the macro name, and toggling the 32-bit mode checkbox. None of that has worked. This the error it gives me:


ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 8884)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A1EBA): Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file.
   at Microsoft.Office.Interop.Access.ApplicationClass.OpenCurrentDatabase(String filepath, Boolean Exclusive, String bstrPassword)
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed.
ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacrox86.exe" 8884)->Unhandled execution error: System.Runtime.InteropServices.COMException (0x800A09A3): The expression you entered refers to an object that is closed or doesn't exist.
   at Microsoft.Office.Interop.Access.ApplicationClass.CloseCurrentDatabase()
   at TaskOfficeMacro.OfficeMacro.tK7WqLoICa34PA6DeON(Object )
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H()
   at TaskOfficeMacro.OfficeMacro.hCmLhMCQZ()
   at ku8f4Ki7tn12qhZSXw.E0Asrfd6lbKLAQ8eLl.YEm1Dvumr(String[]  )

Either it cannot find the file or it is locked. I would try placing it locally in root for testing.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

jrtwynam
Free support Topic Starter

2018-06-27T13:09:39Z

#9

Ok, I just tried that. I put the file here:

C:\Test.accdb

This time, it was able to find the file and run it. VC executes the macro specified in the task, and the macro executes a VBA function in the accdb file, which does this:

Runs a query against an Oracle database (set up on the VC server computer as a 32-bit User DSN).
Exports the results as an Excel file to a LAN folder.

For some reason, VC isn't able to run this when it points to the Test.accdb file on the LAN, even though the path was correct. The file itself is able to export the Excel file to the LAN path (it's hard-coded in the VBA function). Any idea how I get VC to be able to run this (and other) files located on a network drive? I was using the full UNC path, not the local mapped drive.

Thanks.

thomas
Free support

2018-06-28T08:11:51Z

#10

Can't help you I'm afraid, but just out of curiosity: Any reason why you just don't run the query and export to excel directly from VC? I would avoid Access at all costs 😊

Support
Official support

2018-07-03T12:34:20Z

#11

If you want it to access something on the lan it is all about the Credential you use in the Task. The Credential you use must have access to that. You could try using another Credential (remote) and uncheck Local login option in the Credential.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

jrtwynam
Free support Topic Starter

2018-09-06T17:03:32Z

#12

Hi,

I'm still trying to get this to work. This is the section of the VC log file pertaining to my test run:


9/6/2018 12:23:16 PM	Info	Job started: Transline CICO
9/6/2018 12:23:16 PM	Info	User 'VisualCron Default Admin' - Ran Job: Transline CICO
9/6/2018 12:23:16 PM	Debug	SendJobStats->Ended (9/6/2018 12:23:16 PM)
9/6/2018 12:23:16 PM	Debug	Next action: ActionContinue
9/6/2018 12:23:16 PM	Debug	Calling GetNextTaskProcess()
9/6/2018 12:23:16 PM	Debug	Next Task is: Run Transline CICO MS Access Macro
9/6/2018 12:23:16 PM	Debug	Next action: ActionContinue
9/6/2018 12:23:16 PM	Debug	Calling StartTaskProcess() with Task: Run Transline CICO MS Access Macro (75774)
9/6/2018 12:23:16 PM	Info	Task started(Office macro): Run Transline CICO MS Access Macro (75774)
9/6/2018 12:23:16 PM	Debug	RunAsType: API
9/6/2018 12:23:16 PM	Debug	Username: svc.eapwmssupport
9/6/2018 12:23:16 PM	Debug	ExecuteHelper started process: 13180
9/6/2018 12:23:16 PM	Debug	Process status - retrieving process id (75774)
9/6/2018 12:23:51 PM	Debug	Process status - has left WaitForExit (75774)
9/6/2018 12:23:51 PM	Debug	ExecuteHelper->CloseOutputHandle: C:\Windows\TEMP\VisualCron\StdOut_0f26e7b6-62c1-4efe-9ad9-6caef435f9ee
9/6/2018 12:23:51 PM	Debug	ExecuteHelper->CloseOutputHandle: C:\Windows\TEMP\VisualCron\StdErr_8abee60b-7950-4eb6-ae33-74807029bf74
9/6/2018 12:23:51 PM	Debug	Process status - OutPut captured (75774)
9/6/2018 12:23:51 PM	Debug	Process status - trying to retrieve exit code
9/6/2018 12:23:51 PM	Debug	Process status - ExitCode fetched (75774)
9/6/2018 12:23:51 PM	Debug	Process status - ProcessHandles closed (75774)
9/6/2018 12:23:51 PM	Debug	ExecuteHelper->CloseOutputHandle: C:\Windows\TEMP\VisualCron\StdOut_0f26e7b6-62c1-4efe-9ad9-6caef435f9ee
9/6/2018 12:23:51 PM	Debug	ExecuteHelper->CloseOutputHandle: C:\Windows\TEMP\VisualCron\StdErr_8abee60b-7950-4eb6-ae33-74807029bf74
9/6/2018 12:23:51 PM	Info	Task completed (Success)->'Run Transline CICO MS Access Macro' (75774)
9/6/2018 12:23:51 PM	Debug	PrevTaskProcess(Run Transline CICO MS Access Macro)->🅱ExitCodeResult: Success[/b] (ECCId: 746c95ce-d394-491c-925c-8bd50e4daa8ePId: 75774, Exit code: 0)
9/6/2018 12:23:51 PM	Debug	Found 1 matching flows. (75774)
9/6/2018 12:23:51 PM	Debug	Calling GetNextTaskProcess()
9/6/2018 12:23:51 PM	Debug	Next execution (2) for Job 'Transline CICO' is: 9/6/2018 12:30:00 PM
9/6/2018 12:23:51 PM	Debug	SendJobStats->Ended (9/6/2018 12:23:16 PM)
9/6/2018 12:23:51 PM	Info	Job completed (Success)->'Transline CICO'

Based on this, it successfully ran the MS Access macro. What I don't understand is why is VC still getting an error? It looks like it has something to do with when it tries to close the database after running the macro:


ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacro.exe" 75796)->RunAccessMacro->COMException System.Runtime.InteropServices.COMException (0x800A09C5): Exception from HRESULT: 0x800A09C5
   at Microsoft.Office.Interop.Access.DoCmd.RunMacro(Object MacroName, Object RepeatCount, Object RepeatExpression)
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H() - maybe Microsoft Office is not installed? This Task requires Microsoft Office being installed.
ExecuteProcess("C:\Program Files (x86)\VisualCron\\TaskOfficeMacro.exe" 75796)->Unhandled execution error: System.Runtime.InteropServices.COMException (0x800706BE): The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
   at Microsoft.Office.Interop.Access.ApplicationClass.🅱CloseCurrentDatabase()[/b]
   at TaskOfficeMacro.OfficeMacro.tK7WqLoICa34PA6DeON(Object )
   at TaskOfficeMacro.OfficeMacro.FI9TTWX4H()
   at TaskOfficeMacro.OfficeMacro.hCmLhMCQZ()
   at ku8f4Ki7tn12qhZSXw.E0Asrfd6lbKLAQ8eLl.YEm1Dvumr(String[]  )

It looks like it does run the macro. Part of what the macro does is reads an Excel file and processes it, and then deletes the Excel file. When I run the job, I can see the Excel file disappear.

@ Thomas: I'd like to get away from Access as well, but the reality is that this is an existing tool from before we bought VC. Of course, I could re-do the whole tool with VC (it'd be a rather complicated job), but it wouldn't allow me to get away from Access completely because I'd still need somewhere to store historical data, and since I already have it working perfectly completely housed in Access, why bother? This is the basic logic of the tool:

Gets a list of all Excel files in a particular network location.
Loops through the files it found, in order of create date/time:
----- For each file:
---------- Imports all records into the Access database.
---------- If the import was successful, move the file to the "success" folder. If not, move it to the "failed" folder.
---------- Compares each record to the historical data stored in the Access database.
---------- Marks records that have changes to any fields.
---------- For each of these changed records:
--------------- Looks up data relating to the record from a separate Oracle database.
--------------- If the data already matches what's in the Access database, marks the record as processed (i.e. no need to do anything with it).
--------------- If the data doesn't match, marks the record as to-be-processed.
--------------- Does a couple of calculations on fields in the Access database.
--------------- Constructs an XML string from the to-be-processed record in the Access database, and stores it in the Access database.
---------- By this point, any record from the original Excel file that has had changes should have a corresponding XML string.
---------- Loop through the newly-created (i.e. unprocessed) XML strings:
--------------- Insert each XML string into the Oracle database (at which point an existing Oracle procedure takes over and processes it).
--------------- Mark the XML string as processed in the Access database.

Edited by user 2018-09-06T17:04:40Z | Reason: Not specified

Support
Official support

2018-09-14T15:11:10Z

#13

It looks like it is failing on a method we call "CloseCurrentDatabase". We mark this as critical. We could ignore this error though if you want to test it?

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Support
Official support

2018-09-14T15:26:17Z

#14

Please test this build where we ignore the error: https://www.visualcron.c....aspx?g=posts&t=8121

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

jrtwynam
Free support Topic Starter

2018-09-14T17:54:02Z

#15

Hi,

I think I figured this one out. The macro I was running simply executed a VBA function within the database. The end of this function closed the database. I had to do it this way because the previous automation tool I was using to execute this was having trouble ensuring that the MS Access executable shut down after running the macro. However, VC doesn't have that issue. So the macro was closing the database, and then VC was trying to close it but it failed because it was already closed. Once I removed that part of the function, it worked fine.

One thing I'm not sure how to do though is return a value from the macro back to VC. Eg if there was a VBA error, I want to return the error code back to VC. Or instead of having VC run a macro, have it run a VBA function directly.

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Login