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.
