Microsoft KB Archive/171146

= How To Create and Remove a DSN in Visual Basic =

Article ID: 171146

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition

-



This article was previously published under Q171146



SUMMARY
In the 32-bit version of Visual Basic 4.0, it is possible to use DSN-less connections, but because the 16-bit version of ODBC can not handle this syntax, it is not possible to do this in the 16-bit version of Visual Basic 4.0. It is possible to emulate this ability by dynamically creating and removing a Data Source Name (DSN) on the fly using the SQLConfigDataSource ODBC API call.

The following is a 16-and 32-bit example that demonstrates this technique. The 32-bit code was included because this technique has other uses as described below. The 32-bit techniques presented in this article also apply to Visual Basic 5.0.



MORE INFORMATION
The 32-bit ODBC ability to use a DSN-less connection has many uses:


 * 1) Client Simplicity. The user does not have to worry about setting up a DSN, naming it correctly, setting up options, etc. All this can be done dynamically by the application.
 * 2) It solves many JET engine connection and connect string caching issues.
 * 3) Increases the flexibility of the application.

All of these uses can be realized in 16-bit ODBC by creating and deleting a DSN on the fly. This method is also useful for simple DSN management. The code could be used to automatically create, modify, or delete a DSN at any time. Visual Basic does provide the ability to create a DSN using the DBEngine.RegisterDatabase method, but the API provides greater functionality and the ability to modify and remove a DSN, as well.

Step-by-Step Example
 Start a New Project. In the Advanced tab of the Options dialog box under the Tools menu, set a Conditional Compilation Argument named WIN32 equal to 1 if using Visual Basic 4.0 32-bit, or 0 if using Visual Basic 4.0 16-bit. Add two CommandButtons to the default form.  Add the following code to the General Declarations: Option Explicit

'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& ' 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                     Add the following code into the Click event of Command1: #If WIN32 Then Dim intRet As Long #Else Dim intRet As Integer #End If     Dim strDriver As String Dim strAttributes As String

'Set the driver to SQL Server because it is most common. strDriver = "SQL Server" 'Set the attributes delimited by null. 'See driver documentation for a complete 'list of supported attributes. strAttributes = "SERVER=SomeServer" & Chr$(0) strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0) strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0) strAttributes = strAttributes & "DATABASE=pubs" & Chr$(0) 'To show dialog, use Form1.Hwnd instead of vbAPINull. intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _     strDriver, strAttributes) If intRet Then MsgBox "DSN Created" Else MsgBox "Create Failed" End If                     Add the following code into the Click event of Command2: #If WIN32 Then Dim intRet As Long #Else Dim intRet As Integer #End If     Dim strDriver As String Dim strAttributes As String

'Set the driver to SQL Server because most common. strDriver = "SQL Server" 'Set the attributes delimited by null. 'See driver documentation for a complete list of attributes. strAttributes = "DSN=DSN_TEMP" & Chr$(0) 'To show dialog, use Form1.Hwnd instead of vbAPINull. intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _     strDriver, strAttributes) If intRet Then MsgBox "DSN Deleted" Else MsgBox "Delete Failed" End If                    Run the project.</li> Click Command1 to add a DSN named DSN_TEMP.</li> Click Command2 to remove the DSN named DSN_TEMP.</li></ol>

<div class="references_section">