Solved

Accessing a Lotus Notes 5 Database from MS-Access/Visual Basic

Posted on 2004-09-09
7
336 Views
Last Modified: 2013-12-18
Hi, I need to access a Lotus Notes 5 database from MS-Access with Visual Basic. I'm looking for some kind of tutorial, where the complete process (opening the Notes DB, querying, accessing fields, etc., is described. Because of corporate restrictions, I have only access to the Notes 5 client on my machine, so all solutions needing access to the Notes Server are not possible.

I already found the Notes-Object availabe in the MS-Access Object Catalogue and started with the following code

Set session = CreateObject("Notes.NotesSession")
   Set db = session.GETDATABASE("sstrda42.wk.dcx.com/Server/Prod/DCX", "aend_mgmt_sks.nsf")
   Call db.Open("sstrda42.wk.dcx.com/Server/Prod/DCX", "app\019\tpc\pit\aend_mgmt_sks.nsf")

This doesn't produce any errors but trying to open a Notes View and accessing single fields always fails.

So any hint how to get the job done will be very much appreciated - thank you very much in advance.
0
Comment
Question by:cgikb2
7 Comments
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 100 total points
ID: 12013945
Call session.Initialize("password")

The Open has no parameters.

You called GetDatabase with a server parameter, but you say you only have the client. Use "" for the first parameter.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12014054
Btw, it is good practice to verify the return values of calls to Notes, e.g. if db has a correct value.
0
 

Author Comment

by:cgikb2
ID: 12014425
Hi sjef_bosman,

thanks for your fast answer. Alas, the session.initialize call produces the error message "Object doesn't support this property or method". On the other hand, if I comment this out and call session.username it works and produces the correct username.

I found a tutorial at:
http://www-10.lotus.com/ldd/today.nsf/0db661345413ad1d852567ba006bb090/27e234ca2b0b30798525684e00574a54/$FILE/COMacc.pdf

and it seems that there is a lot of confusion about OLE- and COM-access. I'm going to have a closer look at this and maybe you have some more ideas and/or other links to other tutorials and/or an explanation about this OLE- /COM-difference.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12014573
All Notes documentation can be found via www.lotus.com/ldd/doc
0
 
LVL 19

Assisted Solution

by:RanjeetRain
RanjeetRain earned 200 total points
ID: 12016292
YOu may find this thread useful. Besides, Qwaletee has posted some useful comments on the subject which are available in this topic area. If you have access to search you may ytry that. Or else, we may provide you. Anything in particular you would like to know?

http://oldlook.experts-exchange.com:8080/Q_21109426.html
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 200 total points
ID: 12020004
Don't use Notes.NotesSession.  Instead use Lotus.NotesSession, which is more efficient, and does not require the Notes clientto open to process the request.

Better yet, register the TLB, and use:
Dim ns as New NotesSession
ns.Initialize
Dim db as NotesDatabase

etc.
0
 

Author Comment

by:cgikb2
ID: 12064133
Thanks to all of you helped me with that non-trivial problem. Your answer led me to the following solution, reproduced here for all who try something similar:

   Dim session As New NotesSession      ' You were right about this, qwaletee
   Dim notesdb As NotesDatabase
   Dim dir As NotesDbDirectory
   Dim dc As NotesDocumentCollection
   Dim doc As NotesDocument
   Dim Antrag As NotesDocument
   Dim view As NotesView
   Dim vwLookup As NotesView
   Dim viewNav As NotesViewNavigator
   Dim RetVal As Object
   Dim int_RetVal As Integer
   Dim str_Username As String
   Dim Bool_Fortsetzen As Boolean
   Dim str_Subject As String
   Dim db As Database
   Dim rst As Recordset
   Dim str_Hilf As String
   Dim int_Hilf As Integer
   Dim str_Passwort As String
   Dim Aktualisiert As Date

   Set session = CreateObject("Lotus.NotesSession")      ' Setup the connection to the Notes
   Call session.Initialize(str_Passwort)                            ' Database
   str_Username = session.UserName
   Set dir = session.GetDbDirectory("sstrda42.wk.dcx.com/Server/Prod/DCX")
   Set notesdb = dir.OpenDatabase("apps\019\tpc\pit\aend_mgmt_sks.nsf")
   
   Set view = notesdb.GetView("OpenIssuesById")         ' Open the Notes View in question
   Set doc = view.GetFirstDocument
   Set dc = notesdb.AllDocuments

    Set rst = db.OpenRecordset("Offene_Antraege", dbOpenDynaset)
   
   While Not (doc Is Nothing)
      rst.AddNew
      If doc.ColumnValues(1) <> "" Then                        ' Get all relevant field data from the
         rst.Fields("AntragsID") = doc.ColumnValues(1)    ' Notes View and transfer it into a
      Else                                                                       ' MS-Access table
         rst.Fields("AntragsID") = " "
      End If
      If doc.ColumnValues(2) <> "" Then
         rst.Fields("Art") = doc.ColumnValues(2)
      Else
         rst.Fields("Art") = " "
      End If
      If doc.ColumnValues(3) <> "" Then
         rst.Fields("erstellt") = doc.ColumnValues(3)
      Else
         rst.Fields("erstellt") = " "
      End If
      If doc.ColumnValues <> "" Then
         rst.Fields("Betreff") = doc.ColumnValues(4)
      Else
         rst.Fields("Betreff") = " "
      End If
      int_Hilf = doc.ColumnValues(5)
      If int_Hilf = 160 Then
         rst.Fields("NS") = True
      Else
         rst.Fields("NS") = False
      End If
      If doc.ColumnValues(6) <> "" Then
         rst.Fields("Status") = doc.ColumnValues(6)
      Else
         rst.Fields("Status") = " "
      End If
      int_Hilf = doc.ColumnValues(7)
      Select Case int_Hilf
         Case 79
            rst.Fields("intern") = "wartend"
         Case 82
            rst.Fields("intern") = "abgeschlossen"
         Case Else
            rst.Fields("intern") = " "
      End Select
     
      int_Hilf = doc.ColumnValues(8)
      Select Case int_Hilf
         Case 79
            rst.Fields("extern") = "wartend"
         Case 82
            rst.Fields("extern") = "abgeschlossen"
         Case Else
            rst.Fields("extern") = " "
      End Select
      If doc.ColumnValues(9) <> "" Then
         rst.Fields("Bearbeiter") = doc.ColumnValues(9)
      Else
         rst.Fields("Bearbeiter") = " "
      End If
LBL_A:
      If doc.ColumnValues(10) <> "" Then
         rst.Fields("Projekt") = doc.ColumnValues(10)
      Else
         rst.Fields("Projekt") = " "
      End If
      If doc.ColumnValues(11) <> "" Then
         rst.Fields("Teilprojekt") = doc.ColumnValues(11)
      Else
         rst.Fields("Teilprojekt") = " "
      End If
      str_Hilf = doc.ColumnValues(13)
      If str_Hilf <> "" Then
         rst.Fields("gepl_Release") = str_Hilf
      End If
      Set Antrag = dc.GetDocument(doc)                       ' Now a value not available from the View
      rst.Fields("Last_Modified") = Antrag.LastModified  ' is needed; therefore get the real document
      rst.Update                                                            ' and get it there
      Set doc = view.GetNextDocument(doc)
   Wend
   
Exit_Befehl0_Click:
   Set doc = Nothing                                                    ' Clean up
   Set vwLookup = Nothing
   Set viewNav = Nothing
   Set view = Nothing
   Set db = Nothing
   Set session = Nothing
     
   Exit Sub

Err_Befehl0_Click:

    Select Case Err
       Case 3421                                                     ' If the Notes View has an array instead of a field
          str_Hilf = doc.ColumnValues(9)(0)
          If doc.ColumnValues(9)(1) <> "" Then
             str_Hilf = str_Hilf + "/" + doc.ColumnValues(9)(1)
          End If
          rst.Fields("Bearbeiter") = str_Hilf
          Resume LBL_A
       Case 13                                                       ' If the relevant field is empty; only for columns
          int_Hilf = 0                                               ' 7/8 in this example
          Resume Next
         
       Case Else
         MsgBox Err.Description
         Resume Exit_Befehl0_Click
    End Select


As you can see I'm accessing not only the columns from the View but a field from the original document, too, 'cause it's not available in the view.

Because there is not only one "true answer" I'm going to split points - hope you agree.

Thanks again for the great help and keep up the good work.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now