Programatically Creating ODBC DSN

[srinika]

IS there a way to create a Data Source Name Programatically, rather than taking the approach of Control Panel --- > ODBC --->... .
Can this be used for Access, SQL server & Oracle ?
If the Access database is in a different machine, will that be possible?
Srinika

[Sam_Cheung]

Hello,
In my opinion it is possible. Do you know how to create a data source in Excel Macro? Record it and put it in your program. Let me know if it is helpful. Good luck.

Sam Cheung

[Iouri]

CREATE AN ODBC ENTRY
You can create, edit, or delete your program's ODBC entry automatically. This code checks if an ODBC
source has been made; if not, it configures one according to your program's specification.
To edit or remove an existing ODBC source, change the fRequest parameter to the ODBC_CONFIG_DSN or
ODBC_REMOVE_DSN values, respectively:

'ODBC_REMOVE_SYS_DSN = 6& - for sys DSN

Declare Function SQLConfigDataSource Lib _
"ODBCCP32.DLL" (ByVal hwndParent As Long, _
ByVal fRequest As Long, ByVal lpszDriver _
As String, ByVal lpszAttributes As String) _
As Long
Public Sub MakeODBCDataSource()
Const ODBC_ADD_DSN = 1
' Add data source
Const ODBC_CONFIG_DSN = 2
' Configure (edit) data source
Const ODBC_REMOVE_DSN = 3
' Remove data source
Const vbAPINull As Long = 0&
' NULL Pointer
Dim lngRet
' Check if it has been done, only do this once
If GetSetting(App.ExeName, "options", _
"ODBCSetup", "No") = "No" Then
Dim sDriver As String
Dim sAttributes As String
sDriver = "Microsoft Access Driver (*.mdb)"
sAttributes = sAttributes & "DSN=MyDSN" & _
Chr$(0)
sAttributes = sAttributes & _
"DBQ=C:\Temp\Myfile.mdb" & Chr$(0)
lngRet = SQLConfigDataSource(vbAPINull, _
ODBC_ADD_DSN, sDriver, sAttributes)
SaveSetting App.ExeName, "options", _
"ODBCSetup", "Yes"
End if
End Sub


Iouri Boutchkine
[email protected]

[srinika]

I have not tested this, but I feel that it answers my Q completely
Thanks again
Srinika

[srinika]

Can u give the source from which u got this info. Or else tell me the way to create a connection to a SQL server in a remote machine (in the network ) or an Oracle Server in the internet using the same technique.
ie. I need to know the constants / parameters used in each situation.

Also it is useful to know a way to connect to an Access Server in a remote machine
Srinika

[NasirAbbas]

If u have ever used ado data control.. It is very easy to do this...

Just place an ADO DataControl on a form.. Right Click and select Properties and from there choose Build Connection String.. After selecting SQL Server Driver and giving appropriate user name and password and selecting Allow Saving Password and DataBase Press Test.. If it succeeds press Ok .. a string will be build .. u can copy that string from that box to ur code if u know how to create a ado connection in code. Nasir

AITG

[srinika]

Is this DSN ? or ADO connection string ?
Srinika

[mpaul]

Hi,

Here is the code............
Option Explicit
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




'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0& 'amp ' NULL Pointer

'Function Declare
#If Win32 Then

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
#Else
Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
(ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If



Public Function CreateDsn(DataSourceName As String, DatabaseName As String, Description As String, DriverPath As String, LastUser As String, Server As String)




Dim DriverName As String
Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

'DataSourceName = "<the name of your new DSN>"
'DatabaseName = "<name of the database to be accessed by the new DSN>"
'Description = "<a description of the new DSN>"
'DriverPath = "<path to your SQL Server driver>"
'LastUser = "<default user ID of the new DSN>"
' Server = "<name of the server to be accessed by the new DSN>"
DriverName = "SQL Server"

'Create the new DSN key.

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)



End Function