VB File Path Spaces

junkbot · #1 (**permalink**) 08-25-11, 11:01

Good Afternoon,

I am using the following script to allow multiple users to login to a database 'front end' (forms, queries, etc) with the table held in another database but link.
However the network drive where the database(s) are held, some of the parent folders have spaces in the names which is returning an error. I'm looking for some help how to correct the script to either allow or remove the spaces so that the database can be opened.

Code:

Set WshNetwork = CreateObject("WScript.Network")
GetUser = WshNetwork.UserName
Set WshNetwork = Nothing

LUName = "computername\folder with spaces\PB_Database_V1.1.mdb"
'Note: or LUName = "UNC Name"
oldname = LUName

newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

retval = 0
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

retval = objFSO.CopyFile(oldname, newName, True)

Set objFSO = Nothing

Dim objShell
Set objShell = CreateObject("Wscript.Shell")

objShell****n "MSAccess.exe " & newName

Set objShell = Nothing

Any help appreciated....

Thanks,

J

loquin · #2 (**permalink**) 08-25-11, 16:17

Think about running a command from the command line. If the path contains spaces, you need to use a quoted path name.

When scripting in windows, I believe you'll need to replace

Code:

LUName = "computername\folder with spaces\PB_Database_V1.1.mdb"

(which is the equivalent of computername\folder with spaces\PB_Database_V1.1.mdb)

with

Code:

LUName = """" & "computername\folder with spaces\PB_Database_V1.1.mdb" & """"

OR

Code:

LUName = """computername\folder with spaces\PB_Database_V1.1.mdb"""

either of which are the construction of a string containing "computername\folder with spaces\PB_Database_V1.1.mdb"

Four quotes in a row represents a single quote mark. Two quotes in a row, within a quoted string, are escaped as one quote mark, within the quoted string.

Your string would then contain leading and trailing quote marks, within the string.

junkbot · #3 (**permalink**) 08-26-11, 04:07

Thanks loquin

However I'm getting a VB error now for line 15.

Code:

retval = objFSO.CopyFile(oldname, newName, True)

Char 1, bad file name or number, code 800A0034

I have tried both of the suggestions below and they both return the same error.

Do you have any other ideas?

Much appreciated.

Junk

Internet Services

Web Development

Digital Marketing

Consumer Tech