Almost working Drag & Drop

[Juice]

Hi everyone,

A have a somewhat working drag and drop solution, just needs some fine tuning. I have a combo box and a table behind it that has a Hyperlink. If its just a combo box, its not working (you need a table with a Hyperlink table field), but since i need other functions, like "Save as" or "Send it in mail", I can add it in the table and code on it.

When you drop a file onto the combo box (sometimes you need to click the empty text part), it reads out the file path. The problem is that its not getting the full path.

Example: I drop a file with a path "c:\1\example.pdf" but when i read out the text from the combo box, it looks like this: "..\..\..\1\example.pdf" (in Hyperlink terms you see this: ..\..\..\1\example.pdf#..\..\..\1\example.pdf#

Fun part is that i have an older mdb thats used in an office and it works fine, but i cant recreate it.

The older one and this one is made in access 2003 on Win7 OS (people that use it dont want to upgrade)

Anyone has any idea how to set it up so i can get a "normal" path where the drive letter is visible?

 

[Nixversteher]

Without downloading your Test.mdb and the way you describe the path return, I suspect that for some reason a relative path is returned. This probably refers to the position of your MDB. Just add the path of your MDB (without the file name of the MDB) and the relative path together and you should have a complete path that Windows understands.

 

[MajP]

Here is a working file drag and drop demo. I am interested is some code using native controls so would like to see what you really have.

 

[Juice]

I cannot imagine this thing does anything nor can I even guess the issue. There are no tables, no code, no means to do drag and drop. There is nothing there. Unless there is some hidden stuff somewhere. You will need to provide something realistic to even have a conversation on how to do drag and drop..

What you see in the test mdb, thats the base of it. I somehow made it work 10+ years ago and i cant seem to figure out how

 

[Juice]

Here is a working file drag and drop demo. I am interested is some code using native controls so would like to see what you really have.

View attachment 118117

Thanks i test it tomorrow. I post you some code tomorrow too, but the gist of it is i get the path, copy the file to a network drive then put that network path in a table and for the coworker how handles the bills and stuff. And when she click on the field that has the network link, it opens.

 

[Gasman]

What you see in the test mdb, thats the base of it. I somehow made it work 10+ years ago and i cant seem to figure out how

And then the code went walkabout?

 

[sonic8]

I have a combo box and a table behind it that has a Hyperlink. If its just a combo box, its not working (you need a table with a Hyperlink table field),

There are no tables, [...]

I didn't look at the file, but just reading the two statements quoted above gives me an idea where the problems lies.

 

[sonic8]

Anyone has any idea how to set it up so i can get a "normal" path where the drive letter is visible?

Setting the HyperlinkBase property of the database to "file:///" could do that.

 

[MajP]

I see now how this is supposed to work. I was thrown off by the combo box. I do not know how you can get a absolute path /UNC from a relative path.
Anyways what you are describing is a relative path and not a UNC/Absolute Path.

The HyperlinkAddress property can contain an absolute or a relative path to a target document. An absolute path is a fully qualified URL or UNC path to a document. A relative path is a path related to the base path specified in the Hyperlink Base setting in the DatabaseNameProperties dialog box (available by clicking Database Properties on the File menu) or to the current database path. If Microsoft Access can't resolve the HyperlinkAddress property setting to a valid URL or UNC path, it will assume you've specified a path relative to the base path contained in the Hyperlink Base setting or the current database path.

HyperlinkAddress Property [Access 2003 VBA Language Reference]

learn.microsoft.com

What is strange is I cannot get your demo or mine to return the relative path. They both return absolute paths. However, if I remember at one time the default was relative path. If I look at your hyperlink base property it is blank.

Setting the hyperlink base property" refers to specifying a default directory or URL path within a document or application, so that when you create hyperlinks, they are automatically considered relative to that base path, meaning you only need to enter the filename or a relative path instead of the full absolute address each time

 

[arnelgp]

here is what ChatGPT has to say.
see the code in AfterUpdate of the combobox and Module1.

 

[MajP]

Still curious on this. Trying any of these demos and removing any conversion code I always get returned an Absolute/UNC path and not the relative path. I cannot figure out how in fact to even force it automatically store a relative path unless using the hyperlinkbase property. I thought I remember at a time getting relative paths returned by the hyperlink as the default and having to do the conversions. Now it seems the default is an absolute path. Or at least adding a hyperlink by drag and drop or add hyperlink gives me an UNC/Absolute path no longer a relative path.
Anyone know if something changed. It seems now you have to force it for a relative path.

 

[arnelgp]

added more test to it, but not tested on network drives (i don't have one).

 

[Gasman]

This is one of my network drives. Y:

 

[MajP]

That

This is one of my network drives. Y:

View attachment 118130

Yes that is what I get a UNC not a Relative path. \\Servername\path

The relative path often looks like a series of dots and dashes.
..\..\..\Something\..

Absolute, relative, UNC, and URL paths—ArcMap | Documentation

The kinds of paths you use in your day-to-day work are described.

desktop.arcgis.com

 

[sonic8]

It seems now you have to force it for a relative path.

I believe (I can't verify this ATM), the path will be built relative to the HyperlinkBase directory defined for the database. If there is none explicitly defined, Access will assume the database's directory as HyperlinkBase.
Result: If the document dragged into the hyperlink field is on the same drive as the database, it will be stored as a relative path. If the document is on a different drive, it will be stored as an absolute path.

That's why I recommended file:/// as HyperlinkBase because that should result in absolute paths for every situation.

 

[MajP]

Result: If the document dragged into the hyperlink field is on the same drive as the database, it will be stored as a relative path. If the document is on a different drive, it will be stored as an absolute path.

Got it. I was mistakenly testing it wrong going to external drives and network drives instead of drives that would be "relative". Makes sense.

I did add file:/// and it seems to do what you suggest.

 

[MajP]

Also going back to the first demo I posted. As far as I can tell it only saves the absolute path. Unless I am testing it wrong again. I tried the same test and could not create a relative path. Now if you want a relative path instead that may be an issue and require some code. That demo also has the ability to drop multiple files at once and does not rely on hyperlink fields which have their issues.
I tend not to use them because of some of the issues outlined here. You may agree or disagree.

 

[Juice]

I cant seem to find any modifications thats tied to absolute / relative path.

The one differenc i saw was the "Microsoft DAO 3.6 Object Library" which was not in my references in the test.mdb but was in in arnelgp's version of it.

When i put the DAO 3.6 Object Library in my test.mdb that generatade this:

With the code provided does just what the original did

I got hold of an older collegue, how was there at the begining of the making of the original sysem and told me it was developed on win xp and office 98. After that they upgraded to office 2003 (they opend it in 2003 and pressed save as). Basicly somhow i got lucky.

The best guess i have is that the a combination of some older and newer references made it work.

I will implement the code and the reference tomorrow and post the finished product.

Thanks again everyone.

 

[MajP]

Give this a try for a no code solution. Works for me.

 

[Juice]

MajP you are a genius....i have been rakcing my brain for the past week as to wtf did i did back then...

Here is the database property from the original:

Tried it in the new one and it works