SQLServerにアクセスするまとめ

Option Explicit

Sub ConnectToSQLServer()
'SQLServerにアクセスするまとめ
'MicsosoftActive X Data Objects Library 6.1がearly bindingしてあるものとして

'------SQLSever-----------------
Dim SQLServerConnection As String
Dim MyServerName As String
MyServerName = "MYSURFACE"

Dim myDataBaseName As String
myDataBaseName = "Movies"

SQLServerConnection = "Provider=MSOLEDBSQL;" & _
                                    "Server=" & MyServerName & ";" & _
                                    "Database=" & myDataBaseName & ";" & _
                                    "Trusted_Connection=yes;"

Dim adoConn As ADODB.Connection
Dim adoRecSet As ADODB.Recordset

Set adoConn = New ADODB.Connection
Set adoRecSet = New ADODB.Recordset

'------SQLSeverを開く-----------------
  adoConn.Open SQLServerConnection 'SQLSeverを開く"
    Debug.Print "SQLSever Connection Opened"

'------SQLSeverデータにSELECTで接続する-----------------
Dim SQLServerSQLString As String
SQLServerSQLString = "SELECT * FROM dbo.actor;"
With adoRecSet
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open SQLServerSQLString, adoConn
    Debug.Print "Record Count is = " & .RecordCount
    .Close
        Debug.Print "SQLServer RecordSet Closed"
End With

'------SQLSeverを閉じる-----------------
adoConn.Close
    Debug.Print "SQLSever Connection Closed"

Set adoRecSet = Nothing
Set adoConn = Nothing

End Sub