Get Data from a Notes DB into Access

[LeanneGodney]

Hi there,

I'm an intermediate VBA developer, but am very new with automating Access with Lotus Notes. At the moment my department exports data out of a lotus notes database and then imports that data in access.

I would like to grab the data through vba and either put a linked table in Access, or paste the data into an access table.

So far, I've been able to OPEN the database, but I don't know how to access a certain area, or to set the datastore as a recordset, etc.

I want to grab everything sitting inside the ALL BY STATUS section, but only for the category of EUROPE.

Any ideas anyone??

Thanks in advance,
Leanne

 

[M8KWR]

if you have a linked table in access, then you would have create a query to only select the fields you wish, but if this data is to be entered into another table, you then would have to create an append query.

If you give me the field you only want, and the criteria in the filer, i could give you the sql. Also i would need to table that the info was going in to and the fields..

Hope this helps

 

[LeanneGodney]

Hi,

I don't have a linked table. I'm trying to get one, or trying to get direct access to the lotus notes database. So far, I've been able to get to the notes database, but can't figure out how to grab all the data and export it, or import it directly into Access...

Leanne

 

[PJStephenson]

I found this document quite useful from the IBM website:

http://www.redbooks.ibm.com/Redbooks.nsf/RedbookAbstracts/sg245670.html?Open

which gives some useful tips for getting data from Domino Databases.
HTH
Peter

 

[PJStephenson]

You can also link to a table in a notes database if you have the notesSQL ODBC driver on your PC.
If you do you can create a system DSN to the Database then link tables to access through that.
It does seem a bit slow though.
You will also need to create new DSNs for each Database.
Once you have got the DSN it should be fairly straight forward getting recordsets.
It's a different approach to the redbook option in my last post but may be easier if you are used to recordsets.
I do have a sample of code using the redbook method if you are interested in going that route.
HTH
Peter

 

[LeanneGodney]

Hi Peter,

I've managed to link the tables using notessql. But an example of your code would be great!

I don't have to use recordsets - was just thinking of using that if I were to speak directly to the notes database through code. But the linked table will work just as well, so am happy with that option. It is slow, but I'm going to write some code that will make a table of the data, and then use that local copy.

Thanks!
Leanne

 

[PJStephenson]

Ok, this is an adapted version of the Email Address Lookup fron the IBM .pdf file. This gets groups from the address book and tries to put them in a more relational db format.

Public Sub GetAddressbookGroupsContents()
'Set up the objects required for Automation into lotus notes
    Dim DomSession As NotesSession        'The notes session
    Dim DomDB As NotesDatabase
    Dim DomView As NotesView
    Dim DomDoc As NotesDocument
    Dim DomItem As NotesItem
    Dim rstGroup As Object  'Recordset '
    Dim rstPeople As Object  'Recordset '
    Dim rstLink As Object  'Recordset '
    Dim var0, var1, var2, varGroupID
    Dim intLoopCounter, varBookmark
    Set rstGroup = CurrentDb.OpenRecordset("tblNotesGroups")
    Set rstPeople = CurrentDb.OpenRecordset("tblNotesPeople")
    Set rstLink = CurrentDb.OpenRecordset("tblNotesPeopleGroupLink")
    
    
            'Start a session to notes
    Set DomSession = CreateObject("Lotus.NotesSession")
    'On Error Resume Next
    DomSession.Initialize "password"
    UserName = DomSession.UserName
    Set DomDB = DomSession.GetDatabase("YourServer", "YourTable.nsf", False)
    Set DomView = DomDB.GetView("Groups") 'These are views within the database
    Set DomDoc = DomView.GetFirstDocument
    
    While Not (DomDoc Is Nothing)
        var0 = DomDoc.UniversalID
        var1 = DomDoc.GetItemValue("Members") ' alot of fields are arrays
        var2 = DomDoc.GetItemValue("ListName")(0)
        Set DomDoc = DomView.GetNextDocument(DomDoc)
        
        'Fill Tables
        With rstGroup
            .AddNew
            ![NotesUniversalID] = var0
            ![NotesGroupName] = var2
            varGroupID = ![GroupID]
            .Update
            '.MoveLast
        End With
        With rstPeople
            For intLoopCounter = 0 To UBound(var1)
                .AddNew
                ![NotesPersonName] = var1(intLoopCounter)
                '.MoveLast
                rstLink.AddNew
                rstLink![NotesPersonLink] = ![PersonID]
                rstLink![NotesGroupLink] = varGroupID
                rstLink.Update
                .Update
            Next
        End With
    Wend

jumpsendmail:
MsgBox "Done"
'ought to close stuff here
    
    Exit Sub

SendNotesError:
MsgBox "Unknown error: " & Err.Number & " " & Err.Description & vbCrLf & " "
Exit Sub
End Sub

Hope this gives some ideas for getting data from notes dbs
Peter