How can I export from Notes to MS Access and have links function within Access taking you back to a Notes data page?

55 pts.
Tags:
Access 2007 form creation
Data migration
Hyperlinks
Lotus Notes 8
How can I export from Notes to MS Access and have links function within Access taking you back to a Notes data page? Copying as Table from Notes creates the links and it works when pasted to Excel or Word...but not Access. Importing to Access from the working Excel file then breaks the hyperlink in Access. It can be fixed manually to work from within Access but we have thousands of data files to connect to. 
Any help is appreciated.
Thanks,
Chuck


Software/Hardware used:
Notes 8 and Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

This can be done a few ways, but the easiest is likely the Notes URL. Very similar to a web URL except it starts with notes:// instead of http://. The server reference sometimes needs to be different as well, depending on your corporate DNS. For example, sometimes notes://server.domain.com/database/0/documentunid?opendocument will work, but other times it will be just notes://server/database/0/documentunid?opendocument.

Another way is to capture the “Doc Link” info into an NDL file and just add the NDL file into the ACCESS database. If a user launches the NDL file it will work like a doc/view/db link.
NDL files are just text files formatted a specific way and saved with an extension of .NDL.

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rakmup
    Thank you...I have figured out the link format/structure as you note above: notes:///etc...but I cannot get it to export a link that will stay when importing to Access. I do not have the manpower to manually update all the links. (155,000 records) Even copying to a table (with link) has limitations on how many you can do at once. The best solution I see thus far is to dump all the data into Access then build a link in a query combining the documentid and server ID's from the dataset. I have gotten that far now (have a cell with the link) but the link doesn't work for some reason. The cell looks right but the link properties looks like Chinese characters so does not work. I have to figure that out tomorrow. I do not know about this NDL file. I will also look that up tomorrow. Thanks...
    55 pointsBadges:
    report
  • SlikTool
    Though I would check back and see if you had and success with this. I am willing to assist further if I can.
    1,840 pointsBadges:
    report
  • Rakmup
    The main problem for me seems to be I need to link a lot of documents. I can do a few items manually...easily. I guess Notes cannot "export" anything with a link. And the MS products drop the copied link when importing in to Access. So...thank you for checking back but I do not know what to ask for help with at this point. I think I will manage to get the links in Access but it will be with a round-about method....mostly in Access. I was hoping Notes provided something I was missing. Regards
    55 pointsBadges:
    report
  • SlikTool
    I understand frustration, but don't give up. If a Access is limiting the type of URLs you can express, then use the NDL as an attachment. As I said, an NDL file is a text file with a .NDL extension on it. Just use your code to create a text file with the following structure (for a document link specifically). <NDL> <REPLICA 86257754:005B05BF> <VIEW OF970F2933:FDFF8736-ON85256C39:00751ADF> <NOTE OF1E4A3F02:99467EC4-ON862574D5:00338B75> <HINT>CN=SERVER/O=ORGANIZATION</HINT> <REM>Database 'Database Name Here', View 'Full View name here - not Alias'</REM> </NDL> In the <REPLICA> tag you have the replica ID of the database In the <VIEW> tag the full UNID of the view In the <NOTE> tag the full UNID of the document you are linking to In the <HINT> tag it is the Canonical name of the server In the <REM> tag (this is optional) but should be about the server and view Save the file with an NDL and attach it to Access.
    1,840 pointsBadges:
    report
  • Rakmup
    Hello and thank you again...I am frustrated but not ready to give up....yet :) Now it is my turn to really show my ignorance though. I am not sure I see what you mean here. Notes will create an NDL when copying the link and pasting to a text editor one line at a time. Saving the file with .ndl ext makes that file a link. Selecting several doc's does not create multiple links, it just sends you to the view where you can see all of the records. It seems to me I would need a separate NDL file for all 155,000 records...correct? That seems inefficient at best. Am I missing something? Can one NDL take care of all links? I am looking for a way to automate the linking process. Going forward we will probably add another 1,000 lines of data a month that also need to be linked. I appreciate your help but do not expect anyone to spend a lot of time helping my situation. Again, I was hoping there was a quick and easy fix to this I was just missing. Regards...
    55 pointsBadges:
    report
  • SlikTool
    Your assumption of my second suggestion is correct, it would require a file in each record. I ran a quick test, albeit manually, but if you create a "hyperlink" field in a table, insert text and a hyperlink on that text that uses a Notes:// URL, it works. I would need more time to try this programmatically - but I cannot see why it would not work. But what if we take a slightly different attack. Can you easily add an HTTP:// Url to the Access database that works fine? If so, why not create a single web url (can be Domino or .NET, or whatever you want) that receives data about the Notes:// URL via the Query_String and launches a Notes database. A bit "kludgy" I realize - but a possible solution. Mike Kinder
    1,840 pointsBadges:
    report
  • Rakmup
    First of the month reporting and other issues have kept me from working on this as much as I would like. Yes...I have gotten the links to work out of Access but so far only manually fixing in the property box. Frustrating part is it works fine in Word, copies from Word to Excel and will work there but Access is a different beast. Copying directly to Excel will also drop the link. In Access my field reads like the link. Notes:///C22574FD002C1925/Link/0F35ADABFD4E71718625715B006D33BE In the link however it looks like: 퐰%1d%01 If I copy the link to the properties of the link it works just fine...although I get a security warning I will fix later. Looking for a way to make the link properties equal the link. I agree though...I cannot see why it won't work! :) It just doesn't...yet anyway. I will ask a local web guru about your last thought...not sure I have rights to anything web related. Thanks again... Chuck
    55 pointsBadges:
    report
  • Rakmup
    I found a cure on an old message board somewhere... The format structure for the link in Access needs to follow this pattern: displaytext#address#subaddress#screentip Link#Notes:///C22574FD002C1925/Link/0F35ADABFD4E71718625715B006D33BE# I couldn't get it to work with a screen tip at the end so left it off and it works. In the Access cell it simply says "Link" but takes me to the full address of the exact document I need. I understand the "Link" in the middle of the string can be anything but could be used if you have a specific view you want to look at the document in. I don't need it and have not tried leaving it out altogether. So...I export my list from Notes to Excel, concatenate the link into a cell as shown and import to Access. Thanks for all your help. I always learn something out of these communications. BR, Chuck
    55 pointsBadges:
    report
  • SlikTool
    Really cool, did not know that for Access. thanks for sharing the solution.
    1,840 pointsBadges:
    report
  • ChrisPadillaAZ
    You guys are awesome @SlikTool looks like you might know or @RakmUp, you may have figured this out as well as it looks like you might have tons of experience by now... :) so my doclink has these entries:
    <REPLICA 65257646:005F64D2>
    <VIEW OF626E6035:EADBB4CD-ON85256499:006B15A6>
    <NOTE OFDF1C3153:359F7B09-ON65257823:000D8EC8>
    <HINT>CN=d23/OU=23/OU=A/O=IBM</HINT>
    <REM>Database 'Exchange Repository', View 'All Documents', Document 'Daily Call'</REM>
    
    I'm trying to create a URL that I can use anywhere: I've tried a lot of things but seem to be having issues with the fact that it's a document and not a DB. Things I've tried: Notes:///C22574FD002C1925/Link/OFDF1C3153359F7B09-ON65257823000D8EC8# Notes:///C22574FD002C1925/Link/OFDF1C3153359F7B09ON65257823000D8EC8# Notes:///C22574FD002C1925/Link/OFDF1C3153359F7B0965257823000D8EC8# Anywho, any assistance would be greatly appreciated.
    10 pointsBadges:
    report
  • Brooklynegg
    @ChrisPadillaAZ: Examine the NDL information for a document and compare it to the value on the same document in the Notes client when you right-click the document and show its Document Properties. The "+" tab has an Identifier field. That identifier has the Notes:// protocol link for the document (I don't think that value is available as a LotusScript or Java document Property, however.). I any case, this shows you how to construct a direct link to the document that can be used in other applications, as discussed in this thread. In all your examples, you have multiple errors, like including the word 'Link', including the characters 'OF' and 'ON'. If you examine the.NDL and Identifier information you will see where your mistake is. Finally, you can always replace the view identifier with 0. Notes knows how to find the document if you just give it the server, replica ID, 0 for the view, and the document ID. Notes://Servername/ReplicaID/0/DocID
    3,845 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following