How to create a drag and drop field for files?

[Seth Schrock]

I'm trying to create an easy way to get references to files into my database. I want users to be able to drag and drop a file from their desktop (or anywhere else on their computer) onto a control of some kind and have the file get placed on the server with a hyperlink in the database to the file on the server. In reading stuff online, I found out that the Hyperlink data type makes it so that you can drag a file onto a textbox and it will automatically create a hyperlink to that file. My idea was then to use that hyperlink to be able to do a FileCopy to the server and then change the value in the control to be the location on the server instead of the user's PC. When tried, the hyperlink that gets added to the text box when the file is dropped onto it is not a regular file path that FileCopy can use. It starts with ... and then the file path inside pound signs and then duplicates that again. Also, it doesn't include the starting part of the file path so I can't use Left(), Right(), or Mid() functions to parse it down to a usable file path. Now I'm out of ideas.

[zmbd]

Seth,
Search thru Bytes on hyperlink fields, Rabbit (I think) has helped a few others out with how these fields are formatted.

You should also take a look at: Introduction to Hyperlink fields

[Seth Schrock]

Well, I now know why it had all the parts to the value stored in the textbox. I'll have to see if I can parse out the link itself and use that, but I doubt that it will work once I start pulling it apart.

Is there another way to do drag and drop that would be easier?

[TheSmileyCoder]

There are usually quite many ways to do things in access.

I have not worked with hyperlink fields in Access, so if I end up saying something wrong, please excuse me.

A quick test revealed that in Ac2010 when I drag and drop a single file, I get a RELATIVE path inserted automatically in the field. That means alot of "../" to backstep directories with the files I tried. I presume you have the same issue, and that you would rather have the absolute path?

[Seth Schrock]

Assuming that I will use the FileCopy method to copy the original file to the server location, I would need the absolute path as the source. And yes, I'm getting the ... at the beginning of the path. From reading online, hyperlink fields are a pain to work with and it is easier to use plan text fields and just make them hyperlinks manually. I'm not against this, but I then loose the ability to drag and drop my files into the textbox and have it get the file path. Or is there some VBA code that can get the file path for me?

[TheSmileyCoder]

Personally I use the ListView Active X control which is bundled with the treeview in the Microsoft Common Control Library. The listview has a Drag-n-Drop event which can be coded to receive files, and do a filecopy.

However the listview will not work in 64 bit. So whether that is acceptable or not is up to you. The code can get a bit complicated since Drap-n-Drop of multiple files can be a bit messy :)

[Seth Schrock]

When you say multiple files, do you mean all at once or many files separately? Is that 64 bit computer or Office installation?

[TheSmileyCoder]

I mean selecting multiple files and dropping them all at once into your form. Not impossible to deal with, but does require a bit extra. Furthermore dragging a file from an outlook email for example is NOT the same as dragging a file from your desktop.

I don't mean this to discourage you, just to make sure you are aware of the path ahead.

The treeview (and listview) will work fine in Windows 32 AND/OR 64 bit, but will ONLY work with office 32bit, and thus NOT with office 64 bit.

[Seth Schrock]

I can just say that the users can only do one file at a time (maybe put some code in that blocks it if more than one file is attempted?). That is no big deal. I had already discovered that about the emails. Is it possible to drag from an email or do I just have to have them drag to the desktop (or anywhere else) and then drag it into the database?

[TheSmileyCoder]

I have a warning that says 1 file at a time if multiple files are uploaded.(Since my process involves alot of registration about the document) It gives a similar warning if a user tries to drag a folder.

I am sure its possible to get the attachments out of outlook, just haven't looked into it. I check for it, and give a warning.

I will return with some sample code tomorrow. Its a bit late here, and I want to do it proper with some details and explanation.

[zmbd]

>>>UglyFace_n_B ad_Words<<<<<
Sometimes MS really just gets under my skin with how they do things!

OK this took me forever to figure out and haveing the distraction of the kids didn't help (well, actually it did to some degree in that they kept me from getting too deep in any one direction).

Reading thru my texts there is a base hyperlink path property that showed up in V2003 for sharepoint and other web uses.

That led me to think about the "File://" pre-pend when I'm trying to open a HTML from the local drive.

Which led me to try that in the base hyperlink path property for the database. (file... in V2010 look to the right, click on the small line of text that says... show database properties - summary tab)

When that property is not set then the path returned on a drag and drop appears to randomly be either relative or absolute; however, when I set the value to the "File://" then the path seems to always return the absolute path.

Now, because this is the home PC I haven't tried this on network files, nor have I tried this on outlook attachments.... . someone with a better understanding than I will have to handle the outlook attachments

However... because MS has really torqued my jaws on this...

Here's a function that will toggle the value from VBA.
Run the first time to set the value to the "File://"
Run a second time to clear.
It will return a "0" if all went well
It will return a "1" and popup a message block if any unexpected errors occured.

This is a modification of the code I use to set custom properties for my databases.

Option Compare Database
Option Explicit

Public Function fnc_SetorClearHyperlinkBase()
    Dim zdb As DAO.Database
    Dim zdoc As DAO.Document
    Dim zprp As DAO.Property
    Dim zstrPathApp
    Dim zstrErr As String
    '
    'Set for error trap
    On Error GoTo zErr_Handler
    '
    'set the base hyperlink file (could use the current database root)
    'use File:// to force file condition
    zstrPathApp = "File://"
    '
    'Set the pointers to the current database and properties collection
    Set zdb = CurrentDb
    '
    'Now, here's the stumbling block... I was using just "Summary" as it shows
    'on the tab in the dialog...
    'Then reading thru my references, there was an example to set the title in
    'summary... and the value was, "SummaryInfo"
    'once I found that... this almost wrote itself!
    '
    Set zdoc = zdb.Containers("Databases").Documents("SummaryInfo")
    Set zprp = zdoc.Properties("Hyperlink base")
    '
    'If we didn't error out on 3270 then remove the Hyperlink Base from the properties collection
     zdoc.Properties.Delete ("Hyperlink base")
    '
    'return zero if no error
    fnc_SetorClearHyperlinkBase = 0
    '
z_returnfromerror:
    '
    'clean up
    Set zprp = Nothing
    Set zdoc = Nothing
    Set zdb = Nothing
Exit Function
'
zErr_Handler:
    If Err.Number = 3270 Then
        '
        ' property not found in the collection so create it and add
        Set zprp = zdoc.CreateProperty("Hyperlink base", dbText, zstrPathApp)
        zdoc.Properties.Append zprp
        Resume z_returnfromerror
    Else
        
        zstrErr = "Error: " & Err.Number & ": " & Err.Description
        MsgBox zstrErr, , "fnc_SetHyperlinkbase"
        fnc_SetorClearHyperlinkBase = 1
        Resume z_returnfromerror
    End If
End Function

Insert a new module, copy the above to it, compile, and thumb nose at MS for not making this simpler.

BTW:
I use the dialog box and ask the user to find the file and then store that location in a normal text field instead of all of this hyperlink stuff... took the advise from AB website about this (where I link you to in my first post).

[TheSmileyCoder]

There are certain advantages to using Hyperlink. The major is that they are not dependent on a dll or active X control.

The downside is they must be bound, since the control is still just a textbox, and the behavior of the control depends on it being bound to hyperlink type field. If anyone has a way to make a unbound control that works in the same way as a control bound to a hyperlink field I would like to know.



Any way, I cooked up a sample db (2003 format) that illustrates how documents can be dragged onto a listview control, and saved from there.
DragNDropFiles.zip
Hope that helps.

[ADezii]

You can select a single or multiple Files from Windows Explorer, and then Drop them into a TreeView Control. The File Names, complete with Absolute PATHs, then become single Nodes under a Root Node. Once the Drag-N-Drop process has completed all the Nodes can be referenced and the Files copied to a pre-determined PATH on the Server. Clicking on any Node of the TreeView can then Open the associated File on the Server. Nothing is stored within the Database.

[TheSmileyCoder]

Hi ADezii. I just tried that and nothing happens. I don't know if any of the default settings need to be modified. Sure I could code this into the treeview since that also supports OLE drag and drop, but you saying this behavior is built into the treeview somehow?

[Seth Schrock]

@Smiley The file path isn't getting put into the table. Is it supposed to or would you recommend having the manual hyperlink append the file path to it?

@ADezii I would love a tree view as I plan on grouping files by type, so having the built it grouping would be great. However, I'm not very familiar with Treeview controls (or any ActiveX control for that matter). Do you have an example, or a book/website that I could read to learn about it?