Methods of Connecting to SQL Server

[ADezii]

At some point in time, you may need to make a Connection to an External Data Source residing in a SQL Server Database. There are basically 3 distinct Methods for creating this Connection. I will briefly outline each Method below, list several assumptions made concerning the Connections, then demonstrate the proper coding techniques needed to implement each Method. If there are any questions, please feel free to ask.

1. Methods of Connecting to SQL Server
   1. Supply Connection information as an Argument to the Open Method of the Connection Object
   2. Use the ConnectionString Property of the Connection Object
   3. Set the Properties of the Connection Object
2. Assumptions
   
   1. Data Source = "(local)"
   2. Database/Initial Catalogue = "pubs"
   3. User Id = "ADezii"
   4. Password = "r16G37P99J"
3. Code Implementations
   

   'Method 1 - Connection information as an Argument to the Open Method of the Connection Object
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
    
   cnn.Open "Provider=SQLOLEDB;Data Source=(local);Database=pubs;User ID=ADezii;Password=r16G37P99J"
    
   Debug.Print cnn.ConnectionString    'Test for a valid Connection
    
   cnn.Close
   Set cnn = Nothing

   'Method 2 - Use the ConnectionString Property of the Connection Object
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
    
   cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Database=pubs;User ID=ADezii;Password=r16G37P99J"
   cnn.Open
    
   Debug.Print cnn.ConnectionString    'Test for a valid Connection
    
   cnn.Close
   Set cnn = Nothing

   'Method 3 - Set Properties of the Connection Object
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
    
   With cnn
     .Provider = "SQLOLEDB"
     .Properties("Data Source") = "(local)"
     .Properties("Initial Catalog") = "pubs"
     .Properties("User ID") = "ADezii"
     .Properties("Password") = "r16G37P99J"
       .Open
   End With
    
   Debug.Print cnn.ConnectionString    'Test for a valid Connection
    
   cnn.Close
   Set cnn = Nothing