Methods of Connecting to SQL Server

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
   
   Expand|Select|Wrap|Line Numbers

   1. 'Method 1 - Connection information as an Argument to the Open Method of the Connection Object
   2. Dim cnn As ADODB.Connection
   3. Set cnn = New ADODB.Connection
   4.  
   5. cnn.Open "Provider=SQLOLEDB;Data Source=(local);Database=pubs;User ID=ADezii;Password=r16G37P99J"
   6.  
   7. Debug.Print cnn.ConnectionString    'Test for a valid Connection
   8.  
   9. cnn.Close
   10. Set cnn = Nothing
   Expand|Select|Wrap|Line Numbers

   1. 'Method 2 - Use the ConnectionString Property of the Connection Object
   2. Dim cnn As ADODB.Connection
   3. Set cnn = New ADODB.Connection
   4.  
   5. cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Database=pubs;User ID=ADezii;Password=r16G37P99J"
   6. cnn.Open
   7.  
   8. Debug.Print cnn.ConnectionString    'Test for a valid Connection
   9.  
   10. cnn.Close
   11. Set cnn = Nothing
   Expand|Select|Wrap|Line Numbers

   1. 'Method 3 - Set Properties of the Connection Object
   2. Dim cnn As ADODB.Connection
   3. Set cnn = New ADODB.Connection
   4.  
   5. With cnn
   6.   .Provider = "SQLOLEDB"
   7.   .Properties("Data Source") = "(local)"
   8.   .Properties("Initial Catalog") = "pubs"
   9.   .Properties("User ID") = "ADezii"
   10.   .Properties("Password") = "r16G37P99J"
   11.     .Open
   12. End With
   13.  
   14. Debug.Print cnn.ConnectionString    'Test for a valid Connection
   15.  
   16. cnn.Close
   17. Set cnn = Nothing