【Excel VBA】ADOでAccessMDBを操作するコードスニペット（備忘録）

MDBを作成する、テーブルをCREATEする、データをインサートする、データをSELECTするといった基本的な操作をするコードスニペット（忘備録）です。

Sub ADOMdbTest()
    
    Dim sMDBPath    As String
    Dim sConnectStr As String
    Dim adoCn       As ADODB.Connection
    'Dim rs            As ADODB.Recordset
    Dim strSQL      As String
    
    Dim i       As Integer
    
    sMDBPath = Environ$("TEMP") & "\test.mdb"
    sConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sMDBPath & ";"

    If Len(Dir(sMDBPath)) Then
    Else
        With New ADOX.Catalog
            .Create sConnectStr
        End With
        
        '初期データを作成
        With New ADODB.Connection
            .Open sConnectStr
            strSQL = ""
            strSQL = strSQL & " CREATE TABLE TEST ("
            strSQL = strSQL & "     ID COUNTER PRIMARY KEY , "
            strSQL = strSQL & "     TXT VARCHAR(32) "
            strSQL = strSQL & ")"
            .Execute strSQL
            For i = 1 To 10
                strSQL = ""
                strSQL = strSQL & " INSERT INTO TEST ("
                strSQL = strSQL & "     TXT "
                strSQL = strSQL & " ) VALUES ("
                strSQL = strSQL & "     'TEST_" & i & " '"
                strSQL = strSQL & " )"
                .Execute strSQL
            Next
            .Close
        End With
        
    End If
    
    Set adoCn = New ADODB.Connection
    adoCn.Open sConnectStr
    
    
    With New ADODB.Recordset
        strSQL = ""
        strSQL = strSQL & " SELECT *"
        strSQL = strSQL & "   FROM TEST"
        strSQL = strSQL & "  WHERE ID>6"
        strSQL = strSQL & "  ORDER BY ID DESC"
        .Open strSQL, adoCn, adOpenKeyset, adLockOptimistic
        Do While Not .EOF
            Debug.Print Now, !txt
            .MoveNext
        Loop
    End With
    
    adoCn.Close
    Set adoCn = Nothing
    
    'DBを消す場合
    Kill sMDBPath
    
End