Option Explicit

'テーブル情報の取得
Sub showTableInfo()
    
    Const FILE_NAME_DB As String = "Score.accdb"
'    Const FILE_NAME_DB As String = "Score.mdb"
    Const PROVIDER_ACCESS As String = "Microsoft.Ace.OLEDB.12.0"
    
    Dim i As Long
    Dim sProvider As String
    Dim sDataSource As String
    Dim sConnection As String
    Dim adodbConn As New ADODB.Connection
    Dim adoxCat As New ADOX.Catalog
    Dim adoxTable As ADOX.Table
    Dim adoxCol As New ADOX.Column
    
    '情報収集
    sProvider = "Provider=" & PROVIDER_ACCESS & ";"
    sDataSource = "Data Source=" & ThisWorkbook.Path & "\" & FILE_NAME_DB & ";"
    sConnection = sProvider & sDataSource
    
    'データベースへ接続
    Call adodbConn.Open(sProvider & sDataSource)
    
    'テーブル情報の取得
    adoxCat.ActiveConnection = adodbConn
    For Each adoxTable In adoxCat.Tables
        If adoxTable.Type = "TABLE" Then
            Debug.Print adoxTable.Name
            For Each adoxCol In adoxTable.Columns
                Debug.Print vbTab & adoxCol.Name
            Next
        End If
    Next
    
    'データベースを閉じる
    Call adodbConn.Close
    
    'Connectionを開放(破棄)
    Set adodbConn = Nothing
    
End Sub

'テーブルの作成
Sub createTable()
    
    Const FILE_NAME_DB As String = "Score.accdb"
'    Const FILE_NAME_DB As String = "Score.mdb"
    Const PROVIDER_ACCESS As String = "Microsoft.Ace.OLEDB.12.0"
    
    Dim i As Long
    Dim sProvider As String
    Dim sDataSource As String
    Dim sConnection As String
    Dim adodbConn As New ADODB.Connection
    Dim adoxCat As New ADOX.Catalog
    Dim adoxTable As ADOX.Table
    
    '情報収集
    sProvider = "Provider=" & PROVIDER_ACCESS & ";"
    sDataSource = "Data Source=" & ThisWorkbook.Path & "\" & FILE_NAME_DB & ";"
    sConnection = sProvider & sDataSource
    
    'データベースへ接続
    Call adodbConn.Open(sProvider & sDataSource)
    
    'テーブルの作成
    adoxCat.ActiveConnection = adodbConn
    Set adoxTable = New ADOX.Table
    adoxTable.Name = "tblNew"
    Set adoxTable.ParentCatalog = adoxCat
    
    'カタログに追加
    Call adoxCat.Tables.Append(adoxTable)
    
    'データベースを閉じる
    Call adodbConn.Close
    
    'Connectionを開放(破棄)
    Set adodbConn = Nothing
    
End Sub

'フィールドの追加
Sub addColumn()
    
    Const FILE_NAME_DB As String = "Score.accdb"
'    Const FILE_NAME_DB As String = "Score.mdb"
    Const PROVIDER_ACCESS As String = "Microsoft.Ace.OLEDB.12.0"
    
    Dim i As Long
    Dim sProvider As String
    Dim sDataSource As String
    Dim sConnection As String
    Dim adodbConn As New ADODB.Connection
    Dim adoxCat As New ADOX.Catalog
    Dim adoxTable As ADOX.Table
    
    '情報収集
    sProvider = "Provider=" & PROVIDER_ACCESS & ";"
    sDataSource = "Data Source=" & ThisWorkbook.Path & "\" & FILE_NAME_DB & ";"
    sConnection = sProvider & sDataSource
    
    'データベースへ接続
    Call adodbConn.Open(sProvider & sDataSource)
    
    'フィールドの追加
    adoxCat.ActiveConnection = adodbConn
    Set adoxTable = adoxCat.Tables.Item("tblNew")
    With adoxTable.Columns
        Call .Append("ID", adInteger)
        Call .Append("sName", adVarWChar)
        Call .Append("sMemo", adLongVarWChar)
    End With
    
    'データベースを閉じる
    Call adodbConn.Close
    
    'Connectionを開放(破棄)
    Set adodbConn = Nothing
    
End Sub

'フィールドの削除
Sub deleteColumn()
    
    Const FILE_NAME_DB As String = "Score.accdb"
'    Const FILE_NAME_DB As String = "Score.mdb"
    Const PROVIDER_ACCESS As String = "Microsoft.Ace.OLEDB.12.0"
    
    Dim i As Long
    Dim sProvider As String
    Dim sDataSource As String
    Dim sConnection As String
    Dim adodbConn As New ADODB.Connection
    Dim adoxCat As New ADOX.Catalog
    Dim adoxTable As ADOX.Table
    
    '情報収集
    sProvider = "Provider=" & PROVIDER_ACCESS & ";"
    sDataSource = "Data Source=" & ThisWorkbook.Path & "\" & FILE_NAME_DB & ";"
    sConnection = sProvider & sDataSource
    
    'データベースへ接続
    Call adodbConn.Open(sProvider & sDataSource)
    
    'フィールドの追加
    adoxCat.ActiveConnection = adodbConn
    Set adoxTable = adoxCat.Tables.Item("tblNew")
    Call adoxTable.Columns.Delete("sMemo")
    
    
    'データベースを閉じる
    Call adodbConn.Close
    
    'Connectionを開放(破棄)
    Set adodbConn = Nothing
    
End Sub

'テーブルの削除
Sub deleteTable()
    
    Const FILE_NAME_DB As String = "Score.accdb"
'    Const FILE_NAME_DB As String = "Score.mdb"
    Const PROVIDER_ACCESS As String = "Microsoft.Ace.OLEDB.12.0"
    
    Dim i As Long
    Dim sProvider As String
    Dim sDataSource As String
    Dim sConnection As String
    Dim adodbConn As New ADODB.Connection
    Dim adoxCat As New ADOX.Catalog
    
    '情報収集
    sProvider = "Provider=" & PROVIDER_ACCESS & ";"
    sDataSource = "Data Source=" & ThisWorkbook.Path & "\" & FILE_NAME_DB & ";"
    sConnection = sProvider & sDataSource
    
    'データベースへ接続
    Call adodbConn.Open(sProvider & sDataSource)
    
    'テーブルの削除
    adoxCat.ActiveConnection = adodbConn
    Call adoxCat.Tables.Delete("tblNew")
    
    'データベースを閉じる
    Call adodbConn.Close
    
    'Connectionを開放(破棄)
    Set adodbConn = Nothing
    
End Sub