How do I query a Lotus Domino (R6) server for mail merge using LDAP?

[Vince C.]

Hi.

I'd like to use a Lotus Notes directory as a source for a mail merge in Word
2000 and above. Domino server is configured for LDAP and I can make
successful queries using WAB.EXE. So I'd like to know how I can query a
Lotus Domino using LDAP for mailmerge in Word.

Thanks in advance.

Vince C.

 

[Peter Jamieson]

Unfortunately Word can't use an LDAP source directly (in fact it can't even
use the Outlook Express/WAB directly as a data source), so you will have to
export your addresses to e.g. a text file to use them as the data source for
a merge anyway. The only other way I know to get address info from Notes is
to use the Notes ODBC driver (it's on the IBM/Lotus site somewhere).
However, I had limited success because it's quite hard to work out how to
get the ODBC driver to give you the correct data.

 

[Vince C.]

Unfortunately Word can't use an LDAP source directly (in fact it can't even
use the Outlook Express/WAB directly as a data source), so you will have to
export your addresses to e.g. a text file to use them as the data source for
a merge anyway. The only other way I know to get address info from Notes is
to use the Notes ODBC driver (it's on the IBM/Lotus site somewhere).
However, I had limited success because it's quite hard to work out how to
get the ODBC driver to give you the correct data.

Thanks, Peter.

It's awful, however... You can query MS Active Directory directly but not
simple LDAP services... Hmmm. I wonder what MS wanted to ease by doing this.
Worst is Active Directory is in fact an LDAP catalog.

There is a "Microsoft OLEDB Provider for Directory Services". Can't it even
be used?

Vince C.

 

[Peter Jamieson]

It's awful, however... You can query MS Active Directory directly but not

simple LDAP services... Hmmm. I wonder what MS wanted to ease by doing this.
Worst is Active Directory is in fact an LDAP catalog.

There is a "Microsoft OLEDB Provider for Directory Services". Can't it even
be used?

Ah, I think I see roughly where you're coming from now. FWIW I wish I could
find the relevant documentation that would enable these OLEDB providers to
be used even from code, let alone the Data link dialog boxes. If you've
managed to work out how to get at ADSI data usin OLEDB from Word maybe there
is a chance of getting at LDAP data directly. Also, if you happen to have
SQL Server you may be able to "add a server" using sp_addlinkedserver. But
again, I do not know whether you can do that if you are only using the MSDE
versionof SQL Server.

 

[Vince C.]

"Peter Jamieson" <[email protected]> a écrit dans le message de
[...]

Ah, I think I see roughly where you're coming from now. FWIW I wish I could
find the relevant documentation that would enable these OLEDB providers to
be used even from code, let alone the Data link dialog boxes. If you've
managed to work out how to get at ADSI data usin OLEDB from Word maybe there
is a chance of getting at LDAP data directly. Also, if you happen to have
SQL Server you may be able to "add a server" using sp_addlinkedserver. But
again, I do not know whether you can do that if you are only using the MSDE
versionof SQL Server.

Fortunately I'm not using MSDE. In fact I have no SQL server at all but I
have seen a few articles on MSDN with code samples for dealing with
ADO/ADSI. I'm first interested in retrieving data directly using a query but
if I'm not successful I'll try the coded way.

Thanks again.

Vince C.

 

[Peter Jamieson]

Hi VInce,

I've been asking around and having a deeper look at this.

As far as I can see, you can connect to ADSI as a data source. A very simple
example is:

ActiveDocument.MailMerge,OpenDataSource _
Name:="c:\a\empty.odc", _
Connection:="Provider=ADSDSOObject;", _
SQLStatement:="SELECT * FROM 'LDAP://adsiservername'"

where empty.odc is a completely empty file that you can create in e.g.
Notepad, and adsiservername is the name of the adsi server box.

If you already knew that, you're definitely ahead of me. Although I haven't
yet worked out how to test it here, my guess is that the ADSI object is
providing a general-purpose interface to LDAP data and that you may be able
to do what you want by changing the 'LDAP' string to the one that will get
you the data from Notes. If that works, I'm sure that most Word mailmerge
users with Lotus would like to hear about it.

Normally Word would expect you to use the Data link dialog to set up a .odc
file (which basically contains the connection and SQL/command parameters. At
the moment I can't work out how to use the Data Link Properties to do that
but maybe there is a way. Also, although the "table name" is wrapped in
quotes and may well not cause any problems in Word, my guess is that there
could be problems if you try to use the Recipients dialog to set
sorts/filters.

That's as far as I've got - let me know if it gets you any further...

--
Peter Jamieson - Word MVP

"Peter Jamieson" <[email protected]> a écrit dans le message de

[...]
Ah, I think I see roughly where you're coming from now. FWIW I wish I could
find the relevant documentation that would enable these OLEDB providers to
be used even from code, let alone the Data link dialog boxes. If you've
managed to work out how to get at ADSI data usin OLEDB from Word maybe there
is a chance of getting at LDAP data directly. Also, if you happen to have
SQL Server you may be able to "add a server" using sp_addlinkedserver. But
again, I do not know whether you can do that if you are only using the MSDE
versionof SQL Server.

Fortunately I'm not using MSDE. In fact I have no SQL server at all but I
have seen a few articles on MSDN with code samples for dealing with
ADO/ADSI. I'm first interested in retrieving data directly using a query but
if I'm not successful I'll try the coded way.

Thanks again.

Vince C.

 

[Vince C.]

Thanks, Peter.

That's definitely getting me one step further. I'll try it and tell you.

Vince C.

 

[Vince C.]

Hi again, Peter.

I've run an example and I've finally found something that works. However I'm
returned only arrays of characters instead of strings. I'm investigating
further. I'm posting my test VBScript below.

Vince C.

------

Set cnx = WScript.CreateObject("ADODB.Connection")
cnx.Provider = "AdsDSOObject"

strSQL = "SELECT mail, st " & _
"FROM 'LDAP://myserver/o=MyOrg' " & _
"WHERE objectClass='person'"
cnx.Open

Set rs = cnx.Execute(strSQL)
For i = 0 to rs.Fields.Count - 1
strHead = strHead & rs.Fields(i).Name & vbTab
Next

While Not rs.EOF
strOut = strOut & GetRows(rs) & vbCrLf
rs.MoveNext
Wend

WScript.Echo strHead & vbCrLf & strOut
rs.Close


Function GetRows(rs)
On Error Resume Next
For i = 0 to rs.Fields.Count - 1
GetRows = GetRows & join(rs.Fields(i).Value)
GetRows = GetRows & " (" & typename(rs.Fields(i).Value) & ")" & vbTab
Next
End Function

 

[Robert]

This is an interesting discussion that touches on some of the issues
I've been dealing with, If you don't mind, I'd like to ask a couple of
questions that are not directly related to solving Vince's question.

First, Peter mentioned using the DataLink dialog to create an odc
file. I've seen a reference to this type of file online but can't find
out where this dialog can be accessed? From what I can tell an odc
file is very similar to a udl file, and I have been able to
successfully run a mailmerge using a SQL Server datasource with a udl
file like this:

With ActiveDocument.MailMerge
..MainDocumentType = wdFormLetters

..OpenDataSource Name:="I:\Credential\reports\Credential.udl", _
SQLStatement:=strSQL

..Destination = wdSendToNewDocument
..Execute Pause:=False
End With

Unlike Peter's example, however, this is not an empty file but
actually sets up the connection to the database. (I have no idea if
this would be useful in Vince's context.)

Second, in Vince's code below, you get a recordset rather than calling
MailMerge.Execute. I have been wondering if it's possible to run a
mailmerge programmatically from a recordset but so far have not been
able to figure out how you do it. It would seem to make things a lot
simpler if it's possible. How do you use it in your application?

Robert

 

[Vince C.]

This is an interesting discussion that touches on some of the issues
I've been dealing with, If you don't mind, I'd like to ask a couple of
questions that are not directly related to solving Vince's question.

[...]

Hi, Robert.

I don't know if Word allows merging from a Recordset object directly but as I
have no clue on how to link to an LDAP catalog directly without coding, I first
tried building a connection string using ADO. As all my attempts in Visual
Interdev were unsuccessful, I wanted to do it step by step.

My first step was more of a proof-of-concept. Just to make it clear on how I
should setup my own query. Then I planned to used it somehow in word for my
mailmerge. I'm still there .

Vince C.

 

[Peter Jamieson]

First, Peter mentioned using the DataLink dialog to create an odc

file. I've seen a reference to this type of file online but can't find
out where this dialog can be accessed? From what I can tell an odc
file is very similar to a udl file, and I have been able to
successfully run a mailmerge using a SQL Server datasource with a udl
file like this:

Thanks for raising this, especially as I'd forgotten that Word will work
with .udl files.

..odc (Office Data Connection or Office Database Connection) files are in a
sense very similar to .udl files in that they encapsulate a connection
string. However,
a. .udl uses the old .ini file syntax of having a [Section name] followed
by the connection string, e.g. for the ADsDSOObject the data link editor
seems to create a file containing
[oledb]
; Everything after this line is an OLE DB initstring
Provider=ADsDSOObject;Encrypt Password=False;Mode=Read;Bind Flags=0;ADSI
Flag=-2147483648

(as far as I know the first two lines should always be the same in a .udl,
but since the second line is a comment it is difficult to see why it has to
be there)
b. .odc wraps the connection string in XML (and uses HTML/XML "escapes" for
characters such as double-quotes) and wraps that in an HTML file
c. .odc contains other information, e.g. the "command" (typically a table
name or an SQL string, the type of command, and the type of thing (table,
whatever) the .odc is designed to fetch. As far as I can tell, when Word
OpenDataSOurce uses a .odc, it will use the SQL specified in the
OpenDataSource rather than the SQL in the .odc
d. the main point of this seems to be that the "results" of a .odc file can
be viewed in IE. This relies on some additional "behaviours" defined in a
file called DATACONN.HTC
e. since I found it quite difficult to work out how the information in an
OpenDataSource and a .odc combine I experimented with cutting stuff out of a
..odc, discovering that the simplest approach from a test point of view is to
have a completely empty one. But using a .udl is probably even more
straightforward.

To create a .odc in Word 2002/2003, when you get to the Select Data Source
dialog, you can either click on the +COnnect to New Data SOurce.odc or +New
SQL Server Connection.odc file names, which are typically listed in the My
Data Sources folder under your My Documents folder, or you can click the
New Source Button. To use an arbitrary OLEDB provider you can then select
Other/Advanced, select the provider and you should be seeing the same dialog
as you see when you double-click on a .udl. The only difference seems to be
that when you actually save the connection info., it is saved in .odc
format.

Second, in Vince's code below, you get a recordset rather than calling
MailMerge.Execute. I have been wondering if it's possible to run a
mailmerge programmatically from a recordset but so far have not been
able to figure out how you do it. It would seem to make things a lot
simpler if it's possible. How do you use it in your application?

(Vince has said that he's just using VBS to test). As far as I know there is
no way to use a "disconnected" (in-memory) recordset as a data source for a
merge. I can't imagine how you would even identify it if it was created in
another process. But since it is possible to use ADO to save and re-open
recordsets to disk (e.g. in XML format) I suppose that in principle it would
be possible to use an OLEDB provider to re-open the recordset and provide
the data to Word. The question is whether any of the existing providers can
do this using the information that you can store in a Word OpenDataSource,
and/or a .udl or .odc. Although I suspect the answer is "no", partly because
I think you have to use the Persistence provider in conjunction with the
relevant data provider and I'm not convinced that it would be possible to
set all that up in a connection string+a command (e.g. SQL statement)
a. I don't know for sure
b. I can't think of any reason why it would not be at least theoretically
possible to create an OLEDB provider to do it, or even a Word converter. I'd
attempt the former, except it looks quite difficult to create a provider
without grappling with COM and (realistically speaking) C++, neither of
which I especially want to spend time grappling with. I'd attempt the latter
as I already have a non-C++ basis for writing converters, but I'd probably
only be able to do it on a commercial basis.

 

[Peter Jamieson]

It's difficult to tell whether the problem with the return data type is to
do with vbs or to do with your provider. Certainly all the stuff I have done
here using Word OpenDataSource seems to return strings. If you open a blank
Word document, set it up as a mail merge main document, create a module in
the VBA editor, and create a sub containing the OpenDataSource code (adapted
for your system), and run it, you should at least make an "adspath" merge
field available.

FWIW I have been doing a few more tests using my existing example to try to
access an LDAP directory outside my Ads, e.g.

ActiveDocument.MailMerge,OpenDataSource _
Name:="c:\a\empty.odc", _
Connection:="Provider=ADSDSOObject;", _
SQLStatement:="SELECT * FROM 'LDAP://directory.verisign.com' WHERE cn =
'Jamieson*'"

It worked earlier today but either the server is busy or is now unreeachable
from here. In principle it ought to be possibel to specify column names such
as cn instead of * but so far I've only had that working when I query my own
ADS.

 

[Robert]

Peter,

Thanks very much for your extended reply. I was looking for the odc
dialog in all the wrong places. With your advice I was able to create
one right away and it does work.

Robert

First, Peter mentioned using the DataLink dialog to create an odc
file. I've seen a reference to this type of file online but can't find
out where this dialog can be accessed? From what I can tell an odc
file is very similar to a udl file, and I have been able to
successfully run a mailmerge using a SQL Server datasource with a udl
file like this:

Thanks for raising this, especially as I'd forgotten that Word will work
with .udl files.

.odc (Office Data Connection or Office Database Connection) files are in a
sense very similar to .udl files in that they encapsulate a connection
string. However,
a. .udl uses the old .ini file syntax of having a [Section name] followed
by the connection string, e.g. for the ADsDSOObject the data link editor
seems to create a file containing
[oledb]
; Everything after this line is an OLE DB initstring
Provider=ADsDSOObject;Encrypt Password=False;Mode=Read;Bind Flags=0;ADSI
Flag=-2147483648

(as far as I know the first two lines should always be the same in a .udl,
but since the second line is a comment it is difficult to see why it has to
be there)
b. .odc wraps the connection string in XML (and uses HTML/XML "escapes" for
characters such as double-quotes) and wraps that in an HTML file
c. .odc contains other information, e.g. the "command" (typically a table
name or an SQL string, the type of command, and the type of thing (table,
whatever) the .odc is designed to fetch. As far as I can tell, when Word
OpenDataSOurce uses a .odc, it will use the SQL specified in the
OpenDataSource rather than the SQL in the .odc
d. the main point of this seems to be that the "results" of a .odc file can
be viewed in IE. This relies on some additional "behaviours" defined in a
file called DATACONN.HTC
e. since I found it quite difficult to work out how the information in an
OpenDataSource and a .odc combine I experimented with cutting stuff out of a
.odc, discovering that the simplest approach from a test point of view is to
have a completely empty one. But using a .udl is probably even more
straightforward.

To create a .odc in Word 2002/2003, when you get to the Select Data Source
dialog, you can either click on the +COnnect to New Data SOurce.odc or +New
SQL Server Connection.odc file names, which are typically listed in the My
Data Sources folder under your My Documents folder, or you can click the
New Source Button. To use an arbitrary OLEDB provider you can then select
Other/Advanced, select the provider and you should be seeing the same dialog
as you see when you double-click on a .udl. The only difference seems to be
that when you actually save the connection info., it is saved in .odc
format.

Second, in Vince's code below, you get a recordset rather than calling
MailMerge.Execute. I have been wondering if it's possible to run a
mailmerge programmatically from a recordset but so far have not been
able to figure out how you do it. It would seem to make things a lot
simpler if it's possible. How do you use it in your application?

(Vince has said that he's just using VBS to test). As far as I know there is
no way to use a "disconnected" (in-memory) recordset as a data source for a
merge. I can't imagine how you would even identify it if it was created in
another process. But since it is possible to use ADO to save and re-open
recordsets to disk (e.g. in XML format) I suppose that in principle it would
be possible to use an OLEDB provider to re-open the recordset and provide
the data to Word. The question is whether any of the existing providers can
do this using the information that you can store in a Word OpenDataSource,
and/or a .udl or .odc. Although I suspect the answer is "no", partly because
I think you have to use the Persistence provider in conjunction with the
relevant data provider and I'm not convinced that it would be possible to
set all that up in a connection string+a command (e.g. SQL statement)
a. I don't know for sure
b. I can't think of any reason why it would not be at least theoretically
possible to create an OLEDB provider to do it, or even a Word converter. I'd
attempt the former, except it looks quite difficult to create a provider
without grappling with COM and (realistically speaking) C++, neither of
which I especially want to spend time grappling with. I'd attempt the latter
as I already have a non-C++ basis for writing converters, but I'd probably
only be able to do it on a commercial basis.

 

[Peter Jamieson]

Vince (and Robert if you have an interest in this too):

I have now run into a similar "data type" problem to the one you
encountered. When I use OpenDataSource try to SELECT any field except * from
the records I'm trying to retrieve from Lotus, the value is blank. If
instead I get the data via ADO and RecordSet, and specifying a field such
as cn in the SELECT, VBA fails whenever I try to access the Value property
of the Field (e.g. rsPerson.Fields("cn").Value). When I look at the field
type, it is 12 (apparently Variant), which should allow me just to use e.g.
CStr() to return the string. But it just doesn't work. Unless someone has a
really bright idea I'm currently inclined to say "It's a nice idea, but too
many programmability/compatibility issues stand in the way of making it
work". Unless there is a relevant setting in e.g. Lotus that can specify the
column's data type, I suspect using OpenDataSource is never going to work,
which would rule out a mailmerge direct from the data source. Using LDAP to
do an export via ADO might still be feasible but it may be easier to do the
necessary export some other way.