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

This article was previously published under Q316752
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK

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:

    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 = &H80000002Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _   "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _   phkResult As Long) As LongPrivate 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 LongPrivate 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
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
inf
Properties

Article ID: 316752 - Last Review: 10/26/2013 18:11:00 - Revision: 5.0

  • Microsoft Access 2002 Standard Edition
  • kbnosurvey kbarchive kbhowtomaster KB316752
Feedback