How to Open an External Program from MS Access

[DamePique]

Hello everybody,

I have a database with a set of files related to each record containing relevant information about them. When these files are xls or doc files, I have no problem to store them as an hyperlink and make MA opening them when clicking, let's say a command button on a form.

However, there is an specific file type, which belongs to a 'private' software of signal processing. So, storing the file (which is actualy a folder) as an hyperlink is not enough. I was wondering if there is the posibility, similary as done with other standard programs, to open than program by clicking a command button and make it open the file-folder of each subject, which could be stored probably as 'Attached Files'(?). This function would be used with visualization purposes, given the facility of use of the mentioned program to plot signals and assess them.

I would like to thank in advance any suggestion or clue for doing this. Also, if somebody knows that it is impossible to do such thing, I will appreaciate the warning not to waste the time trying to program it.

Thanks again!

[TheSmileyCoder]

First off you can use the SHELL command to run other programs for example:

Const Q As String = """"
Shell "MSAccess.exe " & Q & "Z:\DATA\METRO\ComTool\Update\Update.mdb" & Q, vbNormalFocus

Some programs (like the example) will accept a parameter following the program name, and will open that file. You will need to check your special programs documentation for that.

[NeoPa]

Let's talk Windows for a second here.

Normally, files are opened automatically from a link because the file type has been associated (in Windows) with a particular application. This is not possible with folders though, as they typically don't include file types. If the software can be associated with a particular file type (One of the files in the folder possibly) then this could be achieved most easily by associating the software with that file type and changing the link to the particular file within the folder, rather than the folder itself.

If that's not possible then the Shell command would be your best option (as already covered in Smiley's post #2).

[DamePique]

First of all thanks to both for your suggestions.

I tried in first instance to apply the suggestion of NeoPa, as seemed simpler.I have to say that the program is quite hermetic. It opens folders named as 'name.slp', so I tried to find inside the folder, the file having the same extension and use that file as hyperlink but it didn't work.

So I switched to the option given by TheSmileyCoder. In effect, I'm able to open the program, but it gives me an error when trying to open an specific folder, indicating, that the folder I'm trying to open has an error. However, if I open the same folder directly from the program, it works, evidencing that there is not such error.

So, the task is half-solved. Now I was wondering where in the program files I could find which is the way of opening the folder with the Shell function. I tried to chech the documentation, but it is all related to user-directed tasks of signal processing. Does any of you have a clue of where to find this kind of information? Or, could it be possible that a program is so closed so as not to allow Windows to access its internal functions?

Thank you very much in advance again!

[NeoPa]

Many executables respond to invocations such as :

ProgName.Exe /?

or :

ProgName.Exe /Help

with a list of instructions on use.

The .Exe part is not usually necessary but indicates which file I'm referring to.

Otherwise it's down to you fo find the relevant documentation I'm afraid. Knowing the software (which we don't) is a big first step in the process of course. The internet is frequently able to provide a great deal of relevant information when searched.

Another thing to remember of course, is that spaces in an argument can confuse applications unless they're passed using double-quotes, but Smiley handled that in his post (so assuming you've followed that correctly) you shouldn't have fallen over that issue.

[DamePique]

Dear NeoPa,

I think the problem is not on the Shell Function code-line, but in the permission level the program gives to Windows in order to make it run(I don't know if these are the correct technical terms to define what I mean, but I hope you can understand it). Actually, when trying to open its files using Windows Commands such as 'Open with > Program' same error as with the execution of the Shell Function in MS Access is reported, while when opening the same folder using the GUI of the program, it works.

The program is called ProFusion PSG, but unfortunatelly there is not much technical information on the net. I already checked, both, attached documentation and help files, and also searched for any relevant information in Internet. That's why I was previously pointing out that I was afraid the program was intentionally 'closed'. Maybe the easiest way would be contacting straighly the Technical Support of the software to solve the issue.

However, I really thank you both your help, since I got to open the program, at least, which it is a first step. And in the worst case, I could open the file from the program :S

Anyway if I'm able to get an answer from the Technical Support, I will report my findings here just in case it could be interesting for someone else.

Thanks again!

[DamePique]

Fantastic, I think I don't need to contact the Technical Support. I found a reasonable solution using the function:
'SendKeys'

That way, I can work directly from the program GUI by reproducing my movements to open the given folder.

Thanks again!

[NeoPa]

Why not design a CMD file that sets the current folder before invoking the program?

I'm not a big fan of using SendKeys as it always seems a bit of a kludge.

[DamePique]

I was afraid it was not a very elegant solution, even if it worked :S

Well, I'm pretty new at Databases and VBA, but let me think in your suggestion, understand what you mean and try to implement it. I will report the results here.

Thanks once again for your help. You can't imagine how easy things can become when having the apropiate support!

[NeoPa]

Well, that's what we're here for after all ;-)

As it happens I also do a fair bit of Windows work and designing so I can help on that front as well as on the Access one. This seems mainly Windows though.

[DamePique]

Sorry for the delay in the communication, for several reasons I had to stop for a moment with the database. Well, as you predicted, NeoPa, using SendKeys() is not the most efficient solution for what I wanted to do (opening an external program from MS Access).

The fact is that this solution worked perfectly in my PC, but when I tried to use the Database in another PC, it didn't work. Simply, Windows (I guess) was not performing the key-sequence I programmed.

I checked whether the key sequence for opening and loading the file I wanted was the same in the second computed, and effectively it was, so that was not the problem.


I'm wondering if the problem arises in switching from Windows Vista (my PC) to Windows XP (Second PC). Any idea??

However, could you give me any good link or a clue for learning how to use the CMD file you were mentioning? I guess that would definetively solve any incompatibility.

Thanks again,

[TheSmileyCoder]

As I understand it the issue with SendKeys is that it can be unreliable, for instance if the receiving window is not ready to receive input, or if the focus is shifted (by the impatient user clicking another window to read his mail for instance). SendKeys as I understand is essentially similar to "faking" user keyboard input.

[NeoPa]

Typing CMD /? from a Command Line will give you more than you need for using CMD files. Most of it can be ignored though. Essentially a CMD file, just like the BAT (batch) files before it, is a file which lists commands in order of execution. If you can determine exactly what you want run then putting that into a CMD file is fairly straightforward, but I could certainly help with it as long as you can express exactly what you want clearly enough.

[DamePique]

Well, I essentially need to know how to generate a CMD file (is that a txt file to be executed?) and how to make it execute from a Command Button in MS Access.

I will try to explain what I want to do so maybe you can give me a hand (again). Basically when pushing the command button [View File] in a report, Windows should:

1. Open the Program: Open ProF Software (which can be located in a different directory according to the user computer)
2. Load the Pathway of the file of a subject Load a given the signal (PSGFile) corresponding to the patient from whose Report the Command Button has been pressed in the ‘Open File’ Windonw for ProF Program

Tip: In the database, there is a table storing for each patient/record a string in the Field PSGFilePath with the pathway where the PSGFile of that patient is located.

3. Load & Open the signal in the Program: Open that Signal in the ProF program for visualization purposes

How the program works?

1. Open File Window: When you open the ProF program, by let’s say, double clicking on its fast access button, a second window (an ‘Open file’ window) pops up by default over the ProF window.

Note: The ‘Open File’ window has a list-box ‘Select File’ where last opened file is loaded by default
Tip: Ctrl + O has the de same effect

2. Select File Window: The user needs to click the […] button in the ‘Open File’ window and a third window pops up (Select File).

Note: The third window consists on a ‘Select File’-like navigation-panel where a list with the allowed files located in the last-opened folder is shown and a text box to introduce the desired file pathway.

The user has 2 options:
a) Select manually from the navigation panel the desired file and click OK
b) Introduce manually in the text box the pathway where the desired file is located and click OK

Tip: ProF Opens Folders (instead of files) whose names finish with .BP extension

3. The third window closes (‘Select File Window’)
4. Load Pathway The focus returns to the second window and the selected Folder in step 2 is loaded (‘Open File’ Window)

Note: The list-box in the ‘Open File’ window now displays the selected folder in step 2

5. Confirm Selection: The user needs to select the just-loaded Folder in that list-box by clicking on it with the mouse and then press [OK]
6. Load File and Plot: ProF loads and plots the signals.


I hope I have been clear enough so you can make a clear image or how the program behaves and what I want to do. Any other suggestions are welcome. As always, thanks again for your help!

[NeoPa]

When running from Access, you want to pass a path to the software such that it goes directly to the correct patient without asking the operator to select it.

So, you need to find the syntax of the command that runs the ProF software and which allows you to tell it which folder to start in. Often, this is passed as a simple parameter, but each software package can handle this any way it chooses to, and I know nothing about this particular software. That means you need to find out how it should be done. I will give you an example showing how it would work assuming it is passed as a simple parameter, and you can take it from there.

Also, some software will only run correctly when opened from a particular folder. You need to include this in the information you provide as it will effect the CMD file. I will set it up with a dummy path to make it easy to change for you when you know what that folder is.

Command File = [ProF.CMD]

@ECHO OFF
IF .%1==. GOTO ERR1
CD /D "%ProgramFiles%\ProF"
ProF.Exe %1
GOTO EXIT
:ERR1
ECHO Error: No Patient folder passed.
:EXIT
ECHO ON
@EXIT /B

Lines #3 and #4 may need amending depending on what you find, but your shell line (in your Access VBA) should read something like :

Call Shell("{Use your folder here}\ProF.CMD """ & Me.PSGFilePath & """")

[DamePique]

Fantastic!! After a deep analysis of the program, finally it worked perfectly! Thank you very much once again!

It's time to move to the next topic in MS Access...

[NeoPa]

Good for you:-)

It's not everyone that can work successfully in this area so give yourself a slap on the back (or get someone else to do it for you if you can't reach).