Access Reading File Path Incorrectly "..\.."

[tdoolittle]

Wasn't sure what to title this post, so hopefully the title makes sense!

I may have gone about this a convoluted way, but it works so that's something! I have a field on a form which houses a hyperlink. The hyperlink is actually a file location on our server and I have it set so we can just drag and drop a file from our server on the link and it turns into a "button" which anyone can click to open up that file from the server. It's super handy.

My buddy was getting annoyed that he had to drag a file from the server location into Access and wanted to drag a file from his desktop into Access. Only issue here is that we couldn't access his file since it's saved locally on his machine.

So I put the following together:

'Check to see if the company folder exists on the server. If it does not exist, create the folder
If Len(Dir("X:\Accounting Shared\Vendor Receipts\" & Me.Company, vbDirectory)) = 0 Then
    MkDir "X:\Accounting Shared\Vendor Receipts\" & Me.Company
End If

'Cleans up the original file location to something Access will read when copying to the new location
FindHash = InStr(1, Me.FilePath, "#")
Location = Right(Me.FilePath, FindHash + 1)
CleanUp = Replace(Location, "#", "")

'Combine other fields on the form to designate the right location for the new file to be copied to
NewPath = "X:\Accounting Shared\Vendor Receipts\" & Me.Company & "\" & Me.OrderNumber & " - " & Format(Date, "MM-DD-YYYY") & ".pdf"

'Make a copy of the file in the right folder with the correct naming convention
FileCopy Clean, NewPath

'Put the new file path location into Access instead of the original drag and drop location
Me.FilePath.Value = "#" & NewPath & "#"

'Confirmation that file has been moved
MsgBox "Your file has been moved to " & NewPath & ". Your original file will now be deleted."

'Delete the desktop/old file
Kill Clean

This all works perfectly...! On my computer...

When I drag and drop a file from my desktop into my front end, Access reads the drag and drop as "#C:Users\Name\Desktop\File.pdf#C:Users\Name\Deskt op\File.pdf#" and then it cleans it up and does its thing. When my buddy drops his file in the field from his desktop Access reads the path as "#..\..\desktop\file.pdf#..\..\desktop\file.pd f#".



Any idea why Access would be truncating the file path instead of showing the full path as mine does? I'm really lost as to why it would be doing that at all...
EDIT: Just to clarify, this really is the file path name Access is housing. A Debug.Print shows that his file is physically found at "..\..\desktop\file.pdf" while mine is found at "C:Users\Name\Desktop\File.pdf"

Also, if I've gone about this a completely horrid way and there is a better solution, feel free to throw out ideas! Like I said, this works for the most part, just trying to figure out why Access is working differently on different machines.

Thanks!!

[ranman256]

DONT use paths with drive letters. Not everyone has the same letter.

Use the UNC full path:
vDir = "\\server\Accounting Shared\Vendor Receipts\"

[tdoolittle]

I set up all our locations, so we all have the same drive letters. I can change that though, as it's not a major deal. My issue is my code breaks right away when trying to determine where the current file on the desktop is located. Again, Access sees his file located at "..\..\desktop" which is not a valid file location so the entire code breaks. My only idea at this point is to use something like:

Replace(CleanUp, "..\..", "C:Users\")

But I feel like there is a reason Access is not reading files correctly on his PC compared to mine.

[June7]

Look at the properties of Computer in Windows Explorer. Organize > Folder and Search options > View > Display the full path in the title bar

Maybe that makes a difference.

[tdoolittle]

Great thought! But our folder options seem to be the same at this point, and both on Windows 7 Pro. This is hurting my brain!

[tdoolittle]

Ended up going with my original plan, kinda. I told Access to replace the "..\.." with where I knew the file would be coming from since we agreed we wanted to use our desktops, but then it started to do some weird stuff.

So the replace function consisted of:


Replace(CleanUp, "..\..", "")


For some reason when I tried to replace the "..\.." with what I wanted, information was being duplicated which leads me to believe even though Access was telling me it wasn't reading information, it really was, but oh well. After the 'Replace' I ended up with this:


Desktop\File.PDF


So I added:


FullFilePath = Environ$("Userprofile") & "\" & CleanUp


Which gave me what I wanted:


C:\Users\Userprofile\Desktop\File.pdf


Don't know if this thread will help anyone... but I'm going to mark it as solved since it's running and performing correctly!

Thanks all!