Launching another MS Access application within code

[nigelb]

Does anyone know how I can close an existing Access application and
automatically launch another one at the same time using code in a button
click event? The following code doesn´t seem to work...


Private Sub cmdClose_Click()

' Launch the new access application
Shell "MSACCESS.EXE " & sDestinationFolderFilename, vbNormalFocus

' Quit the existing application
DoCmd.Quit

End Sub


I´ve tried it with DoCmd.Quit before the Shell call, and after... and can´t
get it to work.

 

[fredg]

Does anyone know how I can close an existing Access application and
automatically launch another one at the same time using code in a button
click event? The following code doesn´t seem to work...

Private Sub cmdClose_Click()

' Launch the new access application
Shell "MSACCESS.EXE " & sDestinationFolderFilename, vbNormalFocus

' Quit the existing application
DoCmd.Quit

End Sub

I´ve tried it with DoCmd.Quit before the Shell call, and after... and can´t
get it to work.

Dim sDestinationFolderFilename as String
sDestinationFolderFilename = "c:\FolderName\DatabaseName.mdb"

Application.FollowHyperlink sDestinationFolderFilename
DoCmd.Quit

 

[Dirk Goldgar]

Does anyone know how I can close an existing Access application and
automatically launch another one at the same time using code in a button
click event? The following code doesn´t seem to work...


Private Sub cmdClose_Click()

' Launch the new access application
Shell "MSACCESS.EXE " & sDestinationFolderFilename, vbNormalFocus

' Quit the existing application
DoCmd.Quit

End Sub


I´ve tried it with DoCmd.Quit before the Shell call, and after... and
can´t
get it to work.

I suspect that your problem is with the call to Shell. If the path to the
new database contains spaces, you need to enclose it in quotes. Try this:

Shell "MSACCESS.EXE " & _
Chr(34) & sDestinationFolderFilename & Chr(34), _
vbNormalFocus

 

[nigelb]

Thanks fredg, this works well.

Does anyone know how I can close an existing Access application and
automatically launch another one at the same time using code in a button

[quoted text clipped - 12 lines]

I´ve tried it with DoCmd.Quit before the Shell call, and after... and can´t
get it to work.

Dim sDestinationFolderFilename as String
sDestinationFolderFilename = "c:\FolderName\DatabaseName.mdb"

Application.FollowHyperlink sDestinationFolderFilename
DoCmd.Quit

 

[nigelb]

Exactly, it was the spaces in the path. Thanks Dirk, this works as well.

However, just a thought... this method assumes Windows knows where MSACCESS.
EXE is installed via the windows environment path? So if I distribute the
access application to other users on other PCs is SHELL "MSACCESS.EXE ..." a
guaranteed method?

Nigel

Does anyone know how I can close an existing Access application and
automatically launch another one at the same time using code in a button

[quoted text clipped - 13 lines]

can´t
get it to work.

I suspect that your problem is with the call to Shell. If the path to the
new database contains spaces, you need to enclose it in quotes. Try this:

Shell "MSACCESS.EXE " & _
Chr(34) & sDestinationFolderFilename & Chr(34), _
vbNormalFocus

 

[Dirk Goldgar]

Exactly, it was the spaces in the path. Thanks Dirk, this works as well.

However, just a thought... this method assumes Windows knows where
MSACCESS.
EXE is installed via the windows environment path?
Right.

So if I distribute the
access application to other users on other PCs is SHELL "MSACCESS.EXE ..."
a
guaranteed method?

No method is guaranteed; however, if you'd like to make this one more
reliable, then since you're running the code from within Access anyway, you
can ask Access where it's running from:


Dim strAccessPath As String

strAccessPath = SysCmd(acSysCmdAccessDir) & "msaccess.exe"
' The call to SysCmd returns the directory containing the Access
executable.

Shell Chr(34) & strAccessPath & Chr(34) & " " & _
Chr(34) & sDestinationFolderFilename & Chr(34), _
vbNormalFocus

That said, using FollowHyperlink may be simpler. However, FollowHyperlink
relies on Access being the registered application to handle the designated
file-extension. That can be a problem in some, admittedly unusual, setups.

 

[nigelb]

Brilliant!! Thank you for this. Works very well.

No method is guaranteed; however, if you'd like to make this one more
reliable, then since you're running the code from within Access anyway, you
can ask Access where it's running from:

Dim strAccessPath As String

strAccessPath = SysCmd(acSysCmdAccessDir) & "msaccess.exe"
' The call to SysCmd returns the directory containing the Access
executable.

Shell Chr(34) & strAccessPath & Chr(34) & " " & _
Chr(34) & sDestinationFolderFilename & Chr(34), _
vbNormalFocus

That said, using FollowHyperlink may be simpler. However, FollowHyperlink
relies on Access being the registered application to handle the designated
file-extension. That can be a problem in some, admittedly unusual, setups.