This is a fairly basic question and there must be a million examples posted.
Here is an example which creates a connection where the SQL Server has been
configured to accept connections using Windows Integrated security.  If this
is not the case, look at the following for variations:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

Private Sub cmdTest_Click()

   On Error GoTo Err_Handler

   Dim cnn As ADODB.Connection
   Dim strConn As String

   strConn = "Provider=sqloledb;" & _
             "Data Source=MyServer;" & _
             "Initial Catalog=MyDatabase;" & _
             "Integrated Security=SSPI"

   Set cnn = New ADODB.Connection

   cnn.Open strConn

   MsgBox cnn.ConnectionString, vbInformation

Exit_Handler:

   If Not cnn Is Nothing Then
       If cnn.State <> adStateClosed Then
           cnn.Close
       End If
       Set cnn = Nothing
   End If

   Exit Sub

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
   Resume Exit_Handler

End Sub

Check out www.connectionstrings.com
-Tom.