Need Access to grab data from Notes database 1

[willir]

Hi Folks

I don't ask for help often, but I changed jobs where Notes admin is a bit thin.

I am developing an Access database (2000 and 2002 platform) for call tracking.

This task is currently done within a Notes database but the database is very limited in terms of capturing information, and flexibility of categorizing reports. Since the Notes database application is proprietary, I can not / should not touch it due to licensing issues.

So what I would like to do...
- Avoid duplication of work
- Best case scenario - read from and write to database via Access. Data in Access DB would meet reporting needs. Data in Notes would reflect current activity.
- If not write to, then read from Notes DB. Imported data could be easily categorized for reporting needs.

I am reading up on Notes and Access...

http://www.tek-tips.com/viewthread.cfm?qid=641772

...and others.

...but I suspect I am going to need a better reference tool. Can anyone recommend a book or two on this aspect of Notes. Our local bookstores have zip on this so I suspect I will have to order via some mail / Internet order system where I can not browse before I buy.

Thanks in advance.
Richard

 

[willir]

Oh yea, I guess I am going to need a tool or two to accomplish this. Recommendations...

 

[lespaul]

Well, hello Richard!! Welcome to the world of Notes Databases! First off, FORGET EVERYTHING YOU KNOW ABOUT RELATIONAL DATABASES BEFORE STARTING ANYTHING WITH NOTES!!!!

Ok, now that that's out of the way.

When you are working in Notes, you are actually working with two objects. The backend document (NotesDocument Class) and the front end form that displays the document (NotesUIDocument Class). Most of the applications that we have update the AS400 from the Notes application for each document that is created. You are looking to do the opposite and therefore most of your extraction will come directly from the NotesDocument and NotesDocumentCollection classes, there's really no need for you to delve into the NotesUI classes at this point.

Since you are not interested in modifing the Notes application, is there some field in the document that indicates if it has been "processed" in some way? If so,then you can grab all the new information and insert into Access. If you don't have any way to tell the "status" of a document and if it has been added to the Access database already, I would suggest emptying the Access database each time and repopulating with all the documents in the Notes Database.

If you open a Notes view and right click on a document, select Document Properties, select the second tabbed page. This will list ALL the fields in the document (could be helpful to you!).

That thread you're looking into is a good one to get connected to Notes, once you're ready to start extracting information, I'll be more than happy to show you the ins and outs of Notes!

les

 

[willir]

Thanks Lelsie - I was hoping it would be you "holding my hand".

As an FYI, we are running Domino R5, release 5.0.12.

I have already looked at the properties tab and know the name of the two NSF files I have to hit. So my next task is to see how I can get in connecting to Notes from Access.

And yep, I know, all to well, that the "adminstrative lists" are not the real relational thing. The things that Notes do well is security and auditing.

Any idea on a good reference book for this task? You have a fair idea on my strengths and weaknesses, and what I have to do.

If I have to tweak the Notes design, I might, but only if I am sure of no impact. Calls are referenced with the infamous 10 alphanumeric ID, and calls are closed, so I can at the very least use these two fields to see what has to be brought across.

Oh yea, currently reading ORYX AND CRAKE by Margret Atwood -- real George Orwellian type of stuff, but instead of Big Brother, we have Corporate Greed and Conspiracy Theory.

Finished Universe in a Nutshell by Stephen Hawking. Not SF and Fantasy, but sure makes the mind spin. Interesting the SH now, although reluctantly, addresses string theory.

Good to hear from you. Any comments on the "new look" for Tek-Tips? The style is nice but I sure miss some of the old features that nolonger work.

Richard

 

[lespaul]

oooh, we get to hold hands!

So your basic objective it to connect to the Notes Database, if there are new calls entered, add this information to the Access Database.

Do you have the Domino Designer? I think your best approach is going to be to have a view that contains all the documents that you need to process (hopefully there already is one, if not this is something that you can add that WILL NOT affect the rest of the program).

Then after Access connects to notes, open that view and loop through the documents and extract the document field information to create your Access INSERT INTO statement and execute it.

Here's some stuff that may help:

[psuedocode]
Dim notesdb As Object
Dim notesdoc As Object
Dim notesView As Object

Dim i as Integer

Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.GetDatabase("servername", "database.nsf")
Set notesView = notesdb.GetView( "Name of View to Process" )

For i = 1 to NotesView.EntryCount do
  Set notesdoc = view.GetNthDocument(i)
  [COLOR=red]'Now you can Access the fields on the document to extract the information for your query; I have included some information from the Notes help showing different methods of getting information from a document:[/color]

Next

[/psuedocode]

1. This example uses the Items property of NotesDocument to get each item in each document in a database view, and displays its name and text representation.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView("By Category")
Set doc = view.GetFirstDocument
While Not(doc Is Nothing)
Forall item In doc.Items
Messagebox item.Name & " = " & item.Text
End Forall
Set doc = view.GetNextDocument(doc)
Wend
End Sub
2. This example uses the GetFirstItem method of NotesDocument to get the Subject item in each document in a database view, and displays its name and text value.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim item As NotesItem
Set db = session.CurrentDatabase
Set view = db.GetView("By Category")
Set doc = view.GetFirstDocument
If doc.HasItem("Subject") Then
While Not(doc Is Nothing)
Set item = doc.GetFirstItem("Subject")
Messagebox item.Name & " = " & item.Text
Set doc = view.GetNextDocument(doc)
Wend
End If
End Sub
3. This example uses the item name as a document property to get the value of the Categories item in each document in a database.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView("By Category")
Set doc = view.GetFirstDocument
If doc.HasItem("Categories") Then
While Not(doc Is Nothing)
Forall category In doc.Categories
Messagebox category
End Forall
Set doc = view.GetNextDocument(doc)
Wend
End If
End Sub
4. This example uses the GetItemValue method of NotesDocument to get the value of the Subject item in each document in a database.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView("By Category")
Set doc = view.GetFirstDocument
If doc.HasItem("Subject") Then
While Not(doc Is Nothing)
Forall subject In _
doc.GetItemValue("Subject")
Messagebox subject
End Forall
Set doc = view.GetNextDocument(doc)
Wend
End If
End Sub
5. This example tests for the presence of a specified item in each document in a view.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView("By Category")
itemName = Inputbox("Name of item?")
Set doc = view.GetFirstDocument
While Not(doc Is Nothing)
If doc.HasItem(itemName) Then
Messagebox "Item present in document"
Else
Messagebox "Item not present in document"
End If
Set doc = view.GetNextDocument(doc)
Wend
End Sub

That should at least get you started!!

I haven't read anything new lately, been re-reading some things that I have and browsing the used book stores for others that I haven't read in a really long time (I've started collecting the Stephen Lawhead series: Taliesin, Merlin, Arthur, Pendragon, Grail) I read them YEARS ago, and remember how enjoyable they were, so I've got through Arthur - still need to find the other two! It's a very different look at the Arthurian legend, with the main characters promoting Christianity (instead of the "save the old ways" view)

I do like the new look, but like you I wish they would get some of the other features back to functional!

Let me know if you need any more info!!

les

 

[willir]

Thanks a bunch -- the last post was really insightful.

If you are browsing old book stores, consider Tad Williams.
I found his Memory, Sorrow & Thorn trilogy - Dragonbone Chair, Stone of Farewell and to Green Angle Tower - exceptional once you get past the Frank Herbert verboseess style.

Tailchaser is very cute.

Otherland is popular.

Richard

 

[lespaul]

I haven't read the Tad William MS&T in a long time, but have read them before. I did the Otherland series a few years ago.

Glad the information was helpful.

If you need anything more let me know!

les

 

[nice95gle]

lespaul,
I was always told that the Notes DB and the Access DB needs to sit on the same server for the above method to work. Is this true or was I misinform?

Thanks

 

[lespaul]

I have to say I don't know. I have only read about the theory and looked at the concepts on threads here. I'd be interested to know the answer if Richard succeeds!

Leslie

 

[pmonett]

nice95gle :

I'm not quite sure that is the case. I was recently working in an environment where a PC was connecting to a db on an AS400 and feeding Notes data via ODBC.
I think that, once the PC you are working on has a valid ODBC connection to the database you wish to access, it does not matter if that db is local or on a server.
In any case, it did not matter in my case - except that it was a Notes client doing the updating via ODBC, not an Access client. But still, I think that configuring an ODBC access to a Notes db and getting Access 2000 to work on it should be rather straightforward. Just don't forget to give a valid Person & Internet Password from the Domino NAB.

Pascal.

 

[silvami]

There is a great program or really an ODBC Driver on the IBM website that will allow you to connect to a Lotus Notes DB as if it were a relational DB. The file is called NotesSQL here is the link for the file:

http://www14.software.ibm.com/webapp/download/search.jsp?v=11&lang=en&cc=us&realm=tnb&q=NotesSQL

I have used it and it works great it will take the Lotus data and put it in a relational access format. It is also useful if you need to do any reporting off of Lotus using Crystal Reports. Let me know what you think.

 

[erictourx]

Does this task have to be written in Lotus Script? Or can it use an alternative language…like the C++ Api? I have done something very similar to what you’re trying to accomplish but it was done with the Notes C++ Api. If the language doesn’t matter let me know and maybe I can help.