Can I Mail Merge from SQL Database without an odc datasource?

[Robert Jones]

I want to be able to run a mail merge using data from a SQL database, but I
don't want to have to set up an odc datasource for everyone who will be
running the mail merge. I can't guarantee a shared network drive so sharing
an odc isn't an option either. If you look at the options for a mail merge:

ActiveDocument.MailMerge.OpenDataSource Name:="Name_Address.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;Extended
Properties=""DSN=MyLetters;UID=Work;APP=Microsoft Office
2003;WSID=12345;DATABASE=MyDatabase;Trusted_Connection=Yes"";Initial
Catalog=iMIS" _
, SQLStatement:="SELECT * FROM ""Name_Address""", SQLStatement1:="",
_
SubType:=wdMergeSubTypeOther

it looks as if the mail merge already has all the SQL information it needs
in the Connection and SQLStatement properties. Is there a way to avoid
having to specify the odc file?

 

[Peter Jamieson]

Unfortunately, you have to have /something/ other than the Word document.
The something can either be a .odc, a .udl, an ODBC file dsn, or an ODBC
machine dsn (this is the only "something" that is not a file in the file
system).

However, the .odc can be a completely blank file, as long as all the
necessary connectivity and SQL information is in the OpenDataSource call. So
one approach might be to create a single "empty.odc" in a standard location
on all the machines that need one and use that for all merges that need a
..odc, or maybe you could use VBA to create a blank .odc in the same folder
as the .doc (say) before issuing the OpenDataSource call.

Peter Jamieson

 

[Robert Jones]

I've gone with the empty odc file and it appears to work fine - thanks this
is really useful.