Article ID: 316752
Article Last Modified on 8/11/2004
APPLIES TO
- Microsoft Access 2002 Standard Edition
This article was previously published under Q316752
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
- Start Microsoft Access 2002, and then open any database.
- Create a new table that has two fields, one named Database and the other Description. Make the data type for both fields Text.
- Save the table as tblDatabases.
- Open the tblDatabases table, and then add data to the Databases and Descriptions fields.
- Create a new form.
- 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" - Add a command button to the form. Change the Caption property of the command button to Change DSN.
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!"
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
- Save the form as frmChangeDSN.
- Open the frmChangeDSN form in Form view.
- Select a database name from the combo box, and then click Change DSN.
- Note that you receive a message box that confirms the database and DSN names. Click OK.
- On the File menu, point to Get External Data, and then click Link.
- Change the Files of Type box to ODBC Databases().
- On the Machine Data Source tab, click CodeTestSQL, and then click OK.
- Note that the tables that are listed are from the database that you selected on the form.
- Click Cancel.
- Return to the frmChangeDSN form, select a different database, and then click the command button.
- 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
Additional query words: inf
Keywords: kbhowtomaster KB316752