Hello all, I am trying to create a dsn from VBA. This to be able to run reports from Excel using C/ODBC. The reports have to run on many clients and installing dsn’s on all clients would take a lot of time. This is my code in VBA: Option Explicit Private Const REG_SZ = 1 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 Sub CreateDSN() Dim DataSourceName As String Dim CN As String Dim CSF As String Dim Desc As String Dim Driver As String Dim NType As String Dim PPath As String Dim SName As String Dim DriverName As String Dim lResult As Long Dim hKeyHandle As Long 'Specify DSN parameters DataSourceName = “NAV_Test” CN = “010. UBF International” CSF = “Yes” Desc = “NAV_Test” Driver = “C:\WINNT\System32\codbc.dll” NType = “tcp” PPath = “C:\Program Files\Navision Financials” SName = “172.16.1.40” DriverName = “C/ODBC 32 bit” 'Create the new DSN-key lResult = RegCreateKey(HKEY_LOCAL_MACHINE, “SOFTWAREODBCODBC.INI” & DataSourceName, hKeyHandle) 'Set the values of the new DSN-key lResult = RegSetValueEx(hKeyHandle, “CN”, 0&, REG_SZ, ByVal CN, Len(CN)) lResult = RegSetValueEx(hKeyHandle, “CSF”, 0&, REG_SZ, ByVal CSF, Len(CSF)) lResult = RegSetValueEx(hKeyHandle, “Desc”, 0&, REG_SZ, ByVal Desc, Len(Desc)) lResult = RegSetValueEx(hKeyHandle, “Driver”, 0&, REG_SZ, ByVal Driver, Len(Driver)) lResult = RegSetValueEx(hKeyHandle, “NType”, 0&, REG_SZ, ByVal NType, Len(NType)) lResult = RegSetValueEx(hKeyHandle, “PPath”, 0&, REG_SZ, ByVal PPath, Len(PPath)) lResult = RegSetValueEx(hKeyHandle, “SName”, 0&, REG_SZ, ByVal SName, Len(SName)) 'Close the new DSN key lResult = RegCloseKey(hKeyHandle) lResult = RegCreateKey(HKEY_LOCAL_MACHINE, “SOFTWAREODBCODBC.INIODBC Data Sources”, hKeyHandle) lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, ByVal DriverName, Len(DriverName)) lResult = RegCloseKey(hKeyHandle) End Sub I’m not getting any errors, but unfortunately no dsn is created also. Does anyone know what I am doing wrong. Any help appreciated. Thank you in advance. Best Regards, Gerard van Kuijl
Try This Private Declare Function SQLConfigDataSource Lib “ODBCCP32.DLL” (ByVal _ hwndParent As Long, ByVal fRequest As Long, ByVal _ lpszDriver As String, ByVal lpszAttributes As String) As Long Private Sub Command1_Click() CreateDSN (“c:\filename.mdb”) End Sub Public Sub CreateDSN(strFile As String) On Error Resume Next Const ODBC_ADD_SYS_DSN = 4 'Add data source Const ODBC_CONFIG_SYS_DSN = 5 'Configure (edit) data source Const ODBC_REMOVE_SYS_DSN = 6 'Remove data source Dim ret%, Driver$, Attributes$ Driver = “Microsoft Access Driver (*.MDB)” & Chr(0) Attributes = “DSN=” & “SenCareDSN” & Chr(0) Attributes = Attributes & “Uid=Admin” & Chr(0) & “pwd=” & Chr(0) Attributes = Attributes & “DBQ=” & strFile & Chr(0) 'Attributes = Attributes & “DBQ=” & App.Path & “\NaviCare.mdb” & Chr(0) ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes) End Sub
Hi Ajay, Thanx for your reply. However I did some debugging tonight and noticed I made some minor errors: The pathnames in the register must contain back-slashes: lResult = RegCreateKey(HKEY_LOCAL_MACHINE, “SOFTWARE\ODBC\ODBC.INI” & DataSourceName, hKeyHandle) and lResult = RegCreateKey(HKEY_LOCAL_MACHINE, “SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources”, hKeyHandle) After modifying this everything worked just fine. But thanx again for your help. Best regards, Gerard
See also: HOWTO: Programmatically Create a DSN for SQL Server with VB http://support.microsoft.com/default.aspx?scid=kb;en-us;184608
Thanx Naji, I know the page. I indeed used some code from the microsoft examples. Thank you very much.