ADOX를 이용한 MDB Control
' "구성요소 -> 참조"에서 "Microsoft ADO Ext. 2.7 for DDL and Security"를 선택해야 합니다.
1. 깡통 DB 파일 만들기
다음 코드는 create Method를 이용해서 MS Jet database를 어떻게 만드는지를 보여준다.

Attribute VB_Name = "Create"
Option Explicit

' BeginCreateDatabseVB
Sub CreateDatabase()
    On Error GoTo CreateDatabaseError
    
    Dim cat As New ADOX.Catalog

    ' Jet.OLEDB.4.0  : MDB for Access2000
    ' Jet.OLEDB.3.51 : MDB for Access97
    cat.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='c:\new.mdb'"

    'Clean up
    Set cat = Nothing
    Exit Sub
    
CreateDatabaseError:
    Set cat = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
' EndCreateDatabaseVB
2. 테이블 생성
' BeginCreateTableVB
Sub Main()
    On Error GoTo CreateTableError
    
    Dim tbl As New Table
    Dim cat As New ADOX.Catalog
    
    ' Open the Catalog.
    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"
    
    tbl.Name = "MyTable"
    tbl.Columns.Append "Column1", adInteger
    tbl.Columns.Append "Column2", adInteger
    tbl.Columns.Append "Column3", adVarWChar, 50
    cat.Tables.Append tbl
    Debug.Print "Table 'MyTable' is added."
    
    'Delete the table as this is a demonstration.
    cat.Tables.Delete tbl.Name
    Debug.Print "Table 'MyTable' is deleted."
    
    'Clean up
    Set cat.ActiveConnection = Nothing
    Set cat = Nothing
    Set tbl = Nothing
    Exit Sub
    
CreateTableError:

    Set cat = Nothing
    Set tbl = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
' EndCreateTableVB
3. 테이블 생성 및 인덱스 추가
' BeignCreateIndexVB
Sub Main()
    On Error GoTo CreateIndexError

    Dim tbl As New Table
    Dim idx As New ADOX.Index
    Dim cat As New ADOX.Catalog
    
    'Open the catalog.
    ' Open the Catalog.
    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"
    
    ' Define the table and append it to the catalog
    tbl.Name = "MyTable"
    tbl.Columns.Append "Column1", adInteger
    tbl.Columns.Append "Column2", adInteger
    tbl.Columns.Append "Column3", adVarWChar, 50
    cat.Tables.Append tbl
    Debug.Print "Table 'MyTable' is added."
    
    ' Define a multi-column index
    idx.Name = "multicolidx"
    idx.Columns.Append "Column1"
    idx.Columns.Append "Column2"
    
    ' Append the index to the table
    tbl.Indexes.Append idx
    Debug.Print "The index is appended to table 'MyTable'."
    
    'Delete the table as this is a demonstration
    cat.Tables.Delete tbl.Name
    Debug.Print "Table 'MyTable' is deleted."
    
    'Clean up
    Set cat.ActiveConnection = Nothing
    Set cat = Nothing
    Set tbl = Nothing
    Set idx = Nothing
    Exit Sub
    
CreateIndexError:
    Set cat = Nothing
    Set tbl = Nothing
    Set idx = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
' EndCreateIndexVB
4. Foreign Key 생성
' BeginCreateKeyVB
Sub Main()
    On Error GoTo CreateKeyError

    Dim kyForeign As New ADOX.Key
    Dim cat As New ADOX.Catalog

    ' Connect the catalog
    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"
    
    ' Define the foreign key
    kyForeign.Name = "CustOrder"
    kyForeign.Type = adKeyForeign
    kyForeign.RelatedTable = "Customers"
    kyForeign.Columns.Append "CustomerId"
    kyForeign.Columns("CustomerId").RelatedColumn = "CustomerId"
    kyForeign.UpdateRule = adRICascade
    
    ' Append the foreign key
    cat.Tables("Orders").Keys.Append kyForeign
    
    'Delete the Key as this is a demonstration
    cat.Tables("Orders").Keys.Delete kyForeign.Name
    
    'Clean up
    Set cat.ActiveConnection = Nothing
    Set cat = Nothing
    Set kyForeign = Nothing
    Exit Sub
    
CreateKeyError:
    Set cat = Nothing
    Set kyForeign = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    
End Sub
' EndCreateKeyVB
[출처] : MSDN 자료중에서 ADOX 관련 Example 일부를 정리한 것입니다.

[이 게시물은 오원장님에 의해 2013-02-28 16:41:58 오피스팁에서 이동 됨]