VBA/Excel/Access/Word/Access/SQLServer
Содержание
- 1 Connecting to an SQL Server Database
- 2 Connecting to an SQL Server Using a DSN-less Connection
- 3 Creating a SQL Server Connection Object with Integrated Security
- 4 Creating a SQL Server Connection Object with SQL Server Security
- 5 DSN-less connection using the SQLOLEDB provider
- 6 Transfer SQLServer database
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>