VBA/Excel/Access/Word/Access/SQLServer

Материал из VB Эксперт
Перейти к: навигация, поиск

Connecting to an SQL Server Database

   <source lang="vb">

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

</source>
   
  


Connecting to an SQL Server Using a DSN-less Connection

   <source lang="vb">

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

</source>
   
  


Creating a SQL Server Connection Object with Integrated Security

   <source lang="vb">

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

</source>
   
  


Creating a SQL Server Connection Object with SQL Server Security

   <source lang="vb">

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

</source>
   
  


DSN-less connection using the SQLOLEDB provider

   <source lang="vb">

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

</source>
   
  


Transfer SQLServer database

   <source lang="vb">

Sub TestTransferSQLDatabase()

   DoCmd.TransferSQLDatabase _
       Server:="ServerName", _
       Database:="Pubs", _
       UseTrustedConnection:=True, _
       TransferCopyData:=False

End Sub

</source>