What's the best way to open another mdb file using VBA?

[Paul]

What code can I use in VBA to open another mdb file?

- We're using Access 2003
- Code would reside in each user's C drive in "C:\Project\Version_check.mdb"
- The file to open would be in the same directory in
"C:\Project\ProjectMgr.mdb"
- In our computers, the access application is located in "C:\Program
Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

I've tried finding the answer on the Web where there are lots of discussions
about using the Shell function, and I've tried several different versions,
but I wasn't able to get it to work.

So what code can I use to open
"C:\Project\Version_check.mdb"
from
"C:\Project\ProjectMgr.mdb"?

Thanks in advance,

Paul

 

[Jack Leach]

There's three ways you can do this:

1) Application.Followhyperlink

Application.FollowHyperlink "C:\Project\Version_Check.mdb"

This approach is somewhat irritating at times because you may get security
related messages when using it.



2) Access's built-in Shell function

Actually I'm not sure how this works... never really used it.



3) The ShellExecute API

This one is my favorite... never had any problems... copy/paste the below
code into a new standard module and use the following function from anywhere
in your project to open the file.


ShellEx "C:\Project\Version_check.mdb"


'=====================
Option Compare Database
Option Explicit

Private Declare Function apiShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long


Public Function ShellEx(FileName As String) As Boolean
ShellEx = Iif( _
apiShellExecute(0, "open", FileName, vbNullString, vbNullString, 1), _

32, True, False

End Function
'=====================


This function will return True if the file/operation opened successfully and
false if it failed.



hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Ken Snell MVP]

2) Access's built-in Shell function

Actually I'm not sure how this works... never really used it.

Shell "msaccess.exe ""C:\Project\Version_check.mdb"""

 

[Jack Leach]

I assume the built-in Shell will run the program of the currently registered
filetype (when the executable is not supplied), unlike
Application.FollowHyperlink (which will open a .jpg file in IE rather than
Picture Viewer in many cases)?

Also, after some refreshment from the help file, it seems as though Shell
has some limited overall functionality... I don't believe it is capable of
the "print" or "explore" operations that ShellExecute is, and ShellExecute
also seems to be limited to a specific number of returnable errors (which may
or may not be a good thing, depending on perspective and preference to error
trapping).

It's been a while since I've compared these... I knew there was a reason I
like ShellExecute better! Though in this case either or will likely work
fine.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Paul]

Thanks for making me aware of this function, Jack.

Although I wasn't able to get it to run from the sample below, I did look it
up on the Web and found Dev Ashish's full code for this function there at:

http://www.mvps.org/access/api/api0018.htm

and as you suggested, it seems to work quite well. One nice feature about
it is that it gets the name of the application executable file from Windows,
so you don't have to know what it is. I would think this would be
especially helpful if the name of that file changes over time.

Paul

 

[Paul]

That Shell function works great, Ken.

I struggled with the window display a bit until I checked Help on the Shell
function and discovered you can add a parameter to control the size of the
application window.

And thanks so much for providing the exact syntax. Access Help only gives
the example of launching Calculator.exe, which doesn't require a filename
parameter. Without your example, I wouldn't have known to put in those
extra quotation marks.

Paul