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

Posted on 2004-09-09
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("", "aend_mgmt_sks.nsf")
   Call db.Open("", "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.
Question by:cgikb2
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.
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.

Author Comment

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:$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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 46

Expert Comment

by:Sjef Bosman
ID: 12014573
All Notes documentation can be found via
LVL 19

Assisted Solution

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?
LVL 31

Accepted Solution

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
Dim db as NotesDatabase


Author Comment

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("")
   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)
      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)
         rst.Fields("Art") = " "
      End If
      If doc.ColumnValues(3) <> "" Then
         rst.Fields("erstellt") = doc.ColumnValues(3)
         rst.Fields("erstellt") = " "
      End If
      If doc.ColumnValues <> "" Then
         rst.Fields("Betreff") = doc.ColumnValues(4)
         rst.Fields("Betreff") = " "
      End If
      int_Hilf = doc.ColumnValues(5)
      If int_Hilf = 160 Then
         rst.Fields("NS") = True
         rst.Fields("NS") = False
      End If
      If doc.ColumnValues(6) <> "" Then
         rst.Fields("Status") = doc.ColumnValues(6)
         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)
         rst.Fields("Bearbeiter") = " "
      End If
      If doc.ColumnValues(10) <> "" Then
         rst.Fields("Projekt") = doc.ColumnValues(10)
         rst.Fields("Projekt") = " "
      End If
      If doc.ColumnValues(11) <> "" Then
         rst.Fields("Teilprojekt") = doc.ColumnValues(11)
         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)
   Set doc = Nothing                                                    ' Clean up
   Set vwLookup = Nothing
   Set viewNav = Nothing
   Set view = Nothing
   Set db = Nothing
   Set session = Nothing
   Exit Sub


    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.

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Capture Serial Data in Lotus Notes Database 8 265
Email Message Background Color 4 133
Add code to Lotus Notes view column 2 123
Save conflict messages on existing documents 15 68
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 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