VBA/Excel/Access/Word/Access/SQLServer

Connecting to an SQL Server Database

 
Sub ConnectToSQL_SQLOLEDB()
   Dim conn As ADODB.Connection
   Set conn = New ADODB.Connection
   With conn
      .Provider = "SQLOLEDB"
      .ConnectionString = "Data Source=mySource;" & _
          "database=yourDatabase; UserId=sa; Password=;"
      .Open
   End With
   If conn.State = adStateOpen Then
      Debug.Print "Connection was established."
   End If
   conn.Close
   Set conn = Nothing
End Sub

Connecting to an SQL Server Using a DSN-less Connection

 
Sub Connect_ToSQLServer()
   Dim conn As ADODB.Connection
   Set conn = New ADODB.Connection
   With conn
      " DSN-less connection using the ODBC driver
      " (modify the data source information below)
      .Open "Driver={SQL Server};" & _
          "Server=192.168.1.100;" & _
          "UID=myId;" & _
          "PWD=myPassword;" & _
          "Database=SupportDb"
      .Close
   End With
   Set conn = Nothing
End Sub

Creating a SQL Server Connection Object with Integrated Security

 
Sub CreateConnectionIntegrated()
    "Declare and instantiate the connection
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    "Open the connection
    cnn.Open "Provider=SQLOLEDB.1;" & _
            "Data Source=(local); Initial Catalog=NorthWind;" & _
            "Integrated Security=SSPI"
    "Close the connection
    cnn.Close
    "Destroy the connection object
    Set cnn = Nothing
End Sub

Creating a SQL Server Connection Object with SQL Server Security

 
Sub CreateConnection()
    "Declare and instantiate the connection
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    "Open the connection
    cnn.Open "Provider=SQLOLEDB.1;" & _
            "Data Source=(local); Initial Catalog=NorthWind;" & _
            "User ID=sa;PWD="
    "Close the connection
    cnn.Close
    "Destroy the connection object
    Set cnn = Nothing
End Sub

DSN-less connection using the SQLOLEDB provider

 
Sub Connect_ToSQLServer2()
   Dim conn As ADODB.Connection
   Set conn = New ADODB.Connection
   With conn
      .Open "Provider=SQLOLEDB;" & _
          "DataSource=mySource;" & _
          "Initial Catalog=myDb;" & _
          "UID=myId; Password=myPassword;"
      .Close
   End With
   Set conn = Nothing
End Sub

Transfer SQLServer database

 
Sub TestTransferSQLDatabase()
    DoCmd.TransferSQLDatabase _
        Server:="ServerName", _
        Database:="Pubs", _
        UseTrustedConnection:=True, _
        TransferCopyData:=False
End Sub