If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
Question re MailMerge and VB.NET
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/ |
#3
|
|||
|
|||
Question re MailMerge and VB.NET
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/ |
#4
|
|||
|
|||
Question re MailMerge and VB.NET
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/ |
#5
|
|||
|
|||
Question re MailMerge and VB.NET
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/ |
#6
|
|||
|
|||
Question re MailMerge and VB.NET
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 :-) |
Thread Tools | |
Display Modes | |
|
|