Hi all,
I have been trying to create a System DSN via VBA. The problem is, I can't find the way to select "With Windows NT authentication using the network login ID"
For investigation purposes, I have created 2 DSN's manually in ODBC Data Source Administrator, using the two authentication methods (i.e. SQL Server authentication & Windows NT authentication). I then, went through the registry to identify the difference in the reg key entries. There appears to be nothing different.
Here is the code i am using:
Any help out there?
Thanks,
rnodern
I have been trying to create a System DSN via VBA. The problem is, I can't find the way to select "With Windows NT authentication using the network login ID"
For investigation purposes, I have created 2 DSN's manually in ODBC Data Source Administrator, using the two authentication methods (i.e. SQL Server authentication & Windows NT authentication). I then, went through the registry to identify the difference in the reg key entries. There appears to be nothing different.
Here is the code i am using:
Code:
Private Const REG_SZ = 1 'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Code:
Function CreateDSN()
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String
Dim lResult As Long
Dim hKeyHandle As Long
On Error GoTo Error1
'Specify the DSN parameters.
DataSourceName = "Data_Source_Name" 'I've changed this for demonstration purposes
DatabaseName = "Database_Name"
Description = "Description_Name"
DriverPath = "C:\WINDOWS\system32"
LastUser = "User_Name" 'I've tried not including this line, but it doesn't make a difference
Server = "SERVER_IP_OR_DNS"
DriverName = "SQL Server"
'Create the new DSN key in said registry.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DataSourceName, hKeyHandle)
'Set the values of the new DSN key.
lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))
'Close the new DSN key.
lResult = RegCloseKey(hKeyHandle)
'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)
'MsgBox "Install Complete!"
Exit Function
Error1:
MsgBox Err.Description & vbNewLine & "You may not have sufficient access to create an ODBC connection. This will need to be setup manually."
End Function
Any help out there?
Thanks,
rnodern