How to programmatically change the database in a DSN that is using a trusted connection in Access 2002 (316752)

SUMMARY

This step-by-step article shows you how to programmatically change a DSN that is using a trusted connection. You may want to use this technique because, if you use a DSN with a trusted connection to connect to SQL server, you cannot manually change the database when you are linking or importing tables. You may also want to use this technique if you do not want to create a separate DSN whenever you want to change the database. Finally, you may want to use this technique if you do not want to use SQL security. SQL security does allow you to select another database when you link or import tables.

back to the top

Steps to Programmatically Change a DSN

1. Start Microsoft Access 2002, and then open any database.
2. Create a new table that has two fields, one named Database and the other Description. Make the data type for both fields Text.
3. Save the table as tblDatabases.
4. Open the tblDatabases table, and then add data to the Databases and Descriptions fields.
5. Create a new form.
6. Add a combo box to the form with the following properties:
   
   

   Property	Value
   Name	cboDatabases
   Row Source	Select * from tblDatabases;
   Column Count	2
   Column Widths	1";0"

7. Add a command button to the form. Change the Caption property of the command button to Change DSN.
8. Set the OnClick property of the command button to the following event procedure. Make sure to change the values for the DataSourceName, DatabaseName, Description, DriverPath, LastUser, and Server variables as appropriate for your environment.

   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 strTrusted As String
         Dim lResult As Long
         Dim hKeyHandle As Long
   
         'Specify the DSN parameters.
   
         DataSourceName = "CodeTestSQL"
         DatabaseName = Me.cboDatabases
         Description = Me.cboDatabases.Column(1)
         'Path to your sqlsrv32.dll
         DriverPath = "<Path to>\sqlsrv32.dll"
         'Your network user name
         LastUser = "<user name>"
         'Your SQL server
         Server = "<server name>"
         DriverName = "SQL Server"
         strTrusted = "yes"
         '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))
          lResult = RegSetValueEx(hKeyHandle, "Trusted_Connection", 0&, REG_SZ, _
            ByVal strTrusted, Len(strTrusted))
         '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)
           MsgBox "Database changed to " & Me.cboDatabases & " in DSN CodeTestSQL!"
       
   					

9. Type or paste the following in the General Declarations section of the code for the form:

   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
   					

10. Save the form as frmChangeDSN.
11. Open the frmChangeDSN form in Form view.
12. Select a database name from the combo box, and then click Change DSN.
13. Note that you receive a message box that confirms the database and DSN names. Click OK.
14. On the File menu, point to Get External Data, and then click Link.
15. Change the Files of Type box to ODBC Databases().
16. On the Machine Data Source tab, click CodeTestSQL, and then click OK.
17. Note that the tables that are listed are from the database that you selected on the form.
18. Click Cancel.
19. Return to the frmChangeDSN form, select a different database, and then click the command button.
20. Repeat steps 12 through 17. Note that the tables listed are from the new database that you selected.

back to the top