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.

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.

---

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



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

1. 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.
   
2. 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.
   
3. 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.

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:



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.

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.

---