DOCUMENT:Q184608 27-APR-1998 [vbwin] TITLE :HOWTO: Manually Create a DSN for SQL Server with Visual Basic PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0 OPER/SYS:WINDOWS KEYWORDS:kbcode ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0 --------------------------------------------------------------------- SUMMARY ======= This article demonstrates how to manually 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 ================ DSN's 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. Put a command button on Form1 (the default form), and leave the default name of the command button as Command1. Write the following code in the general 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 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 DSN's that you have already created. REFERENCES ========== For additional information, please see the following articles in the Microsoft Knowledge Base: ARTICLE-ID: Q166392 TITLE : HOWTO: Use "DSN-Less" ODBC Connections with RDO ARTICLE-ID: Q147875 TITLE : HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO ARTICLE-ID: Q171146 TITLE : HOWTO: Create and Remove a DSN in Visual Basic ARTICLE-ID: Q123008 TITLE : HOWTO: Set Up ODBC Data Sources When Distributing Apps Additional query words: ====================================================================== Keywords : kbcode Technology : odbc Version : WINDOWS:5.0 Platform : WINDOWS Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 1998.