By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,313 Members | 1,113 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Methods of Connecting to SQL Server

ADezii
Expert 5K+
P: 8,669
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
Sep 2 '07 #1
Share this Article
Share on Google+