Problem locating file using Dir() function and path using UNC (Universal Naming Conventions) in Access 2003

[stuart.medlin]

I have an Access 2003 application that I recently converted from
Access 97. I have a routine in which I use to export the data that
the users have keyed into a text file. This text file resides on a
network drive where the back end database resides so that multiple
users can write to the same file. I have set up the path to the back
end (under the Link Manager) using the UNC (univ. naming convention).
When this export routine runs, I need to check to see if the file
exists or not (so I can either append the data or create a new text
file with this data)

The previous version I was able to use the function
Application.Filesearch to locate the file without a problem. However,
I switched to using the Dir() command (because Filesearch did not work
in 2003) to locate the file, but I get an error "Error Number 52: Bad
File Name or Number" when I use the Dir command to locate a file that
is defined with the UNC (i.e. \\agrhq01\data1\...\pest\paa200.74)

I can map the actual path in the link manager, but preferred using the
universal name if possible.

Thanks in advance!

Stuart

The code follows:
------------------------------------------------------------------------------------------
'if output file is found, append data from the three text files to the
'end of the current file. "/b" is added as a command line option to
'prevent EOF character from being copied to the file

If dir(paafile) <"" Then

cmdmsg = Environ$("ComSpec") & " /c " & _
"copy " & paafile & "+" & _
card1file & "+" & card2file & "+" & card3file & _
" " & paafile & " /b"

ExecCmd cmdmsg

'Output file is not found, so Concatenate the 3 text files into one
file
Else
cmdmsg = Environ$("ComSpec") & " /c " & _
"copy " & card1file & "+" & _
card2file & "+" & card3file & " " & _
paafile & " /b"

ExecCmd cmdmsg

End If

Public Sub ExecCmd(cmdline$)

Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ReturnValue As Integer

' Initialize the STARTUPINFO structure:
start.cb = Len(start)

' Start the shelled application:
ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

' Wait for the shelled application to finish:
Do
ReturnValue = WaitForSingleObject(proc.hProcess, 0)
DoEvents
Loop Until ReturnValue <258

ReturnValue = CloseHandle(proc.hProcess)
End Sub

[Allen Browne]

The UNC should work find with Dir().

There may be an issue with using non-registered file types. See if it makes
any difference if you use the name paa200.txt instead of 200.74 for the
file. It does for some operations (such as TransferText.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<st***********@ncmail.netwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...

>I have an Access 2003 application that I recently converted from
Access 97. I have a routine in which I use to export the data that
the users have keyed into a text file. This text file resides on a
network drive where the back end database resides so that multiple
users can write to the same file. I have set up the path to the back
end (under the Link Manager) using the UNC (univ. naming convention).
When this export routine runs, I need to check to see if the file
exists or not (so I can either append the data or create a new text
file with this data)

The previous version I was able to use the function
Application.Filesearch to locate the file without a problem. However,
I switched to using the Dir() command (because Filesearch did not work
in 2003) to locate the file, but I get an error "Error Number 52: Bad
File Name or Number" when I use the Dir command to locate a file that
is defined with the UNC (i.e. \\agrhq01\data1\...\pest\paa200.74)

I can map the actual path in the link manager, but preferred using the
universal name if possible.

Thanks in advance!

Stuart

The code follows:
------------------------------------------------------------------------------------------
'if output file is found, append data from the three text files to the
'end of the current file. "/b" is added as a command line option to
'prevent EOF character from being copied to the file

If dir(paafile) <"" Then

cmdmsg = Environ$("ComSpec") & " /c " & _
"copy " & paafile & "+" & _
card1file & "+" & card2file & "+" & card3file & _
" " & paafile & " /b"

ExecCmd cmdmsg

'Output file is not found, so Concatenate the 3 text files into one
file
Else
cmdmsg = Environ$("ComSpec") & " /c " & _
"copy " & card1file & "+" & _
card2file & "+" & card3file & " " & _
paafile & " /b"

ExecCmd cmdmsg

End If

Public Sub ExecCmd(cmdline$)

Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ReturnValue As Integer

' Initialize the STARTUPINFO structure:
start.cb = Len(start)

' Start the shelled application:
ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

' Wait for the shelled application to finish:
Do
ReturnValue = WaitForSingleObject(proc.hProcess, 0)
DoEvents
Loop Until ReturnValue <258

ReturnValue = CloseHandle(proc.hProcess)
End Sub