ODBC connection Works w/Excel Fails w/Word

[snowman]

I am trying to use a MySQL DB to provide Word merge data.

I have established a DSN connection that will import data into an Excel
sheet. I can then use the Excel data as a source for Word.

However if I try to use the DSN connection directly in Word, I get this
error message:

"The connection failed because of an error in initializing provider.
Catastrophic failure."

Where have I gone astray??

 

[Peter Jamieson]

I doubt if you have gone astray, but Word is much more finickity about ODBC
connections than Excel.

Which version of Word are you using? Did you try to connect using MS Query?
If your DSN was a file DSN and you tried to connect any other way it
probably won't work. To use MS Query in Word 2000 or earlier, click the MS
Query button in Open Data Source and take it from there. In Word 2002 or
later, in Select Data Source, click the Tools menu at the to pright of the
dialog. MS Query may or may not be installed.

If /that/ doesn't work, it's probably either because Word has truncated one
of the strings involved, or lost the necessary login credentials when it
created the connection string. The only way around that is probably to do
the connection using a VBA OpenDataSource call, which would need to look
something like the following for a MySQL connection:

Sub ConnectViaMachineDSNWord2000()
' For a DSN called mydsn with all the other conneciton info (SERVER,
' DATABASE, perhaps PORT and OPTIONS),
' password mypw, table mytable
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;PWD=mypw;", _
SQLStatement:= "SELECT * FROM mytable"
End Sub

For Word 2002/2003 you may need the following except maybe in recent SPs

Sub ConnectViaMachineDSNWordXP2003()
' For a DSN called mydsn with all the other conneciton info (SERVER,
' DATABASE, perhaps PORT and OPTIONS),
' password mypw, table mytable
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;PWD=mypw;", _
SQLStatement:= "SELECT * FROM mytable", _
Subtype:=wdMergeSubtypeWord2000
End Sub

Sub ConnectViaFileDSN()
' For a File DSN called c:\mydsns\mydsn.dsn with
' all the other conneciton info (SERVER,
' DATABASE, perhaps PORT and OPTIONS),
' password mypw, table mytable
, password mypw, table mytable
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\mydsns\mydsn.dsn", _
Connection:="FILEDSN=c:\mydsns\mydsn.dsn;PWD=mypw;", _
SQLStatement:= "SELECT * FROM mytable"
End Sub

Peter Jamieson