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

Article translations Article translations
Article ID: 316752 - View products that this article applies to.
This article was previously published under Q316752
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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.

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:

    Collapse this tableExpand this table
    PropertyValue
    NamecboDatabases
    Row SourceSelect * from tblDatabases;
    Column Count2
    Column Widths1";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.

References

For additional information about programmatically creating a DSN, click the following article number to view the article in the Microsoft Knowledge Base:
184608 How To Programmatically Create a DSN for SQL Server with VB

Properties

Article ID: 316752 - Last Review: October 26, 2013 - Revision: 5.0
Applies to
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbnosurvey kbarchive kbhowtomaster KB316752

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com