利用ADO和VBA在Access中创建与管理数据库

背景简介

在数据管理和处理领域，能够熟练操作数据库是开发者的必备技能。Microsoft Access作为广泛使用的桌面数据库管理系统，其强大的功能和灵活性使其在小型数据库应用中占据了重要的地位。本篇博客将基于Microsoft Access中的ADOX对象库和VBA（Visual Basic for Applications）编程技术，探讨如何创建和管理数据库，以及如何通过编程方式操作表和字段。

动手操作：创建新表

在本章中，我们首先学习了如何创建一个新的Access数据库表。通过编写VBA代码，并使用ADOX对象库中的Catalog和Table对象，我们能够定义表的结构，包括字段的名称、类型以及大小。例如，创建一个名为 tblFilters 的新表，包含 Id 、 Description 和 Type 三个字段，代码示例如下：

Sub Create_Table()
    Dim cat As ADOX.Catalog
    Dim myTbl As ADOX.Table
    On Error GoTo ErrorHandler
    Set cat = New Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set myTbl = New Table
    With myTbl
        .Name = "tblFilters"
        With .Columns
            .Append "Id", adVarWChar, 10
            .Append "Description", adVarWChar, 255
            .Append "Type", adInteger
        End With
    End With
    cat.Tables.Append myTbl
    Set cat = Nothing
    MsgBox "新表 'tblFilters' 已创建。"
End Sub

此过程不仅展示了创建表的基本步骤，还演示了如何在出现错误时进行处理。

复制表与表结构

在数据库维护中，有时我们需要复制现有表。ADOX通过 Catalog 对象提供了这一功能，使得我们可以使用SQL语句 SELECT...INTO 来创建一个新表，并将原表的数据复制到新表中。例如，复制 Northwind 数据库中的 Customers 表到新表 CustomersCopy 的VBA代码如下：

Sub Copy_Table()
    Dim conn As ADODB.Connection
    Dim strTable As String
    Dim strSQL As String
    On Error GoTo ErrorHandler
    strTable = "Customers"
    strSQL = "SELECT " & strTable & ".* INTO " & strTable & "Copy FROM " & strTable
    Debug.Print strSQL
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & CurrentProject.Path & _
               "\\Northwind.mdb"
    conn.Execute strSQL
    conn.Close
    Set conn = Nothing
    MsgBox "The " & strTable & " table was copied."
End Sub

删除表

在某些情况下，需要从数据库中删除不再需要的表。我们可以通过ADOX的 Catalog 对象访问 Tables 集合，并使用 Delete 方法来删除指定的表。例如，删除名为 tblFilters 的表的VBA代码如下：

Sub Delete_Table(strTblName As String)
    Dim cat As ADOX.Catalog
    On Error GoTo ErrorHandler
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    cat.Tables.Delete strTblName
    Set cat = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Table '" & strTblName & "' cannot be deleted" & vbCrLf & _
           "because it does not exist."
    Resume Next
End Sub

添加和删除字段

除了表的创建和删除，向现有表中添加新字段以及删除字段也是常见的操作。通过访问 ADOX 的 Table 对象的 Columns 集合，我们可以添加新字段，如文本字段 MyNewField ：

Sub Add_NewFields()
    Dim cat As New ADOX.Catalog
    Dim myTbl As New ADOX.Table
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    cat.Tables("tblFilters").Columns.Append "MyNewField", adWChar, 15
    Set cat = Nothing
End Sub

而删除字段时，需要确保该字段不是索引的一部分。例如，删除 tblFilters 表中的 Type 字段：

Sub Delete_Field()
    Dim cat As New ADOX.Catalog
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    cat.Tables("tblFilters").Columns.Delete "Type"
    Set cat = Nothing
End Sub

检索表和字段属性

最后，通过访问ADOX的 Table 对象的 Properties 集合，我们可以检索表的属性。同样地，我们也可以获取字段的属性。例如，列出 tblFilters 表的所有属性：

Sub List_TableProperties()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim pr As ADOX.Property
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables("tblFilters")
    For Each pr In tbl.Properties
        Debug.Print tbl.Name & ": " & pr.Name & "="; pr.Value
    Next pr
    Set cat = Nothing
End Sub

总结与启发

在本章中，我们通过一系列的动手实践深入学习了如何使用ADOX和VBA来创建和管理Microsoft Access数据库。通过这些示例，我们可以感受到编程带来的强大控制能力，以及在处理可能出现的错误时所需的细致考虑。掌握了这些技术，开发者能够更加高效地构建和维护复杂的数据库系统。此外，本章也展示了如何链接外部数据源，这在数据集成和系统互操作性方面具有重要的应用价值。