VBA, MS Access and external applications

Question

I have an MS Access database that has a button on it that should launch the tool that I wrote. The tool works. Access DB works. VBA to run the tool works.

However, a problem occurs when starting the tool from VBA. It always crashes when trying to load XML configuration data. I checked this by checking my Error Reporting tool (Control Panel → Administrative Tools → Event Viewer). I also wrote a second version of my tool in which all XML data is hard-coded.

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

Information:

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

Thanks for any help you can offer.

Upon request, here is the code that 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 ) 

An application is a WinForms application that allows a user to map column names in csv to access fields. The only relevant issue is that it is trying to load XML configuration data:

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

Again, if I hard code the configuration data in the application itself, there are no problems at all when starting the application. However, if I try to load the XML configuration data, this gives an error message in the event viewer, which indicates that there was an unhandled exception while loading the XML file. If I run the application outside of the VBA shell call, it works fine and can load the XML file. It always crashes when trying to load XML from a VBA shell.

Answer 1

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

If you want to know 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 this 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 file in the Program Files (x86)\mytool\ and try to call it from Access with the VBA code from your question.

When I do this on my machine, I get the following:

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

Strange, isn't it? Environment.CurrentDirectory and Directory.GetCurrentDirectory() both return my Documents folder as soon as the application runs from MS Access.
I have no idea why this is happening, but it is.

Decision:
If you get the same results on your computer as I do, my solution is very 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 build .NET through COM-Interop from MS Access.
If I remember him correctly, both System.Threading.Thread.GetDomain().BaseDirectory and AppDomain.CurrentDomain.BaseDirectory did not work for me either because both returned the msaccess.exe directory.
I had to use this.GetType().Assembly.Location to get the actual location of the .NET assembly.