How to programmatically change the connection of a Microsoft Access project (306881)

MORE INFORMATION

The easiest way to change the connection of an ADP is to do it manually. You can access the current project's connection properties as follows: on the File menu, click Connection. The dialog box that appears gives you options to change the server name and security modes, among other properties.

However, sometimes, you might want to automate the process. You may want users to connect to a different SQL server for a specific section of your application, or you may want to control the connection process upon startup so that you can look for errors and handle them accordingly, perhaps by routing to another server.

The following steps use a function that demonstrates how to change the existing connection of an ADP by using the Access object model. The function returns True if it succeeds and False if it does not succeed.

1. Start Access, and then open the sample Access project NorthwindCS.adp.
2. In the Database window, click Modules under Objects, and then click New to open a new module.
3. Add the following code to the new module, and then save the module:

   Function ChangeADPConnection(strServerName As String, strDBName As _
      String, Optional strUN As String, Optional strPW As String) As Boolean
   Dim strConnect As String
   On Error GoTo EH:
   Application.CurrentProject.CloseConnection
   'The Provider, Data Source, and Initial Catalog arguments are required.
   strConnect = "Provider=SQLOLEDB.1" & _
   ";Data Source=" & strServerName & _
   ";Initial Catalog=" & strDBName
   If strUN <> "" Then
       strConnect = strConnect & ";user id=" & strUN
       If strPW <> "" Then
           strConnect = strConnect & ";password=" & strPW
       End If
   Else  'Try to use integrated security if no username is supplied.
       strConnect = strConnect & ";integrated security=SSPI"
   End If
   Application.CurrentProject.OpenConnection strConnect
   ChangeADPConnection = True
   Exit Function
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
   ChangeADPConnection = False
   End Function
   					

4. In the Database window, click Forms under Objects, click New, and then click OK to open a new form in Design view.
5. Add a command button to the form.
6. Set the OnClick property of the command button to the following event procedure:

   Dim bCheckConnection As Boolean
      'You must specify the correct parameters for your following server.
      'username and password parameters are optional.
   bCheckConnection=ChangeADPConnection("ServerName","DBName","UserName","PW")
   MsgBox bCheckConnection
   					

7. Close the Visual Basic environment to return to the form.
8. Save the form, and then switch the form to Form view.
9. Click the command button to run the underlying code.
   
   You will receive a message box that says True if you supplied correct parameters for a connection in step 6.
   
   -OR-
   
   If you did not supply the correct parameters, you receive the error message. When you click OK to the error message, you get the message box that displays False .