如何阻止Access移除QueryDef中列名的方括号（SQL Server迁移场景）

问题核心 #

Access的SQL解析引擎会自动简化非必要方括号，但列名Current属于SQL Server保留字，SSMA必须依赖方括号才能正确识别，避免迁移时语法报错。手动修改保存能保留方括号，但200+查询批量处理必须用脚本解决。

可行解决方案 #

1. 用DAO修改QueryDef时临时切换ODBC连接（绕过自动格式化） #

Access对本地查询会自动格式化SQL，但标记为ODBC查询时不会。可以临时修改连接属性，赋值SQL后再复原：

Sub PreserveReservedWordBrackets()
    Dim qd As DAO.QueryDef
    Dim originalConnect As String
    Dim modifiedSQL As String

    For Each qd In CurrentDb.QueryDefs
        ' 跳过系统自动生成的查询
        If Left(qd.Name, 1) <> "~" And Left(qd.Name, 4) <> "MSys" Then
            modifiedSQL = qd.SQL
            ' 批量替换保留字Current，确保带方括号
            modifiedSQL = Replace(modifiedSQL, ".Current", ".[Current]")
            modifiedSQL = Replace(modifiedSQL, " Current ", " [Current] ")

            ' 临时标记为ODBC查询，阻止自动格式化
            originalConnect = qd.Connect
            qd.Connect = "ODBC;"
            
            qd.SQL = modifiedSQL
            ' 恢复原连接属性并强制保存
            qd.Connect = originalConnect
            qd.Close
        End If
    Next qd
    Set qd = Nothing
    MsgBox "批量处理完成！"
End Sub

2. 用ADOX直接操作底层SQL（彻底绕过Access格式化） #

ADOX组件可以直接读写查询的SQL文本，不会触发Access的自动格式化规则：

Sub UseADOXForBracketPreservation()
    Dim cat As New ADOX.Catalog
    Dim cmd As ADODB.Command
    Dim modifiedSQL As String

    Set cat.ActiveConnection = CurrentProject.Connection

    For Each cmd In cat.Views
        If Left(cmd.Name, 1) <> "~" And Left(cmd.Name, 4) <> "MSys" Then
            modifiedSQL = cmd.CommandText
            modifiedSQL = Replace(modifiedSQL, ".Current", ".[Current]")
            modifiedSQL = Replace(modifiedSQL, " Current ", " [Current] ")
            ' 直接赋值，ADOX不会自动移除方括号
            cmd.CommandText = modifiedSQL
        End If
    Next cmd

    Set cmd = Nothing
    Set cat = Nothing
    MsgBox "处理完成！"
End Sub

注意：使用前需在VBA编辑器的「工具」→「引用」中勾选Microsoft ADO Ext. 6.0 for DDL and Security组件。

3. 导出SQL文本批量修改后重新导入 #

如果上述脚本仍有问题，可通过文本批量替换后导入：

步骤1：导出所有查询SQL到本地文件夹

Sub ExportAllQuerySQLs()
    Dim qd As DAO.QueryDef
    Dim fso As Object
    Dim ts As Object
    Dim exportFolder As String

    exportFolder = CurrentProject.Path & "\QuerySQL_Backup\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(exportFolder) Then fso.CreateFolder exportFolder

    For Each qd In CurrentDb.QueryDefs
        If Left(qd.Name, 1) <> "~" And Left(qd.Name, 4) <> "MSys" Then
            Set ts = fso.CreateTextFile(exportFolder & qd.Name & ".sql", True)
            ts.Write qd.SQL
            ts.Close
        End If
    Next qd

    Set ts = Nothing
    Set fso = Nothing
    Set qd = Nothing
    MsgBox "SQL导出完成！"
End Sub

步骤2：批量替换保留字

用Notepad++等工具打开导出的所有SQL文件，批量替换.Current为.[Current]、Current为[Current]。

步骤3：导入修改后的SQL替换原查询

Sub ImportModifiedQuerySQLs()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim importFolder As String
    Dim qd As DAO.QueryDef
    Dim sqlContent As String

    importFolder = CurrentProject.Path & "\QuerySQL_Backup\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(importFolder)

    For Each file In folder.Files
        If LCase(fso.GetExtensionName(file.Name)) = "sql" Then
            ' 删除原查询（如果存在）
            On Error Resume Next
            CurrentDb.QueryDefs.Delete Left(file.Name, Len(file.Name) - 4)
            On Error GoTo 0

            ' 读取修改后的SQL文本
            Set ts = fso.OpenTextFile(file.Path, 1)
            sqlContent = ts.ReadAll
            ts.Close

            ' 创建新查询
            Set qd = CurrentDb.CreateQueryDef(Left(file.Name, Len(file.Name) - 4), sqlContent)
            qd.Close
        End If
    Next file

    Set ts = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
    Set qd = Nothing
    MsgBox "SQL导入完成！"
End Sub

验证方式 #

处理完成后不要打开查询设计视图，直接通过VBA查看QueryDef.SQL属性，或用SSMA直接扫描数据库，确认方括号已保留且SSMA能正常识别迁移。

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