VC says it executes MS Access macro, but it doesn't - VisualCron

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

Welcome Guest! To enable all features please Login or Register.

jrtwynam
Free support Topic Starter

2018-06-20T14:59:22Z

Hi,

I've been having a lot of trouble getting VC to run a macro in an MS Access *.accdb file, so I set up a very simple test file. In this file, there's a single query (pass-through to an Oracle database that selects 4 rows). There's a VBA function that outputs that query to an Excel file, and there's a macro that executes this VBA function. When I run the macro manually by opening the accdb file and double-clicking it, it works perfectly - the Excel file appears. I have a task set up in VC to run this macro, using the same credentials as I'm logged onto the computer with. VC says it completed successfully (exit code = 0), but there's no Excel file, which tells me that it didn't run. Any ideas what could be going on?

Thanks.

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

Support
Official support

2018-06-21T11:27:22Z

As the macro is passed to Office API the only way to debug this is to add debug lines in the code that maybe write progress to file. Without this it is hard to say what is going on. Probably, you are ignoring any errors to. I would guess it either has problem accessing the file or logging into Oracle database.

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

jrtwynam
Free support Topic Starter

2018-06-21T12:07:54Z

I'm not ignoring any errors, I'm handling them and returning them from the function:


Public Function TestOutput() As Long

On Error GoTo ErrHandler

    Dim lErrCode As Long
    
    lErrCode = 0
    
    DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatXLSX, "<mypath>\test.xlsx"
    
ExitFunction:
    TestOutput = lErrCode
    Exit Function
    
ErrHandler:
    lErrCode = Err.Number
    Resume ExitFunction
    
End Function

So far, I haven't been able to get VC to run an Access macro. I've tried these methods:

Choosing an Office Macro task, specifying the path to the accdb file and the name of the macro. I've tried it with and without credentials, with and without quotes around the path, with and without quotes around the macro name, 32-bit mode on and off, and using the macro name as well as the name of the VBA function.
Choosing an Execute task, specifying the path to the MS Access exe file as well as the path to the accdb file with "/x MacroName" after it. Again, I've tried it with and without credentials, with and without quotes, specifying or not specifying the working directory, shell mode on or off.
Creating a connection to the accdb file, the thinking being I could use an SQL task to do something like SELECT FunctionName() FROM DUAL (where DUAL is a simple table I created with a single record in it), which would cause the function to run. Unfortunately, VC wasn't able to create the connection.

Nothing has worked. The closest I've gotten is the original reason of this post, where it says it did it but it actually didn't. Other attempts (which I've posted about in separate threads) have yielded an error message. This is a very key piece of functionality for me.

Thanks.

jrtwynam
Free support Topic Starter

2018-06-21T12:20:02Z

On a related note, I can't figure out why it doesn't like it when I try to create a connection to the accdb file. These are the settings I've used in Connection Wizard:

And this is the error it gives me:

Ordinarily, I would think that I need to use a different provider, because the one I chose doesn't seem to be registered. The problem here is that the provider actually is registered:


😎 Microsoft.ACE.OLEDB.12.0
Key: \\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{3BE786A0-0366-4F5C-9434-25CF162E475E}
OLE DB Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
ProgID: Microsoft.ACE.OLEDB.12.0
VersionIndependentProgID:

For some reason, VC doesn't see it. I've also tried using Microsoft.Jet.OLEDB.4.0 (also registered), but VC doesn't see that either. As a last resort, I tried creating a Windows DSN, but VC wasn't able to see that either.

Support
Official support

2018-07-10T12:07:32Z

I may have used the wrong wording with "ignoring" errors. But you are "handling" making it impossible for VC to capture it. It would be better to not handle it.

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