Paths containing spaces in shortcuts and MSAccess97VBA 1

[c0i0nes]

I use WindowsXP Professional with several versions of Access and need to use a specific version to open a database over our network. Both the path to the version of Access I want to use (97, not the default Access2002) and the path to the database file I need to open contain spaces in the folder names like:

c:\Program Files\Microsoft Office\Office\MSAccess.exe z:\Shared Data\Databases\Access97 Databases\Accounts.mdb

Enclosing the full string in quotes ("&quot does not work, and neither does enclosing both paths in quotes, or nesting quotes. Although this is not an actual path in the example above, error messages would complain that z:\Shared.mdb cannot be found, taking the first space in the target path as the end of the string. The problem appears the same for both ordinary shortcuts and for expressions in VBA Subs. Apart from changing the folder names so they do not contain spaces, is there a solution?

 

[vistor]

Try putting %20 where the space is:

R:\templates\my%20file.doc

 

[c0i0nes]

Thanks 'Visitor', but I have had no success using the "%20" in place of spaces. I would be interested in getting information about the use of the "%20" and "%%20" construct in place of spaces in VB and registry entries. Can you help? Google, of course, does not recognise a search on "%20"!

 

[vistor]

Sorry, That has been my solution. Can you rename the files and take out the space?

 

[c0i0nes]

Thanks for the rapid reply, 'Visitor'. Yes this the only solution that I find works for both the shortcut I use from my desktop and for the code under a button on my database admin control form. I had hoped there was a more elegant solution that would cope with paths with spaces. I would still be interested in reading some references to the use of the "%20" construct as a replacement for spaces if you can point me in the right direction!
Thanks.

 

[vistor]

The %20 is just a general rule that seems to be basic knowledge, I don't know of any documentation on it. Try a google search under removing spaces in file names


Also never have a space in a field name in a table, If you want to use a seperator, use an underscore.
Customer_FirstName.

 

[c0i0nes]

The only thing I know is that %20 refers to ascii hex 20 (020H), which is a space character. It's just that I cannot get specific information on its usage in either shortcuts, or VB. It seems to be, as you say "common knowledge" with its origins somewhere in the distant past of programming!
Fortunately, I have control of the names I give to database elements, such as tables and fieldnames. Unfortunately, I have no such control over folder names dreamed up by network administrators, Microsoft, or users!

 

[FancyPrairie]

The shortcut target should look like this (Note the double quotes)

"c:\Program Files\Microsoft Office\Office\MSAccess.exe" "z:\Shared Data\Databases\Access97 Databases\Accounts.mdb"

 

[c0i0nes]

I wish it worked FancyPrairie, but the shortcut opens Access97 with the warning:

Microsoft Access can't find the database file 'z:\Shared Data\Databases\Access97 Databases\Accounts.mdb.'

Make sure you entered the correct path and file name.

The single quotes seen here are introduced by Access

 

[vistor]

Try single quotes

"c:\Program Files\Microsoft Office\Office\MSAccess.exe' 'z:\Shared Data\Databases\Access97 Databases\Accounts.mdb"

 

[WayneRyan]

c0h0nes,

Are you sure that nesting quotes doesn't work?

The following is a launcher for Access and the only way
that it will work is with the following quotes:

strAccessPath = """C:\Program Files\Microsoft Office\Office\MSACCESS.exe """
strDBPath = "C:\EFSfy02\EFSTimeCard.mdb "
strWorkGroup = "/wrkgrp ""C:\EFSfy02\system.mdw"" "
strOther = "/Nostartup"

strCommandLine = strAccessPath
strCommandLine = strCommandLine & strDBPath
strCommandLine = strCommandLine & strWorkGroup
strCommandLine = strCommandLine & strOther

wsh.run strCommandLine

hth,
Wayne

 

[FancyPrairie]

I have to agree with WayneRyan (are you sure the quotes don't work?). To test out part of it, goto the Start button and on the Windows task bar, select Run. Then type
"z:\Shared Data\Databases\Access97 Databases\" to see if you have the correct path.

Every database I've put together uses the same syntax (whether it's Access 95, 97, or 2000). I put double quotes around the path\name for msaccess.exe and around the path\name of my database.

 

[c0i0nes]

Thanks for all your help I am partway there! Apologies to FancyPrairie. Permissions on the server were not set up correctly. Once I had sorted out the permissions on the Z:\ drive, the shortcut worked fine using your method, so I have awarded you a star!
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "z:\Databases\Raynauds Database\RIS.mdb"

I am still working on the code to call Access97 and open the database on the path with the space in the folder name.

I have a database form in Access97 which I use to open all my active databases by selecting command buttons. Some of the databases are Access v 2.0, and others are being developed in Access XP, hence the need to have paths to the version of Access I need as well as the location of the MDB file.
The following Sub works fine for a path without a space in it:

Private Sub Command80_Click()
On Error GoTo Err_Command80_Click

Dim stAppName As String
'the following is a single unbroken line
stAppName = "c:\Program Files\Microsoft Office\Office\msaccess.exe z:\Databases\haq.mdb"
'line ends
Call Shell(stAppName, 1)

Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
MsgBox Err.Description
Resume Exit_Command80_Click

End Sub
------------------------
Whereas the following doesn't:

Private Sub runris_Click()
On Error GoTo Err_runris_Click

Dim stAppName As String
'the following is an unbroken single line
stAppName = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE z:\Databases\Raynauds Database\RIS.mdb"
'line ends
Call Shell(stAppName, 1)

Exit_runris_Click:
Exit Sub

Err_runris_Click:
MsgBox Err.Description
Resume Exit_runris_Click

End Sub
--------------------

This sub brings up the following warning:


The command line you used to start Microsoft Access contains an option that Microsoft Access doesn't recognise.

exit and restart Microsoft Access using valid command-line options.

-------------------

Then a new instance of Microsoft Access opens up a window with the following alert:


Microsoft Access can't find the database file
'z:\Databases\Raynauds.mdb.'

Make sure you entered the correct path and file name.

Eventually after considerable trial and error, the following code runs properly!

Private Sub runris_Click()
On Error GoTo Err_runris_Click

Dim stAppName As String

stAppName = """C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"" ""z:\Databases\Raynauds Database\RIS.mdb"""
Call Shell(stAppName, 1)

Exit_runris_Click:
Exit Sub

Err_runris_Click:
MsgBox Err.Description
Resume Exit_runris_Click

End Sub

--------------
thanks again for your support.