Connecting to SQL Server for mailmerge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cc900630@ntu.ac.uk - 31 May 2005 00:49 GMT

Hi, I am trying to write vba program that will work on Word 2000 & 2002
clients and connect to SQL 2000 to produce a batch of invoices using
mailmerge automation.

I am having problems with the connection.It gives a runtime error 5174,
"This file could not be found", although I have tested the code and
both the connection string and SQL works fine (see below). Any help /
alternatives appreciated.

thanks
hals_left

Sub MailMergeTest()

Dim strConn, strSQL, objRS, strResult
Dim objConn As New ADODB.Connection

' SQL Server 2K on local machine integrated security
strConn = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=pubs;" & _
"Integrated Security=SSPI"

strSQL = "Select stor_id, stor_name from stores"

' ~~~~~~~~ Test the connection manually
objConn.Open strConn
Set objRS = objConn.Execute(strSQL)
While Not objRS.EOF
strResult = strResult & objRS(0) & "-" & objRS(1) & vbCrLf
objRS.MoveNext
Wend
MsgBox strResult
objConn.Close
Set objConn = Nothing

' ~~~~~~~~~ Now mailmerge - doesnt work
ActiveDocument.MailMerge.OpenDataSource strConn, , , , , , , , , ,
, , , strSQL
ActiveDocument.MailMerge.Execute
End Sub

Reply to this Message

hals_left - 31 May 2005 01:31 GMT

After some searching, this does seem to be a known problem.

I have managed to get it working now with a file dsn, below, but this
is not the ideal solution as the database will be remote hosted and the
clients distributed. If anyone has any solutions or know the versions
that do support this please let me know.
thanks.

' ~~~~~~~~~ using a file dsn
ActiveDocument.MailMerge.OpenDataSource mydsn, , , , , , , , , , ,
"DSN=mydsn", strSQL
ActiveDocument.MailMerge.Execute

Reply to this Message

Peter Jamieson - 31 May 2005 09:38 GMT

The code in your first message uses an OLEDB connection string which will
not work in Word 2000 because it does not support OLEDB at all (Word 2002
does). i.e. in Word 2000 you either have to connect via ODBC or using some
other indirect route such as connecting to a Jet database that has links to
a SQL Server database.

As you have noticed, the problem is that you have to distribute an
additional <something> when you distribute the document . - The Name
parameter in the OpenDataSource call must either contain a file name, or it
has to be blank, in which case
a. Word expects you to supply a system/user DSN name in the Connection
parameter (i.e. the <something> is a DSN)
b. in Word 2002 and later, you have to supply the parameter
Subtype:=wdMergeSubtypeWord2000

If you specify a file (e.g. a file DSN) then
c. the file must exist
d. the file cannot be at a URL address - it must be a local file or
addressable via Windows networking, e.g. by using a drive letter connected
to a share or using a UNC network name. So if all your clients happen to be
able to connect to a windows networking folder somewhere, you /might/ be
able to put the necessary file DSN there

In the case of an SQL Server connection, you obviously have to have the
appropriate ODBC driver on each system as well.

FWIW, I am surprised that have managed to get

> ActiveDocument.MailMerge.OpenDataSource mydsn, , , , , , , , , , ,
> "DSN=mydsn", strSQL

to work. In my experience, when you specify a file dsn, you have to specify
the file's pathname in the Name parameter (which is presumably what you have
done) and at least

"FILEDSN=the pathname of the DSN;"

in the Connection parameter. I may be wrong of course, but wonder if your
current statement only works because you /also/ have a user/system DSN
called "mydsn" as well as the file dsn. or maybe it works if the file dsn is
in the default location for file DSNs

The only other suggestions I can make are
a. if you choose to distribute a file DSN as well as a Word document, it
can be loacted anywhere so e.g. you can put it in the same folder as the
.doc and use VBA code to build the full pathname of the file.
b. if for some reason you need to avoid having a separate /file/, you can
create a user DSN dynamically using VBA and Win32 calls (I have some code
somewhere if you need it) then reference it in OpenDataSource.

Peter Jamieson

> After some searching, this does seem to be a known problem.
>
[quoted text clipped - 8 lines]
> "DSN=mydsn", strSQL
> ActiveDocument.MailMerge.Execute

Reply to this Message

cc900630@ntu.ac.uk - 01 Jun 2005 22:59 GMT

Peter, Thanks for the info on dsn.

It is surprising that word 2K doesnt use OLEDB for mailmerge.

The comments you made on my dsn working are probably true - it was late
and dsn is new to me, as I usually use dsn-less connections. I dont
reacall being promted for a location to store it so it must have been a
system dsn.

While a dsn isnt ideal it may actually work OK over VPN with a mapped
drive letter, I will have to test this. There may be some
authentication issues too.

Ideally I want the same solution (single distributed word file with
signed vba) for Word2K & 2002 Clients.

Do you think its a bad approach to re-invent the wheel here and write
mailmerge type of functionality using code to create multiple instances
of a template and replace placeholders with adodb recordset fields .
Something like this

strConn = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=pubs;" & _
"Integrated Security=SSPI"
strSQL = "Select stor_id, stor_name from stores"

objConn.Open strConn
Set objRS = objConn.Execute(strSQL)
While Not objRS.EOF
Application.Documents.Add ("myTemplate.doc")
With Documents(Application.Documents.Count)
.Range.Find.Text = "STOR_ID"
.Range.Find.Replacement.Text = objRS(0)
End With
objRS.MoveNext
Wend

Would this approach be any less efficient or more error prone for
clients, for 100 documents than a mailmerge ?

Reply to this Message

Peter Jamieson - 02 Jun 2005 07:18 GMT

> and dsn is new to me, as I usually use dsn-less connections. I dont

I've seen tiny bits of evidence that Word does set up DSN-less connections
itself in some circumstances but I've never been able to do it using either
OpenDataSource or the DATABASE field.

> While a dsn isnt ideal it may actually work OK over VPN with a mapped
> drive letter, I will have to test this. There may be some
> authentication issues too.

Never had to try that myself so would be interested in the outcome.

> Ideally I want the same solution (single distributed word file with
> signed vba) for Word2K & 2002 Clients.

One problem you may face is that you may have to use the Subtype parameter
with OpenDataSource in Word 2002, certainly if you use a blank Name
parameter whereas it is regarded as invalid in Word 2000. I think you can
get around this in VBA by wrapping the calls in different subs/functions and
calling the correct one depending on the Word version. Another thing to
notice is that if you need to get tthe details of an existing connection you
can't usually do it in Word 2002 via the MailMerge.DataSource object because
of an error in Word. I suspect in your case neither of these things will be
an issue though.

> Do you think its a bad approach to re-invent the wheel here and write
> mailmerge type of functionality using code to create multiple instances
> of a template and replace placeholders with adodb recordset fields .

...

> Would this approach be any less efficient or more error prone for
> clients, for 100 documents than a mailmerge ?

Generally speaking, I believe it is less efficient, but that would not
usually be my primary concern unless the merges were very large. I would
usually be more concerned with function (i.e. "does it do what is needed"),
stability, distributability (as you are) and maintenance. If you are
essentially using Word as a development platform with simple text
replacement facilities, and end users never need to modify the document
content, insert new "placeholders" etc. I think it's a good approach. As
soon as end users need to be able to tweak stuff, you need to be sure your
code is robust, they understand how the placeholder system works, and so on.
For example, in theory users can nest fields in the built-in mailmerge
system. You probably don't need to allow that but if you do, the coding
effort increases dramatically. Arguably, "rolling your own" merge code gives
you more control and makes certain types of merge simpler, e.g. when you
need to output one document per record in the data source, or one print job
per record in the data source (important if, for example, documents are
being stapled automatically by the printer).

Peter Jamieson

> Peter, Thanks for the info on dsn.
>
[quoted text clipped - 36 lines]
> Would this approach be any less efficient or more error prone for
> clients, for 100 documents than a mailmerge ?

Reply to this Message