
    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 ispossible 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 
    -------------------- 
     
    1. Start a New Project. 
     
    2. 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. 
     
    3. Add two CommandButtons to the default form. 
     
    4. 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 
     
    5. 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) 
     strAttributes = strAttributes & "UID=sa" & Chr$(0) 
     strAttributes = strAttributes & "PWD=" & 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 
     
    6. 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 
     
    7. Run the project. 
     
    8. Click Command1 to add a DSN named DSN_TEMP. 
     
    9. Click Command2 to remove the DSN named DSN_TEMP. 
     

    SUMMARY 
    ======= 
     
    This article demonstrates how to programmatically create a Data Source Name (DSN) for SQL Server using Visual Basic. The technique discussed in this article uses Windows Application Programming Interface (API) functions to create and manipulate entries in the Windows Registry. 
     
    MORE INFORMATION 
    ================ 
     
    DSNs are usually created through the ODBC Data Source Administrator 
    window, which is accessible from the Windows Control Panel. Other 
    techniques that provide access to ODBC-compliant databases include using RegisterDatabase (a Data Access Object (DAO) method), using the SQLConfigDataSource ODBC API function, or using a DSN-less connection 
    string. 
     
    However, it is possible to establish a new DSN by manually creating and manipulating values in the Windows Registry. The following technique uses the RegCreateKey, RegSetValueEx, and RegCloseKey API functions to create a system DSN for a SQL Server database. 
     
    Step-by-Step Procedures 
    ----------------------- 
     
    1. Open a new Visual Basic project. Form1 is created by default. Put a CommandButton on Form1 (Command1), and put the following code in the General Declarations section of the code for Form1: 
     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 
     
    2. Place the following code in the click event of the Command1 button on Form1: 
     
     Change the values of the DataSourceName, DatabaseName, Description, DriverPath, LastUser, and Server variables as appropriate for your environment. Any of the drivers listed on the ODBC Drivers tab of the ODBC Data Source Administrator window can be used as part of the DriverPath variable. All of these drivers can be found in C:\Windows\System for Windows 95 or Windows 98 machines and C:\Winnt\System32 for Windows NT. 
     
     Private Sub Command1_Click() 
     
     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 = "" 
     DatabaseName = "" 
     Description = "" 
     DriverPath = "" 
     LastUser = "" 
     Server = "" 
     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 Sub 
     
    3. Run the project and click on the Command1 command button. Then open up the ODBC Data Source Administrator from the Control Panel. Your new DSN will appear along with the other system DSNs that you have already created. 
<END>     
¶Ô ÓÚ Õâ ¸ö ÎÊ Ìâ £¬ ÎÒ ÓÐ Ò» ¸ö Ïë ·¨ ¿É ÒÔ ¸ø Äã ¿´ ¿´ ¡£ 
    ¿É ÒÔ Ê¹ ÓÃ ÎÄ ¼þ DSNÀ´ ´¦ Àí ¡£ ¿É ÒÔ Í¨ ¹ý ODBCÀ´ ½¨ Á¢ Ò» ¸ö ÎÄ ¼þ DSN£¬ ¿É ÒÔ µÃ µ½ Ëü ´æ ·Å µÄ Ä¿ Â¼ ¼° ÎÄ ¼þ ¸ñ Ê½ ¡£ ÒÔ ºó ¾Í ¿É ÒÔ ÔÚ ³Ì Ðò ´ú Âë ÖÐ ¶Ô Õâ ¸ö ÎÄ ±¾ ¸ñ Ê½ µÄ ÎÄ ¼þ ½ø ÐÐ ²Ù ×÷ ÁË ¡£ 
<END>
