Shell Function With Spaces in Parameters

[wezhogan]

Hi again. I have another problem that I hope someone can help me with. I am using the shell function to open database B from a form in database A. The reason I am using the shell function is because the databases I put together will be used in Acccess Runtime instead of regular Access so the create object option is not available. Here is the code I am using for the shell function within a module of database A:

Function OpenRemoteForm(strDbFile As String)
Dim ShellInt As Integer

ShellInt = Shell("MSACCESS.EXE " & strDbFile, vbNormalFocus)
End Function

The click event that fires the shell function from the form in database A is:

Private Sub bScreening_Click()

On Error GoTo Err_Form_Open
OpenRemoteForm "C:\Users\New Beginnings\Desktop\Louise Drafts\PreScreening.accdb"
DoCmd.Close acForm, "frmForms", acSaveNo

Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Form_Open

End Sub

The problem I am having has to do with the spaces in the path for database B. The sub in the click event calls the shell function and passes the path to a string variable. I have tried putting single quotes and double quotes in a number of different places but am still having problems. Does anyone know exactly how the string needs to be formated to be passed correctly?

Thanks in advance

 

[boblarson]

I don't think it is the path that is the problem as I have done it before with no issues. But using "MSAccess.exe" probably is the problem. I think you are going to need to include the full path:

Shell("C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE " & strDbFile, vbNormalFocus)

(where 14 is if you have Office 2010, 12 if you have Office 2007, and 11 if 2003)

The only problem could be is if someone has it installed to another location than the standard location. So you might need this function:

http://access.mvps.org/access/api/api0023.htm

But you might have better luck using this Shell Execute function:

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

 

[ChrisO]

I can’t test this under all conditions but it should work:-

Shell SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE " & strDbFile, vbNormalFocus

Chris.

 

[wezhogan]

No it is definately the file path. I get an error saying that it cannot find the file New.mdb because it stops at the first space which is between "New Beginnings".

 

[mdlueck]

No it is definately the file path. I get an error saying that it cannot find the file New.mdb because it stops at the first space which is between "New Beginnings".

So you need to also wrap the database filespec in double quotes as well. If you are specifying the filename fully qualifying then double quote before the drive letter, ending double quote at the end of the filename.

"Path to Access" "Path to Database"

So to update a prior post:

I can’t test this under all conditions but it should work:-

Shell SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE " [COLOR=Red][B]& chr(34) & strDbFile & chr(34)[/B][/COLOR], vbNormalFocus

Chris.