Access shows Notes ODBC field as zero or blank

[MVSGuy]

Hello, I have a problem where a Notes field shows up in Access (via
ODBC connection) but the value is either zero or blank in Access.
I've verified the field is not zero or blank in Notes.

Background - This is an existing ODBC connection from Access to
Notes. For all other fields, it works fine. I've added a number
field to the Notes database. It shows up in Access but never has the
correct value.

Is there something simple I'm missing?

Regards,
MVSGuy

[MVSGuy]

Additional information -
The Access linked table is pulling in all documents of a specific Notes
form. I set up new linked table with the same (negative) results.
However when I set up a Notes view to display the fields, a linked
table against the view will pull in the desired results.

Pulling in the actual document does not work where pulling in a view of
all forms does.
Can anyone explain this?

Regards,
MVSGuy

[The Frog]

Hi MVSGuy,

Yeah, I can explain it. The issue is that NotesSQL is not a true SQL
driver / interface. The only way to actually get at data is via the
views. The other objects / tables that you see are based on the
definitions of the objects themselves, and not the data that might use
that object.

If you want to play with Notes, and Notes type objects, you may be
better off trying to play with the OLE methods and objects. It all
depends on what you want to achieve. I have played with Notes for a
while now, but still consider myself a bit of a rube with it die to the
lack of decent documentation. Most of what I have learnt is via trial
and error.

I can suggest that there is a far greater deal of control and data
accessability via the OLE methodology, but it requires a bit of VBA
coding. I have attached below a sample function to read Notes emails
(you need to make a reference to the Lotus Domino Objects in the VBA
project).
This will allow you to read an email (body of text) from a notes
database, where you supply the subject of the email, and the password
to access the database for the userid.

Cheers, and I hope this helps

The Frog
-----------------------------------------------------------------------------------------------------------------------

Function MailReader(EmailSubject As String, password As String) As
Variant

'The lotus notes client must be installed on the machine
'make sure this is done=C:\Program Files\Notes then type "regsvr32
nlsxbe.dll"
'make sure that there is a reference to "Lotus Domino Objects" under
tools/references

If EmailSubject = "" Then
MailReader = "No Subject To Search For"
End Sub
End If

If password = "" Then
MailReader = "No password given for login"
End Sub
End If

Dim Session As New NotesSession
Dim MailDB As NotesDatabase
Dim Collection As Object
Dim Document As NotesDocument
Dim Subject As Variant
Dim DocumentCount As Long
Dim BodyText() As String
Dim BodyCount As Long

Session.Initialize (password)

Set notesdir = Session.GetDbDirectory("")
Set MailDB = notesdir.OpenMailDatabase

Set Collection = MailDB.AllDocuments

BodyCount = 0

For i = 1 To Collection.Count
Set Document = Collection.GetNthDocument(i)
Subject = Document.GetItemValue("Subject")
If Subject(0) = EmailSubject Then
ReDim Preserve BodyText(BodyCount)
temp = Document.GetItemValue("Body")
BodyText(BodyCount) = temp(0)
BodyCount = BodyCount + 1
'Call Document.Remove(True) 'This would delete the document
from the database
'Depending on synchronisation settings the document may still
appear to be there
'but actually the document is gone and the view simply needs a
refresh to reflect
'the change
End If
Next i

If BodyCount <0 Then
MailReader = BodyText
Else
MailReader = "No email found with that subject"
End If

Set Document = Nothing
Set Collection = Nothing
Set MailDB = Nothing
Set Session = Nothing
'It is very important to do the cleanup here or else you risk a crash

End Function

[MVSGuy]

The only way to actually get at data is via the

views. The other objects / tables that you see are based on the
definitions of the objects themselves, and not the data that might use
that object.

OK, that fits well with what I'm seeing. A number field in the form
shows up as text on a view.

Thank you for your help on OLE. I love it and wish I could do all my
work in it, but at the moment my question deals with a legacy PC
application. (MAN! It does a mainframers heart good to say that!!!)

The app is [poorly] written in a series of queries chained together
into a macro. I'm told not to recode it in VB as the app is 'going
away'. It's been 'going away' for years now and I'm restricted to
doing [constant] incremental changes.

Again, thank you Frog for you explanation. It helps greatly.

Regards,
MVSGuy

P.S. "Legacy PC application." Tee hee!

[The Frog]

Anytime, glad to be of help.

Let me know if you get stuck anywhere, and if I can help I will :)

Cheers

The Frog