Question re MailMerge and VB.NET

I am trying to open a Word documents for mail merge and I keep getting a
DSN log on from Word. I have created the DSN entry and works fine
outside of the application. This is the line that gives me the problem.


WordDoc.MailMerge.OpenDataSource(Name:="", _
Connection:="DSN=$RMPLATINUM$", _
SQLStatement:=SQL, LinkToSource:=False,
AddToRecentFiles:=False, _
SubType:=8)


Any clues??


Thanks


------------------------------------------------
~~ Message posted from http://www.WordForums.com/

A few questions/points:
a. What is the actual data source? Does it require a logon? If so, and the
ODBC driver does not let you store the security info. in the DSN, you will
need to provide it in the Conection string. If you are using SQL Server and
Integrated security you /may/ need to ensure that Integrated Security is
specified in the DSN or the connection string. If you don't want to embed
security info. in the application, want good control over the application
and the user needs to log on, you should consider getting the logon details
yourself (i.e. probably in a .NET Windows form) and constructing the
connection string on-the-fly
b. can we assume you are using a machine (user/system) DSN? Otherwise, you
need the pathname of the .dsn file in Name and FILENAME=that_pathname in
Connection
c. what does the SQL string actually contain (sometimes SQL that executes
correctly outside Word does not execute from within Word, though on the
whole I've experienced this with OLEDB providers rather than ODBC drivers)
d. just in case - which version of Word?

--
Peter Jamieson

"thecoiman" wrote in message
...

I am trying to open a Word documents for mail merge and I keep getting a
DSN log on from Word. I have created the DSN entry and works fine
outside of the application. This is the line that gives me the problem.


WordDoc.MailMerge.OpenDataSource(Name:="", _
Connection:="DSN=$RMPLATINUM$", _
SQLStatement:=SQL, LinkToSource:=False,
AddToRecentFiles:=False, _
SubType:=8)


Any clues??


Thanks


------------------------------------------------
~~ Message posted from http://www.WordForums.com/

Thanks for responding.

I am connecting to SQL Server.

I am building the DSN in code each time the application runs.

This is how I am doing it.

8

Public Function CreateDSNEntry(ByVal ServerName As String, ByVal
DatabaseName As String, ByVal UserName As String, ByVal Password As
String, ByVal WindowsAuth As Boolean) As Boolean
Dim reg As RegistryKey
Dim bGood As Boolean = False

reg =
Registry.CurrentUser.OpenSubKey("Software\\ODBC\\O DBC.INI\\$RMPLATINUM$",
True)

' Key doesn't exist; create it.
If reg Is Nothing Then
reg =
Registry.CurrentUser.CreateSubKey("Software\\ODBC\ \ODBC.INI\\$RMPLATINUM$")
End If

'Update registry
If WindowsAuth Then
Try
reg.DeleteValue("UID")
Catch ex As Exception
End Try
Try
reg.DeleteValue("Password")
Catch ex As Exception
End Try
reg.SetValue("Database", DatabaseName)
reg.SetValue("Description", "DO NOT EDIT")
reg.SetValue("Driver", "SQLSRV32.DLL")
reg.SetValue("LastUser", System.Environment.UserName)
reg.SetValue("Server", ServerName)
reg.SetValue("Trusted_Connection", "Yes")
bGood = True
Else
Try
reg.DeleteValue("Trusted_Connection")
Catch ex As Exception
End Try
reg.SetValue("Database", DatabaseName)
reg.SetValue("Description", "DO NOT EDIT")
reg.SetValue("Driver", "SQLSRV32.DLL")
reg.SetValue("LastUser", UserName)
reg.SetValue("Server", ServerName)
reg.SetValue("Password", Password)
reg.SetValue("UID", UserName)
bGood = True
End If


If bGood Then
reg =
Registry.CurrentUser.OpenSubKey("Software\\ODBC\\O DBC.INI\\ODBC Data
Sources", True)
' Key doesn't exist; create it.
If reg Is Nothing Then
reg =
Registry.CurrentUser.CreateSubKey("Software\\ODBC\ \ODBC.INI\\ODBC Data
Sources")
End If
reg.SetValue("$RMPLATINUM$", "SQL Server")
End If

Return True

End Function

After I make the connection, I try to do the mail merge. The sql string
that I am sending it is like "Select * from ##WordMerge"
The version of Word is Word 2003.

The funny thing is that this all worked a couple of days ago and was
working for about a month, then all of a sudden the logon boxed
appeared.


---
Message posted from http://www.ExcelForum.com/

I have figured out what the problem is. It appears that DSN entry stores
the password but the WORD object model is not reading the password from
the DSN entry. I had to override the DSN entry and supply the password.
Here is what I did in code:

If Val(WordApp.version) 9 Then
If WinAuth Then
WordDoc.MailMerge.OpenDataSource(Name:="", _
Connection:="DSN=$RMPLATINUM$", _
SQLStatement:="Select * from
PM_MailMergeColl Where CuserID Is Not Null", LinkToSource:=False,
AddToRecentFiles:=False, _
SubType:=8)
Else
WordDoc.MailMerge.OpenDataSource(Name:="", _
Connection:="DSN=$RMPLATINUM$;DATABASE=" &
DatabaseName & ";uid=" & UserName & ";pwd=" & Password, _
SQLStatement:="Select * from
PM_MailMergeColl Where CuserID Is Not Null", LinkToSource:=False,
AddToRecentFiles:=False, _
SubType:=8)
End If

Hope this helps some one else out.


---
Message posted from http://www.ExcelForum.com/

There is an ODBC API call to create a DSN which you should, in theory, call
rather than updating the registry directly, although it may well have
exacgly the same effect as your existing DSN-creation code. However, I don't
know whether it is
possible or easy to make this call from within VB.NET. The VBA code looks
like this:

' hwndParent is the Parent Window - it can be 0
' fRequest is the type of request, as per the list of constants defined
below
' lpszDriver is a string containing the Driver name /exactly/ as it appears
in the
' ODBC Administrator tool
' lpszAttributes is a string containing KEYWORD=VALUE pairs
' terminated by chr(0)
' Returns True if successful, False if not
'
Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
(ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As
String, ByVal lpszAttributes As String) As Boolean
'
' fRequest values
'
Private Const ODBC_ADD_DSN = 1
Private Const ODBC_CONFIG_DSN = 2
Private Const ODBC_REMOVE_DSN = 3
Private Const ODBC_ADD_SYS_DSN = 4
Private Const ODBC_CONFIG_SYS_DSN = 5
Private Const ODBC_REMOVE_SYS_DSN = 6
Private Const ODBC_REMOVE_DEFAULT_DSN = 7
'
' SQLCreateDataSource initiates a DSN creation dialog, starting with a
' prompt for the DSN type (User, System, File)
' hwndParent is the Parent Window
' lpszDS is a string containing a suggested DSN name
' Returns True if successful, False if not
'
Private Declare Function SQLCreateDataSource Lib "odbccp32.dll" _
(ByVal hwndParent As Long, ByVal lpszDS As String) As Boolean
'
' GetActiveWindow just gets the Active Window handle
'
Private Declare Function GetActiveWindow Lib "User32.dll" () As Long

' Function to create or modify a machine data source

Sub ConfigDSN(DriverName As String, DSNName As String, DSNValues As String)
If SQLConfigDataSource(0, ODBC_CONFIG_DSN, DriverName, DSNValues) Then
Debug.Print DSNName & " already present - modified"
Else
If SQLConfigDataSource(0, ODBC_ADD_DSN, DriverName, DSNValues) Then
Debug.Print "Added " & DSNName
Else
Debug.Print "Could not create/modify " & DSNName
End If
End If

End Sub


Sub CreateASQLServerDSNs()
' an example showing how to make a SQL Server DSN
Dim z As String
Dim DriverName As String
Dim DSNName As String
Dim DSNInvariantValues As String
Dim DSNValues As String

' Create a zero byte string
z = Chr(0)


' SQL Server DSN

DriverName = "SQL Server"
DSNName = "mysqlserverdsn"
DSNValues = "DSN=" & DSNName & z & _
"Server=myserver" & z & _
"Network=DBMSSOCN" & z & _
"Address=mserver,1433" & z & _
"Database=mydatabase"

Call ConfigDSN(DriverName, DSNName, DSNValues)

End Sub

There's another API call for writing File DSNs - I don't think you need it
for the above:

'
' SQLWriteFileDSN writes a KEYWORD=VALUE pair to a File DSN,
' creating the file if necessary
' lpszFileName is the full path name of the DSN file
' lpszAppName is the Section name, normally "ODBC"
' lpszKeyName is the KEYNAME
' lpszString is the VALUE
' Returns True if successful, False if not
'
Private Declare Function SQLWriteFileDSN Lib "odbccp32.dll" _
(ByVal lpszFileName As String, ByVal lpszAppName As String, _
ByVal lpszKeyName As String, ByVal lpszString As String) As Boolean

Glad you managed to solve the problem. FWIW
a. the code you post below specifies ("Trusted_Connection", "Yes"). If you
need a password int he connection string, Trusted_Connection should be set
to No.
b. A nitpicking point maybe, but I wouldn't characterise the problem as
"the Word object model not reading the password from the DSN entry", since
the SQL Server driver never adds a password to the machine DSN. It may /ask/
for a password, but that is so it can go and get additional configuration
information to use in th DSN. It doesn't actually store that password, which
is probably one reason why people get quite confused when trying to create
and use SQL Server DSNs. Adding an entry to the registry by hand is not
logically quite the same thing - if SQL Server ODBC's model says "I don't
store passwords in machine DSNs", the presence of a registry entry named
"password" is not necessarily enough to fool the driver.

--
Peter Jamieson

"thecoiman " wrote in message
...
Thanks for responding.

I am connecting to SQL Server.

I am building the DSN in code each time the application runs.

This is how I am doing it.

8

Public Function CreateDSNEntry(ByVal ServerName As String, ByVal
DatabaseName As String, ByVal UserName As String, ByVal Password As
String, ByVal WindowsAuth As Boolean) As Boolean
Dim reg As RegistryKey
Dim bGood As Boolean = False

reg =
Registry.CurrentUser.OpenSubKey("Software\\ODBC\\O DBC.INI\\$RMPLATINUM$",
True)

' Key doesn't exist; create it.
If reg Is Nothing Then
reg =

Registry.CurrentUser.CreateSubKey("Software\\ODBC\ \ODBC.INI\\$RMPLATINUM$")
End If

'Update registry
If WindowsAuth Then
Try
reg.DeleteValue("UID")
Catch ex As Exception
End Try
Try
reg.DeleteValue("Password")
Catch ex As Exception
End Try
reg.SetValue("Database", DatabaseName)
reg.SetValue("Description", "DO NOT EDIT")
reg.SetValue("Driver", "SQLSRV32.DLL")
reg.SetValue("LastUser", System.Environment.UserName)
reg.SetValue("Server", ServerName)
reg.SetValue("Trusted_Connection", "Yes")
bGood = True
Else
Try
reg.DeleteValue("Trusted_Connection")
Catch ex As Exception
End Try
reg.SetValue("Database", DatabaseName)
reg.SetValue("Description", "DO NOT EDIT")
reg.SetValue("Driver", "SQLSRV32.DLL")
reg.SetValue("LastUser", UserName)
reg.SetValue("Server", ServerName)
reg.SetValue("Password", Password)
reg.SetValue("UID", UserName)
bGood = True
End If


If bGood Then
reg =
Registry.CurrentUser.OpenSubKey("Software\\ODBC\\O DBC.INI\\ODBC Data
Sources", True)
' Key doesn't exist; create it.
If reg Is Nothing Then
reg =
Registry.CurrentUser.CreateSubKey("Software\\ODBC\ \ODBC.INI\\ODBC Data
Sources")
End If
reg.SetValue("$RMPLATINUM$", "SQL Server")
End If

Return True

End Function

After I make the connection, I try to do the mail merge. The sql string
that I am sending it is like "Select * from ##WordMerge"
The version of Word is Word 2003.

The funny thing is that this all worked a couple of days ago and was
working for about a month, then all of a sudden the logon boxed
appeared.


---
Message posted from http://www.ExcelForum.com/

Hi Thecoiman,

From what I can see, this message is completely
disconnected from the original thread and response. Please
find the original thread, select the response, then click
REPLY in the interface you're using to connect to the
newsgroup. This way, your message will be in context.

Thanks for responding.

I am connecting to SQL Server.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail
:-)