VB.NET中复制Access数据库表结构，实现无数据改动的库更新

基于你已经实现的新旧库差异检测（ComboBox1展示缺失表、ComboBox2展示已有表的缺失字段及数据类型），以下是具体的更新实现逻辑和代码：

1. 复制缺失表的完整结构到旧库 #

针对ComboBox1中的每个缺失表，从新数据库读取完整表结构（含字段定义、主键、约束等），在旧库中创建相同结构的空表：

• 使用OleDbConnection分别连接新旧库
• 通过GetSchema("Columns")获取新表的字段详情，通过GetSchema("Indexes")获取主键等索引信息
• 拼接Access兼容的CREATE TABLE语句，在旧库执行创建操作

示例代码片段（创建缺失表） #

' 假设旧库连接字符串为 oldConnStr，新库为 newConnStr
For Each tableName As String In ComboBox1.Items
    ' 从新库获取表字段信息
    Dim newConn As New OleDbConnection(newConnStr)
    newConn.Open()
    Dim dtColumns As DataTable = newConn.GetSchema("Columns", New String() {Nothing, Nothing, tableName})
    Dim dtIndexes As DataTable = newConn.GetSchema("Indexes", New String() {Nothing, Nothing, tableName})
    newConn.Close()

    ' 拼接CREATE TABLE语句
    Dim createSql As String = $"CREATE TABLE [{tableName}] ("
    Dim primaryKeys As New List(Of String)()

    ' 处理字段定义
    For Each row As DataRow In dtColumns.Rows
        Dim colName As String = row("COLUMN_NAME").ToString()
        Dim dataType As String = MapAccessDataType(row("DATA_TYPE").ToString(), row("CHARACTER_MAXIMUM_LENGTH"))
        createSql += $"[{colName}] {dataType}, "

        ' 收集主键字段
        For Each idxRow As DataRow In dtIndexes.Rows
            If idxRow("PRIMARY_KEY").ToString() = "True" AndAlso idxRow("COLUMN_NAME").ToString() = colName Then
                primaryKeys.Add(colName)
            End If
        Next
    Next

    ' 添加主键约束
    If primaryKeys.Count > 0 Then
        createSql += $"PRIMARY KEY ({String.Join(", ", primaryKeys.Select(Function(k) $"[{k}]"))})"
    Else
        ' 移除末尾多余的逗号
        createSql = createSql.TrimEnd(", "c)
    End If
    createSql += ")"

    ' 在旧库执行创建
    Dim oldConn As New OleDbConnection(oldConnStr)
    oldConn.Open()
    Dim cmd As New OleDbCommand(createSql, oldConn)
    cmd.ExecuteNonQuery()
    oldConn.Close()
Next

' 辅助函数：映射OleDb数据类型到Access SQL类型
Private Function MapAccessDataType(oleDbType As String, maxLength As Object) As String
    Select Case oleDbType
        Case "VarWChar"
            If maxLength Is DBNull.Value Then Return "TEXT" Else Return $"VARCHAR({maxLength})"
        Case "Integer"
            Return "INT"
        Case "Double"
            Return "DOUBLE"
        Case "DateTime"
            Return "DATETIME"
        Case "Boolean"
            Return "BIT"
        Case Else
            Return "TEXT"
    End Select
End Function

2. 为已有表添加缺失字段 #

针对ComboBox2中的每个缺失字段（建议格式为表名.字段名(数据类型)，方便拆分），执行ALTER TABLE语句为旧库对应表添加字段：

• 拆分字段信息，提取目标表名、字段名和数据类型
• 拼接ALTER TABLE [表名] ADD COLUMN [字段名] 数据类型语句，在旧库执行

示例代码片段（添加缺失字段） #

For Each fieldInfo As String In ComboBox2.Items
    ' 假设ComboBox2的项格式为：Customers.Email(VARCHAR(50))
    Dim parts() As String = fieldInfo.Split({"(", ")"}, StringSplitOptions.RemoveEmptyEntries)
    Dim tableAndField() As String = parts(0).Split("."c)
    Dim tableName As String = tableAndField(0)
    Dim fieldName As String = tableAndField(1)
    Dim dataType As String = parts(1)

    ' 拼接ALTER TABLE语句
    Dim alterSql As String = $"ALTER TABLE [{tableName}] ADD COLUMN [{fieldName}] {dataType}"

    ' 在旧库执行添加
    Dim oldConn As New OleDbConnection(oldConnStr)
    oldConn.Open()
    Dim cmd As New OleDbCommand(alterSql, oldConn)
    cmd.ExecuteNonQuery()
    oldConn.Close()
Next

关键注意事项 #

• 数据安全：执行前建议自动备份旧库，避免操作失误导致数据丢失
• 约束兼容：复制表结构时需确保Access支持的约束（如主键、默认值）正确迁移，部分OleDb元数据可能需要额外处理
• 异常处理：在代码中添加Try-Catch块，捕获OleDb异常并记录日志，避免更新中断

内容的提问来源于stack exchange，提问作者Mahmoud Hiyani