Hyperlink field that does not link to the file

[Umar]

I have a hyperlink field called FileName in Access, to populate this field I
imported from Excel the list of file names such as
“\\MAIN-SERVER\DOWN-SERVER\Documents 001.doc," "... Documents 002.doc“ and
many more after the import succeeded then I clicked the hyperlink field to
open the file, but it won't open the file. As you know Excel 2007 does not
support hyperlink type, so the cell type in Excel was Text.

So I copied the file name from the Access field to a Word document,
Ctrl-click does not open it.

To make the link work in Word I put the cursor to the end of the File Name
that I just pasted then I hit the Backspace button followed by the Enter key.

When I do the Ctrl-Click at the File Name that is now in the Word document
the new document is opened as hyperlink supposed to.

Now I Copied this File Name from the Word document and Paste it
in the FileName field in Access, following that I can open My_Documents
001.doc and other file names from Access, which means the linkage of the
hyperlink in Access is established.

But since there are thousands of these File Names, I do not want to repeat
the process for each FileNames.

There are two ways to make this job easier one is Access should recognize
the FileNames imported as hyperlink or if that is not possible, how do I make
the process faster and avoid those manual process repeated so many thousand
times.

Any help is appreciated, thank you.

 

[Lars Brownies]

Umar,

A hyperlink value consists of 2 parts, the display-part and the actual
hyperlink-part which is surrounded by #'s

So a hyperlink value could be:
C:\MyFile.doc#C:\MyFile.doc#, or
MyFile#C:\MyFile.doc#
You will notice this when you tab to the hyperlink field and press F2.

Is seems you need to convert your Excel values like
\\MAIN-SERVER\DOWN-SERVER\Documents 001.doc to
\\MAIN-SERVER\DOWN-SERVER\Documents
001.doc#\\MAIN-SERVER\DOWN-SERVER\Documents 001.doc#, or
Documents 001.doc#\\MAIN-SERVER\DOWN-SERVER\Documents 001.doc#

That should be possible with queries and/or some VBA. Also, you can also use
the find and replace option in hyperlink fields.

Personally I never store pathnames in hyperlink fields. I set the
hyperlinkbase path so that I can easily migrate the application along with
its external files, without having to adjust the hyperlink values.

Hopes this gives you some ideas.
Lars

 

[Umar]

Lars,
Thank you, that is interesting to know, you are correct the # that
sorrounded the hyperlinks part is shown as soon as I press F2.

Let me explain further of what I am doing.

The Database is organized around Requirement Number which is the highest
hierarchy in the Database Primary key fields (the Primary Key consisted of
multiple fields), accordingly each documents for that Requirements are stored
in the directory that started with \\MAIN-SERVER\DOWN-SERVER\R0001\ up to the
highest Requirement Number \\MAIN-SERVER\DOWN-SERVER\Rxxxx\ this then will be
followed by another sub directories which is the Document Type such as
\Legal, \Cost, \Funding etc. That is how I match the Database Record with
the Directory hierarchy.

The Requirement Number and the Document Type are also fields in the Database
record.
Each file will be on a different Requirement Number directory, I hope now
you can see that I could not set up the entire column to a hyperlink based
path. Beside that I do not see Access give me an option to set up the
hyperlink based path for the entire column.

However your idea of hyperlink based path is very interesting, It will work
if I build the string that consisted of Server Name which I can hard code,
Requirement Number which I shall format into Rxxxx, Document Type, and
combine that with the Document File Name which now should be stored in the
Document Name field as text. Furthermore after the string is constructed how
do I translate it into opening the document. I am not sure if VBA can do
that, the site that I am working does not have a compiler , so I have been
using VBA for everything that need to be done.

The User is unsophisticated so I cannot expect them to open the document by
doing more than click or double-click the mouse button.

Your input is very helpful, I also would welcome if there is more ideas that
I can use.

Best Regards,

Umar.

 

[Lars Brownies]

Umar,

You can set the hyperlinkbase manually in the menu File->Database
properties, or by code:

Public Function fSetHyperlinkBase()
Dim db As DAO.Database
Dim doc As DAO.Document
Dim prp As DAO.Property

On Error GoTo Err_Handler

Set db = CurrentDb
Set doc = db.Containers("Databases").Documents("SummaryInfo")
Set prp = doc.Properties("Hyperlink base")
prp = strPathApp & "Hyperlinks\"
'strPathApp is a global var pointing to the app's path
'which is being picked up when the user start the app

Exit_SetHyperlinkBase:
Set prp = Nothing
Set doc = Nothing
Set db = Nothing
Exit Function

Err_Handler:
If Err.Number = 3270 Then ' property not found
Set prp = doc.CreateProperty("Hyperlink base", dbText, strPathApp &
"Hyperlinks\")
doc.Properties.Append prp
Resume Next
Else
Dim strErr As String
strErr = "Error: " & Err.Number & ": " & Err.Description
Call ErrLog("Specific", "fSetHyperlinkbase", strErr)
Resume Exit_SetHyperlinkBase
End If
End Function

If you set the hyperlinkbase to for instance C:\MyFolder\ you only need to
store the document name in your hyperlinkfield. If the document is in an
underlying folder you'd store that folder too:
Legal\MyDoc1.doc#Legal\MyDoc1.doc#

When you click it will open C:\MyFolder\Legal\MyDoc1.doc

This way, if you ever need to migrate your data, you can copy the complete
structure and you'd only have to change the hyperlinkbase.

However, if I had to rebuild my app, I probably wouldn't use hyperlinks
anymore. I'd just store the folder and pathnames in a textfield. I'd still
use the concept of the hyperlinkbase though.

Do I understand correctly that all info to create the right path and
filename is in fact in your record?
If that's the case you wouldn't even have to store the hyperlink or filename
but derive it from your record when you need it. I'm not sure how you
normally add files to the server, but you might have to write an import
routine that imports the particular file of the user to the main server
folder and give it the right name so that it corresponds with the particular
record's values.

Lars