It is possible to create ODBC - DSN connections programmatically. There are many ways by which you can create DSNs programmatically. I will just tell you some of the possibilities. Which one to implement, depends upon the type of Data access methods you are using.
You can establish a new DSN by manually creating and manipulating values in the Windows Registry. Let us try out creating one for SQL Server. Creating or manipulating values in the registry requires calling API functions from Visual Basic. You will need 3 API functions. They are as listed below. You will find these functions in API Text Viewer as they are displayed below:
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
You can place the above functions in the general declarations or in basic modules (replace the word private with public in case of basic modules). Now place the following code in a function. The following function will create a DSN for you. So here goes…
Private 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
'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>"
'This driver path for SQL Server on my machine is E:\WINNT\System32\SQLSRV32.dll"
'In case of Access Driver it is "E:\WINNT\System32\odbcjt32.dll"
LastUser = "<default user ID of the new DSN>"
Server = "<name of the server to be accessed by the new DSN>"
' DBQ = "<name of the Access database with full path>" 'In case of MS Access
DriverName = "SQL Server" 'In case of SQL Server
' DriverName = "Microsoft Access Driver (*.mdb)" 'In case of MS Access
'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
And yeah, I forgot. You will also have to declare some constants for HKEY_LOCAL_MACHINE and REG_SZ. REG_SZ stands for a string variable type. You can do it as follows. Just put the following statements in the place of the API functions declarations.
Private Const REG_SZ = 1
Private Const HKEY_LOCAL_MACHINE = &H80000002
After coding in the function, execute it. Open ODBC Data Manager. You will find your DSN.
There is another method. Using a function 'SqlConfigDataSource'. This function is written in ODBCCP32.dll. But you will have to do some R&D for using this function coz I have not used this function (and frankly I don't know).
So this was about creating DSN connections programmatically through Visual Basic. If you search through the materials of MSDN you will find that they were easily available. For a Microsoft guy MSDN is a Bible. You will find answers to 98% of your queries.
Now that you know how to create DSN connections I would like to tell you something. Nowadays it is being increasingly popular for not using existing ODBC Data Stores. This releases the burden on application setup. Moreover you don't have to worry about creating DSNs on all the client machines. So I will also give you some information regarding these issues.
I will be using ADO for data access. So here goes…
You will have to put a reference in your project for Microsoft Active Data Objects 2.0. Then you can write the following function in your Visual Basic Project.
Private Sub GetConnection()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "Driver={SQL Server};server=<Server Name>;uid=sa;pwd=;database=pubs;"
cnn.Open
' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Connection Failed"
End If
' Close the connection.
cnn.Close
End Sub
The above function will connect to SQL Server's Pubs database. This was once again using ODBC. But the difference is that we are not using existing ODBC DSN. We are using ODBC Drivers directly. This is like creating temporary DSNs on the fly. But ADO provides another efficient way. Using something called 'OLEDB Providers'. These Providers are installed along with the databases. The function below will tell you how to access the database using providers. I will be connecting to SQL Server and also to Access Database.
Private sub ConnectToSQLServer()
Dim objAdoConnection as New ADODB.Connection
objAdoConnection.ConnectionString = "Provider=SQLOLEDB;Server=<your server name>;Database=Pubs;Uid=sa;Pwd=;"
objAdoConnection.Open
'Check if the connection succeeded
If objAdoConnection.State = adStateOpen then
MsgBox "Connection Successful"
Else
MsgBox "Connection Failed"
End If
objAdoConnection.Close
End Sub
Private sub ConnectToAccess()
Dim objAdoConnection as New ADODB.Connection
objAdoConnection.ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source=<database name with path>;Uid=;Pwd=;"
objAdoConnection.Open
'Check if the connection succeeded
If objAdoConnection.State = adStateOpen then
MsgBox "Connection Successful"
Else
MsgBox "Connection Failed"
End If
objAdoConnection.Close
End Sub
Hope this information will help you. Just let me know how useful this info was to you.