各種のデータベースにアクセスするまとめ

'------Access-----------------
Dim AccessConnection As String
Dim AccessFileFullPath As String
AccessFileFullPath = Environ("UserProfile") & "¥OneDrive¥VBA¥SampleData.accdb"

AccessConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & AccessFileFullPath & ";" & _
                                "Persist Security Info=False;"

'------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;"

'------Excel-----------------
Dim ExcelConnection As String
Dim ExcelFileFullPath As String
ExcelFileFullPath = Environ("UserProfile") & "¥OneDrive¥VBA¥個人情報5000件.xlsx"

'ダブルクオートに注意
ExcelConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ExcelFileFullPath & ";" & _
                            "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

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

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

Option Explicit

Sub ConnectionStrings()
'各種のデータベースにアクセスするまとめ
'MicsosoftActive X Data Objects Library 6.1がearly bindingしてあるものとして

'------Access-----------------
Dim AccessConnection As String
Dim AccessFileFullPath As String
AccessFileFullPath = Environ("UserProfile") & "¥OneDrive¥VBA¥SampleData.accdb"

AccessConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & AccessFileFullPath & ";" & _
                                "Persist Security Info=False;"

'------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;"

'------Excel-----------------
Dim ExcelConnection As String
Dim ExcelFileFullPath As String
ExcelFileFullPath = Environ("UserProfile") & "¥OneDrive¥VBA¥個人情報5000件.xlsx"

'ダブルクオートに注意
ExcelConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ExcelFileFullPath & ";" & _
                            "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

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

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

'------Accessファイルを開く-----------------
  adoConn.Open AccessConnection 'Accessファイルを開く"
    Debug.Print "Access Connection Opened"

'------AccessデータにSELECTで接続する-----------------
Dim AccessSQLString As String
AccessSQLString = "SELECT * FROM 販売管理"

'CursorLocation,CursorTypeに注意
With adoRecSet
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open AccessSQLString, adoConn
    Debug.Print "Record Count is = " & .RecordCount
    .Close
        Debug.Print "Access RecordSet Closed"
End With

'------Accessファイルを閉じる-----------------
adoConn.Close
    Debug.Print "Access Connection Closed"

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

'------SQLSeverデータにSELECTで接続する-----------------
Dim SQLServerSQLString As String
SQLServerSQLString = "SELECT * FROM dbo.actor;"

'CursorLocation,CursorTypeに注意
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"

'------Excelを開く-----------------
  adoConn.Open ExcelConnection 'Excelを開く"
    Debug.Print "Excel Connection Opened"

'------SQLSeverデータにSELECTで接続する-----------------
Dim ExcelSQLString As String

'Excelに接続するときは、このようにシート名をセットすること
ExcelSQLString = "SELECT * FROM [個人情報$];"
With adoRecSet
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open ExcelSQLString, adoConn
    Debug.Print "Record Count is = " & .RecordCount
    .Close
        Debug.Print "Excel RecordSet Closed"
End With


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

Set adoRecSet = Nothing
Set adoConn = Nothing

End Sub