Lotus Notes data (.nsf) To MS Access 1

[BGrego]

Hello!

I am in dire need of assistance...I am trying to import data from a .nsf lotus notes db to a MS Access db. I understand how to create the ODBC connection, and I have imported tables using the views in the .nsf file, but the actual data that I need is in a form document within the .nsf file. I guess, my question is...how do I get the .nsf form data that I need into Access? Is there a way to do this using the import wizard, or should I use VBA? And if I should use VBA, can anyone assist?

I am a very proficient programmer, so you shouldn't have to be too descript. Any help given or a good link would be GREATLY appreciated.

Thank you
Brett

 

[lespaul]

So you need the data that is stored on the document? Do you need to get information from each document? If so, then you'll need to get all the documents in a collection, loop through each document and get the values from each field to import to Access.

Here's some code:

(this is the lotus script needed to get Notes document information to Excel, should be fairly similar)

Sub Click(Source As Navigator)
	
Dim workspace As New NotesUIWorkspace
Dim session As New NotesSession
Dim collection As NotesDocumentCollection
Dim refview As NotesView
Dim doc As NotesDocument
Dim searchFormula As String
Dim startdate As String, enddate As String
Dim db As NotesDatabase
Dim j As Integer
	
Set db = Session.CurrentDatabase
Set refview = db.getview("(ReferralsByDate)")
	
	
EnterDate:	
startdate = Inputbox("Please enter the start date (mm/dd/yyyy) for which you would like to run the report", "Report Selection")
If Isdate(startdate) = True Then
Dim sdate As New NotesDateTime (startdate)
Dim edate As New NotesDateTime (startdate)
Call edate.AdjustMonth(1)
Elseif startdate <> &quot;&quot; Then
messagetext = &quot;You entered &quot; + startdate + &quot;.  That is not a recognized date.  Please try again.&quot;
If Messagebox(messagetext, 1 + 16, &quot;Entry Error&quot;) = 1 Then
Goto EnterDate
Else
Exit Sub
End If
Else
Exit Sub
End If
	
Set excelapp = createobject(&quot;excel.application&quot;)
excelapp.visible = True	
excelapp.visible = False
excelapp.Workbooks.add(&quot;O:\Pretrialforms\DirectDVReport&quot;)
Set exceldta = excelapp.Workbooks(&quot;DirectDVReport1&quot;).Worksheets(&quot;Database&quot;)
Set excelsht = excelapp.Workbooks(&quot;DirectDVReport1&quot;).Worksheets(&quot;DirectDVReport&quot;)
excelapp.DisplayAlerts = False
j = 2
	
Set doc = refview.GetFirstDocument
Do While Not doc Is Nothing
Set item = doc.GetFirstItem(&quot;OpenDate&quot;)
If (item.datetimevalue.dateonly >= sdate.dateonly) And (item.datetimevalue.dateonly < edate.dateonly)Then
exceldta.cells(j,1).value = doc.GetItemValue(&quot;AssignedTo&quot;)
exceldta.cells(j,2).value = doc.GetItemValue(&quot;OpenDate&quot;)
exceldta.cells(j,3).value = doc.GetItemValue(&quot;Completed&quot;)
exceldta.cells(j,4).value  = doc.GetItemValue(&quot;Program&quot;)
exceldta.cells(j,5).value = doc.GetItemValue(&quot;Rejected&quot;)
exceldta.cells(j,6).value = doc.GetItemValue(&quot;Sex&quot;)
j = j+1
Set doc = refview.GetNextDocument(doc)
Else
Set doc = refview.GetNextDocument(doc)
End If		
Loop
excelsht.range(&quot;A2&quot;).value = startdate
Call excelsht.Auto_Open
excelsht.printout	
excelapp.DisplayAlerts = False
excelapp.workbooks(1).close
excelapp.quit
End Sub

Here's some more ODBC/SQL code that you may find helpful:

Function Insert(rdate As String, rtime As String, rjudge As String) As Variant
	
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
	
	
Set qry.Connection = con
Set result.Query = qry
	
rdate = Format(rdate, "YYYYMMDD")
rtime = Format(rtime, "HHMM")
	
con.ConnectTo("metro")	
If con.IsConnected Then
qry.SQL = "SELECT JUDCOD FROM CMLIB.CMPJUDSV WHERE JUDNAM = '" + rjudge + "'"
result.Execute
rjudge = result.GetValue("JUDCOD")		
If rjudge <> 5 And rjudge <> 15 Then
qry.SQL = &quot;SELECT * FROM CMLIB.CMPSCHCT WHERE HERNGDAT = &quot; + rdate +_
&quot; AND HERTIM = &quot; + rtime + &quot; AND JUDCOD = &quot; + rjudge + &quot; AND HERTYP = 'SJ'&quot;
result.Execute
If result.GetValue(&quot;HERNGDAT&quot;) = False Then
' Add jury slot record to AS/400 jury calendaring
qry.SQL = &quot;INSERT INTO CMLIB.CMPSCHCT (HERNGDAT, HERTIM, HERTYP, JUDCOD, HERSEQ, HERMAX)&quot; + _ 
&quot;VALUES(&quot; + rdate + &quot;, &quot; + rtime + &quot;, &quot; + &quot;'SJ', &quot; + rjudge + &quot;,  0 , 1)&quot;
result.Execute
If result.GetErrorMessage = &quot;NO ERROR&quot; Then
Insert = True
Else
Insert = False
End If
Else	
Messagebox &quot;This judge has already has a Jury Slot scheduled for this day and time.  &quot; + Chr(10) + &quot;Please review the Jury Request Calendar or Judge View.&quot;
Insert = False
End If		
Else
qry.SQL = &quot;SELECT * FROM CVLIB.CVPSCHCTA WHERE DKTDATE = &quot; + rdate +_
&quot; AND DKTTIM = &quot; + rtime + &quot; AND JUDCOD = &quot; + rjudge + &quot; AND DKTTYP = 'SJ'&quot;
result.Execute
If result.GetValue(&quot;DKTDATE&quot;) = False Then
' Add jury slot record to AS/400 jury calendaring
qry.SQL = &quot;INSERT INTO CVLIB.CVPSCHCTA (DKTDATE, DKTTIM, DKTTYP, JUDCOD, HERSEQ, HERMAX)&quot; + _ 
&quot;VALUES(&quot; + rdate + &quot;, &quot; + rtime + &quot;, &quot; + &quot;'SJ', &quot; + rjudge + &quot;,  0 , 1)&quot;
result.Execute
If result.GetErrorMessage = &quot;NO ERROR&quot; Then
Insert = True
Else
Insert = False
End If
Else
Messagebox &quot;This judge has already has a Jury Slot scheduled for this day and time.  &quot; + Chr(10) + &quot;Please review the Jury Request Calendar or Judge View.&quot;
Insert = False
End If
End If
result.Close(DB_CLOSE)
con.Disconnect
Else
Insert = False
End If
	
End Function

HTH
Leslie

 

[BGrego]

Thanks a lot!!! I appreciate the examples.

Brett