VBA, MS Access and External Applications

Question

I have a MS Access Database that has a button on it that is supposed to run a tool that I wrote. The tool works. The Access DB works. The VBA to run the tool works.

However, there is an issue when running the tool from VBA. It always crashes when trying to load XML configuration data. I tested this by checking my Error Reporting tool (Control Panel -> Administrative Tools -> Event Viewer). I also wrote a second version of my tool which has all of the XML data hard-coded into it.

I am wondering if there is a known reason for this to occur, and if there is a workable solution for getting around this error. Hard-coding all of my configuration data is not really an ideal solution, as the configuration data needs to be modifiable without having to recompile the entire project and push out an update.

Information:

MS Access 2010 with Visual Basic for Applications (using Shell for application calling)
C# application with XML configuration data using references (IO, Linq, Xml.Linq, Data.OleDb, Globalization)

Thanks for any assistance you may be able to offer.

Per request, here is the code I use to run the application:

Dim hProcess As Long
Dim myPath As String
dim myFile As String

myPath = Environ("ProgramFiles(x86)" & "\mytool\"
myFile = "mytool.exe"

hProcess = Shell( myPath & myFile, vbNormalFocus )

The application is a WinForms application that allows the user to map column names in a csv to fields in access. The only actual issue is the part where it attempts to load the XML configuration data:

XDocument xDoc = XDocument.Load(Environment.CurrentDirectory + "\\config.xml");

Again, if I hard-code the configuration data into the application itself, there is no issue at all when running the application. However, if I attempt to load the XML configuration data, it gives an Error Event in the Event Viewer stating that there was an unhandled exception when loading the XML file. If I run the application outside of the VBA Shell call, it runs fine and can load the XML file. It only ever crashes when trying to load the XML from VBA Shell.

I tried that as well, however, it had the same issue. P.S. Both ways work if I run the application separately. The problem for some reason seems to stem from the actual loading of the xml file. I'm at a loss as to why. — Jim P
– Jim P, Commented May 18, 2012 at 16:29
What message, if any, does it give when it "crashes when trying to load XML configuration data"? — Joshua Drake
– Joshua Drake, Commented May 18, 2012 at 16:37
To confirm, the System.IO.FileNotFoundException happens even if the path points to and the file is located at: C:\temp\config.xml? — Joshua Drake
– Joshua Drake, Commented May 18, 2012 at 16:45

Christian Specht · Accepted Answer · 2018-05-08 14:41:06Z

In my experience, executing .NET code from MS Access seems to "mess up" some of .NET's "get the current directory" methods, which all work fine if you run the exact same .NET application directly without Access.

If you want to see what I'm talking about, create a new console application in Visual Studio and paste the following code:

using System;
using System.IO;

namespace CurrentDirTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(Environment.CurrentDirectory);
            Console.WriteLine(Directory.GetCurrentDirectory());
            Console.WriteLine(System.Threading.Thread.GetDomain().BaseDirectory);
            Console.WriteLine(AppDomain.CurrentDomain.BaseDirectory);
            Console.ReadLine();
        }
    }
}

When I run that directly from Visual Studio, it outputs this (as expected):

C:\Dev\Code\CurrentDirTest\CurrentDirTest\bin\Debug
C:\Dev\Code\CurrentDirTest\CurrentDirTest\bin\Debug
C:\Dev\Code\CurrentDirTest\CurrentDirTest\bin\Debug\
C:\Dev\Code\CurrentDirTest\CurrentDirTest\bin\Debug\

Now put the compiled .exe in your Program Files (x86)\mytool\ folder and try to call it from Access, with the VBA code from your question.

When I do that on my machine, I get this:

C:\Users\MyUserName\Documents
C:\Users\MyUserName\Documents
C:\Program Files (x86)\mytool\
C:\Program Files (x86)\mytool\

Weird, isn't it?
Environment.CurrentDirectory and Directory.GetCurrentDirectory() both return my "Documents" folder as soon as the application is executed from MS Access.
I've got no idea why this happens, but it does.

Solution:
If you get the same results on your machine as I do on mine, the solution for your problem is simple: just use System.Threading.Thread.GetDomain().BaseDirectory or AppDomain.CurrentDomain.BaseDirectory to get the current directory.

Just in case someone has a similar problem when using COM-Interop: The problem is even worse when you execute a .NET assembly via COM-Interop from MS Access.
If I recall it correctly, both System.Threading.Thread.GetDomain().BaseDirectory and AppDomain.CurrentDomain.BaseDirectory didn't work for me either because both returned the directory of the msaccess.exe.
I had to use this.GetType().Assembly.Location to get the actual location of the .NET assembly.

That is a very odd thing for .Net to be doing. Thank you for the headsup on the reason this was happening. I will do some further testing to make sure that this is working as intended a little later today.

Collectives™ on Stack Overflow

VBA, MS Access and External Applications

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related