Problem with database path (Folder Name contains a Space)

[Zikar]

Hello group members,
I am having trouble with starting a database of which the path is specified
programatically. I know that the problem is due to a space in the name of the
folder in which the database resides. I do not have Administrator's
permission to change the name of the folder on the network. I am getting two
error mesaages which are:

(1) The command line you used to start Microsoft Office Access contains an
option that Microsoft Office Access doesn't recognize.

(2)Microsoft Office Access can't find the database file name'C:\Risk.mde'.

The code line is:
RetVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE
Q:\Risk Management\RM.mde", 1)

this code runs if I exclude the space in the folder name
(example:Q:\Risk_Management).

I am also having the same problem if I specify the folder name with a space
by running the database from a shortcut.

Is there a way I can fix this code and the shortcut path to avoid the
"space" issue? I tried including the name string in between brackets and " "
but still did not work. I would greatly appreciate the help I would get to
solve this problem.

p.s. I am sure someone would be wondering, "why doesn't he use the server
path instead of the drive path?". This I will try as soon as I can solve the
"space" issue.
Thanks

 

[Dirk Goldgar]

Hello group members,
I am having trouble with starting a database of which the path is
specified
programatically. I know that the problem is due to a space in the name of
the
folder in which the database resides. I do not have Administrator's
permission to change the name of the folder on the network. I am getting
two
error mesaages which are:

(1) The command line you used to start Microsoft Office Access contains an
option that Microsoft Office Access doesn't recognize.

(2)Microsoft Office Access can't find the database file name'C:\Risk.mde'.

The code line is:
RetVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE
Q:\Risk Management\RM.mde", 1)

this code runs if I exclude the space in the folder name
(example:Q:\Risk_Management).

I am also having the same problem if I specify the folder name with a
space
by running the database from a shortcut.

Is there a way I can fix this code and the shortcut path to avoid the
"space" issue? I tried including the name string in between brackets and "
"
but still did not work. I would greatly appreciate the help I would get to
solve this problem.

Force the code to enclose the paths in quotes, embedded in the argument
string:

RetVal = Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""Q:\Risk Management\RM.mde""", 1)

I think I have the quoting right in the above. Each quote inside the string
literal has to be doubled up.

 

[dymondjack]

To get around the space issue, you'll have to effective enclose the path with
quotes that are readable when it's processed. I can give an example using
the cmd line...

strCmd = "CMD.EXE 'C:\Program Files\Folder\Folder\Filename'"
Shell strCmd

This should enclose the path in quotes so the cmd reads it as one line.

As an alternative, you can go to MVPS.org access web, where there is an api
to convert filenames from the long to short format and vice versa, which
should remove any requirement for quotes.

I've never used the access command line arguments so I can't advise what
might be wrong there.

Try changing this

RetVal = Shell("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE
Q:\Risk Management\RM.mde", 1)

to

RetVal = Shell("'C:\Program Files\Micorsoft Office\OFFICE11\MSACCESS.EXE
Q:\Risk Management\RM.mde'", 1)

(I added a single quote at the beginning and end of the string).

You can probably shell the file directly without having to specify access,
assuming you have only one version of access installed and it's properly
registered for the filetype. Something like

Shell "'Q:\Risk Management\RM.mde'"

hth
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Zikar]

Dirk, thanks for the good hint you provided me. It works on the case I gave
in relation to the Shell method. However, I realised that I still have
another issue with specifying a path string that has a space in the directory
name. My code is:
stPath = "Q:\Corporate\Risk Management\System\Myfile.mde"

I am sure this requires a different solution as it works without the space.
I would greatly appreciate your help with this one too.

Kind regards

 

[Dirk Goldgar]

Dirk, thanks for the good hint you provided me. It works on the case I
gave
in relation to the Shell method. However, I realised that I still have
another issue with specifying a path string that has a space in the
directory
name. My code is:
stPath = "Q:\Corporate\Risk Management\System\Myfile.mde"

I am sure this requires a different solution as it works without the
space.
I would greatly appreciate your help with this one too.

In what context is that causing a problem? The line of code you quoted is
completely correct, so far as I can see, so I find it hard to believe that
this line is itself raising an error. On the other hand, if you want to use
that variable in your call to the Shell method, you'd still have to wrap
some
quotes around it in building the argument, like this:

Dim strAccessPath As String
Dim strDBPath As String

Const Q As String = """"

strAccessPath = _
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

strDBPath = "Q:\Corporate\Risk Management\System\Myfile.mde"

RetVal = Shell( _
Q & strAccessPath & Q & " " & Q & strDBPath & Q, _
1)

If that's not the sort of thing you're looking for, please explain post the
code that is raising the error, and explain what you're trying to do.

 

[Zikar]

I just found out that the code is fine in relation to the quotes. The problem
actually lies in the type of file. It seems that the .mde type of file cannot
be recognized for some reason. I concluded this after I tested it with a .mdb
file and works. for my luck now I have locked on the source of the problem.

I am actually running the following code:

Dim fIsFileDIR As Integer
Dim stPath As String

F_TestFilePath = "0"

stPath = "Q:\Corporate\Risk Management\System\MyFile.mde"

fIsFileDIR = Len(Dir(stPath))

If fIsFileDIR > 0 Then
F_TestFilePath = "1"
Else
If fIsFileDIR = 0 Then
F_TestFilePath = "2"
End If
End If

This code is returning the value "0" although the directory actually exists.

By refering to a file named MyFile.mdb, which also exists in the same
directory, the code returns a value "17" which means the directory exists. Do
you have a solution for this issue of the .mde type? Thanks

 

[Zikar]

Thank you for your suggestion. Unfortunately the type of quotes did not work
with my code but Dirk's proposal did work well.

I also thank you for the proposed MVPS.org access web that refers to an api
to convert filenames. I have seen this before but did not know how I should
call the function and where should I place the code. I would appreciate any
clarification on how to execute this procedure. Thanks.

 

[Dirk Goldgar]

I just found out that the code is fine in relation to the quotes. The
problem
actually lies in the type of file. It seems that the .mde type of file
cannot
be recognized for some reason. I concluded this after I tested it with a
.mdb
file and works. for my luck now I have locked on the source of the
problem.

I am actually running the following code:

Dim fIsFileDIR As Integer
Dim stPath As String

F_TestFilePath = "0"

stPath = "Q:\Corporate\Risk Management\System\MyFile.mde"

fIsFileDIR = Len(Dir(stPath))

If fIsFileDIR > 0 Then
F_TestFilePath = "1"
Else
If fIsFileDIR = 0 Then
F_TestFilePath = "2"
End If
End If

This code is returning the value "0" although the directory actually
exists.

By refering to a file named MyFile.mdb, which also exists in the same
directory, the code returns a value "17" which means the directory exists.
Do
you have a solution for this issue of the .mde type? Thanks

There must be something missing, because there is no way that code -- by
itself -- could return a value of "17". That is, assuming that
F_TestFilePath is the value you are returning. Is that the name of the
function that contains this code?

According to that code, F_TestFilePath could return a value of "0", "1", or
"2", and that's all. Further, it could only return a value of "0" if an
error was raised such that the code never executes the statements that test
the value of fIsFileDIR. So you need to either post the rest of the code or
correct your statement of what the function returns.

Also, are you trying to test for the existence of a directory, or of a file
in that directory? You said "directory", but your code seems to be testing
the existence of a file, not a directory. Please explain what you want this
function to do.

I can't think of any reason that an .mde file would not be recognized by the
Dir() function, so I think you are misinterpreting what's going on.

 

[Zikar]

Yes Dirk, your analysis is correct. I meant with the F_TestFilePath to return
a value of "0", "1", or "2", so I would use those variables as an outcome
that decides on another scenario in another event about whether to execute
that event or not.

I was actually trying to test for the existence of a directory when I first
wrote the code, but now I am trying to test the existence of the file as
expressed in the path. I can see that now after you depicted the possible
problem but have no clue of how to change it to do so. I would greatly
appreciate your help in this matter. Thanks.

 

[dymondjack]

Sorry about that, I thought I had used the single quotes in this same context
before, apparently I wasn't remembering correctly though. In any case, Dirk
seems to have things straightened out there.

As far as the long/short filename api, I think the method of inserting
double quotes would be a better route. It seems as though the short
filenames are a dying breed (I don't think an OS has used them since Win95,
but I could be wrong), and the quotes are the "proper" way to handle the
situation. But, if you still want to give it a shot I can certainly take a
look at the api and some of your code and see how it might be used.

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Dirk Goldgar]

Yes Dirk, your analysis is correct. I meant with the F_TestFilePath to
return
a value of "0", "1", or "2", so I would use those variables as an outcome
that decides on another scenario in another event about whether to execute
that event or not.

I was actually trying to test for the existence of a directory when I
first
wrote the code, but now I am trying to test the existence of the file as
expressed in the path. I can see that now after you depicted the possible
problem but have no clue of how to change it to do so. I would greatly
appreciate your help in this matter. Thanks.

Please post the whole code for the function. Before you go to that trouble,
though, you may want to look at these versions of functions to answer the
questions, "Does the file exist?" and "Does the folder exist?", posted by
Allen Browne:

http://www.allenbrowne.com/func-11.html

It could be that Allen's functions will suit your needs.

 

[Zikar]

Dirk, your help is greatly appreciated.
Cheers!

Force the code to enclose the paths in quotes, embedded in the argument
string:

RetVal = Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""Q:\Risk Management\RM.mde""", 1)

I think I have the quoting right in the above. Each quote inside the string
literal has to be doubled up.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)