Create a System DSN on the fly
I'm working on updating my article: "Creating DSNs for SQL Server: In depth on how to make one."
I'm trying to update the VBA code section to add it from Access 2003 on a Windows 7 Pro system.
Any help or ideas -- even code testers would be appreciated.
I'm trying to update the VBA code section to add it from Access 2003 on a Windows 7 Pro system.
Option Compare Database
Option Explicit
Option Base 1
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
Public Function Add_DSN_Win7_System_DSNs()
'Open Þ Load Þ Resize Þ Activate Þ Current
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim Driver As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String
Dim AddSetup As String
Dim lResult As Long
Dim hKeyHandle As Long
DriverName = "SQL Server"
'Specify the DSN parameters.
DataSourceName = "TestDSN1"
DatabaseName = "Adventureworks"
Description = "Adventureworks Database ODBC Call"
Driver = "C:\Windows\system32\SQLSRV32.dll"
LastUser = "sa"
Server = "(local)"
AddSetup = "No"
'Create the new DSN key.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\Wow6432Node\ODBC\ODBC.INI" & _
DataSourceName, hKeyHandle)
'Set the values of the new DSN key.
lResult = RegSetValueEx(hKeyHandle, "QuotedId", 0&, REG_SZ, _
ByVal AddSetup, Len(AddSetup))
lResult = RegSetValueEx(hKeyHandle, "AnsiNPW", 0&, REG_SZ, _
ByVal AddSetup, Len(AddSetup))
lResult = RegSetValueEx(hKeyHandle, "AutoTranslate", 0&, REG_SZ, _
ByVal AddSetup, Len(AddSetup))
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 Driver, Len(Driver))
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.
'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\test2sql
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)
End FunctionAny help or ideas -- even code testers would be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I ran your code on my Win7 64-bit box. This produced the following reg entries (see screenshot):
RegDSN.png
RegDSN.png
forgot this reference above:
http://support.microsoft.com/kb/184608
http://support.microsoft.com/kb/184608
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are right about the "SOFTWARE\Wow6432Node\ODBC
But mine are ending up under HKCU instead of HKLM.
And the reason that I'm trying to get to the Wow6432Node is a 32 bit app won't understand and use the HKLM\Software\ODBC\ODBC.IN
But mine are ending up under HKCU instead of HKLM.
And the reason that I'm trying to get to the Wow6432Node is a 32 bit app won't understand and use the HKLM\Software\ODBC\ODBC.IN
Ahh .... I did have UAC turned OFF at the time. I'll turn it back on and try it again. I'm betting that's the issue.
Jim,
I'm going to look at the code I did a while back. Don't remember why, but I did it in VBA first, then gave up and moved it to VB6. There was some type of restriction on doing this from VBA. Don't remember off-hand what but I'll dig. Not sure if I was using the same method as you or not though (it's been a while since I looked at that code).
JimD.
I'm going to look at the code I did a while back. Don't remember why, but I did it in VBA first, then gave up and moved it to VB6. There was some type of restriction on doing this from VBA. Don't remember off-hand what but I'll dig. Not sure if I was using the same method as you or not though (it's been a while since I looked at that code).
JimD.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
https://www.experts-exchange.com/questions/26867495/vbs-script-to-create-ODBC-system-DSN-works-in-XP-but-not-Windows-7.html?cid=1572&anchorAnswerId=35088939#a35088939
Sort of have an insight into it here. Now just have to figure out how to incorporate that into VBA.
I haven't been ignoring you all -- Just 50+ hour weeks are a killer.
Sort of have an insight into it here. Now just have to figure out how to incorporate that into VBA.
I haven't been ignoring you all -- Just 50+ hour weeks are a killer.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
lResult = RegCreateKey(HKEY_LOCAL_MA
DataSourceName, hKeyHandle)
There's no trailing "\" after ODBC.INI ...IOW, if I pass in a DataSouceName as "Scott", it would try to create a key named:
"SOFTWARE\Wow6432Node\ODBC
Instead of
"SOFTWARE\Wow6432Node\ODBC
Otherwise: What sort of errors/troubles do you get when trying to execute the code?