Solved

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

Posted on 2004-09-09
7
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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