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_Bad_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?

[zmbd]

With the solution I've given in post#11 one doesn't need an external reference library nor anything beyond the current core VBA. This forces the absolute path to be stored in the hyperlink field and the property is easily toggled from the "set" state back to default.

I love the fact that there are so many ways to work around this; however, I'm a great advocate of not using more external library references than absolutly needed to get a task done... even if they are nicer from a GUI stand point.

As it stands now, with V2013 around the corner, Win7-67bit OS becoming the mainstream with the 64Bit office soon to follow, Win8 and possibly another OS release within the year - using ActiveX controls designed for 32bit is somewhat scary and in fact in post #8 TheSmileyCoder points out just one such break. And we never know when MS is going to mess up another Kill Bit in an Office or OS security update.

[ADezii]

@Seth Schrock
Seth, as soon as I get a chance I'll see if I can put a small Demo together for you.

[Seth Schrock]

@ADezii That would be great.

@Z From where would I call your function? I assume that the textbox would still need bound to a hyperlink field?

[TheSmileyCoder]

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?

No, the filepath is not meant to be put into the table. The setup I used in the demo is a semi-relative path. This means you can move the document storage folder without breaking the application. A copy is placed into the storage folder, subdivided by case number.

[ADezii]

@Seth:
I created a demo for you that will enable you to dynamically Drag-N-Drop any File(s) into a TreeView Control.

1. Open the Demo and click on the Explorer Command Button.
2. Select any File/Group of Files, hold down the SHIFT Key, then DragNDrop into the TreeView Window.
3. When finished, expand the Main Node to view all the Files that have been dropped, minus the extensions. The Absolute PATHs to each File are the actual Display Values for that Node.
4. Click on any File Name to see info related to that File.
5. Critical Code is contained in the OLEDragDrop() Event of the TreeView Control.
6. I have not tried this, but once you are done you can iterate through all the Nodes and perform a Bulk Copy Operation to your Server.
7. The File Names/PATHs will not persist in the TreeView, so at some point they will need to be stored internally.
8. Each Node/File can now be opened from the TreeView via ShellExecute().
9. Any questions please feel free to ask.

[ADezii]

@TheSmileyCoder:
Be sure to set the OLEDropMode of the TreeView to 'Manual'.

[TheSmileyCoder]

@ADezii
I got the impression from your previous post that it would be possible to have the treeview show the file paths without adding any code. I can't seem to get that functionality, and I notice that your example include code to get the filepaths.

I just want to make sure: Is there a no-code way to add the files directly to a treeview? Its merely a matter of curiosity.

[zmbd]

Seth,
What I did in the test DB was drop the function in a standard module and then called it in the on_open event of the test form to set the "file://" as the base and then called the function again on the on_close event of the form to clear things back to the default state.

[Seth Schrock]

That would work. I had put it in the AfterUpdate event of the control, but as you can imagine, that made it work once, and then not, and then it did... I ended up just turned it on and then removed it from the event. It at least allowed me to play with the results to trim it down to get the file path without the display part and just the file name so that I can display just the name, but still have the link to the full path.

I'm in the process of creating a test database to try the listview, treeview, and just the plain method that Z has recommended to see what will fit my situation the best. I will let you know the test results once I get them.

[Seth Schrock]

Well, I got all three methods to work and they work well. My boss ended up liking the treeview the best so that is what I have gone with.

Thanks to everyone for you help, insight, and time spent creating samples. They were very, VERY helpful. Also, your tutorials on treeviews were a big help Smiley!

[TheSmileyCoder]

Always happy to be of assistance.