Create a Lotus Notes DB Connection and access through Excel

version of Notes?

Please have a look at:
http://www.ibm.com/developerworks/lotus/products/notesdomino/notessql/

Sounds like this is what you want

If you can read Chinese (but even if you can't), this page contains some code that could be what you need:
http://topic.csdn.net/t/20060105/10/4499220.html

Why didn't you answer about Notes version? It could be crucial to the type of solution you'll get.

You want to query Lotus Domino database using SQL? Is that a must? I'd rather suggest you use web services to pull the data.

If it is crucial, then you'll have to install NotesSQL driver, which enables you to get Domino data using SQL queries, but I would strongly suggest against it, it's highly under-optimized, can be very very slow (unless you build and maintain specific views) and the support is virtually non-existent.

I agree with mbonaci.  I prefer using the COM library for querying Notes data.  You could still let your users store queries, buy the syntax would use Lotus Formula Language rather than Notes SQL.

Here'a a simple example that searches the current user's email.

Public Sub SearchMailFileBySubject(ByVal keyword As String)
 
  ' open current user's email file
  Dim sess As Object, email As Object, dc As Object, memo As Object, item As Object
  Dim mailServer As String, mailFile As String
  Set sess = CreateObject("Lotus.NotesSession")
  Call sess.Initialize("")
  mailServer = sess.GetEnvironmentString("MailServer", True)
  mailFile = sess.GetEnvironmentString("MailFile", True)
  Set email = sess.GetDatabase(mailServer, mailFile, False)
  
  ' search for email with the specified keyword in the subject
  Set dc = email.Search("@Matches(Subject; '*" + keyword + "*')", Nothing, 0)
  MsgBox "found " & dc.Count & " memos"
  Set memo = dc.GetFirstDocument
  Do Until memo Is Nothing
    If (MsgBox(memo.GetItemValue("Subject")(0), vbOKCancel) = vbCancel) Then Exit Do
    Set memo = dc.GetNextDocument(memo)
  Loop
  
End Sub

Select allOpen in new window

Hi Sorry, experts, I had to be away from the PC for a few days... I'm using Lotus Notes 8.  I have no issues using the COM directory, I don't want to have to make the end user install the notes SQL driver.  The Chinese link from sjef_bosman looked to be along the lines of what I'm trying to do... Establish a connection to a specified database, query for data, a return the results to Excel.

I'm not searching an email file, but I think I can play around with the connection that bill has.  Do you have any more examples (maybe in English) that execute something looking like a SQL statement?

-Thanks,

There's no SQL for Notes unless you plan to install NotesSQL, an add-on that comes with a price tag. From an admin's point of view, it's easier to use the COM interface and either a use view or query the database using the NotesDatabase.Search method (or FTSearch, which requires more admin, is faster, uses a full-text index and therefore increases the db's size). If you always look for the same type of documents and the same type of data, I'd suggest to use a sorted view.

The thing that's closest to a SELECT is the Search, everything else is to be hard-coded: get the first document (say "record"), get the items in the document (say "column) and move to the next. Etc.

RE: "I don't want to have to make the end user install the notes SQL driver"

For direct access, you won't be able to avoid this.  You'll either have to install the Notes client or the NotesSQL driver.

The only way that I see to avoid installing a Notes connector is if you provide a web service on a Domino server that provides all of the Domino functionality that your Office app needs.  Then just call the web service from your app.

Thanks, it looks like I have a bit of work to do to set up the best long term solution, but this seems pretty straight forward.

I appreciate the guidance.