标签 ADO 下的文章

Access 连接 SQL Server：直通查询 vs 链接表 vs ADO，如何选择？

摘要：当 Access 前端需要连接 SQL Server 后端时，开发者面临三种主流技术方案：链接表（Linked Tables）、直通查询（Pass-Through Queries） 和 ADO 编程。本文从底层原理、性能特征、适用场景三个维度进行深度对比，帮助开发者在实际项目中做出正确的技术选型。

一、技术背景

Access 作为前端开发工具连接 SQL Server 后端，是中小型企业信息化的经典架构。这种"胖客户端"模式相比纯 Web 方案，具有开发效率高、部署简单的优势。

但 Access 与 SQL Server 之间的数据交互存在多种实现路径，不同方案在 网络开销、服务器负载、代码复杂度 上差异显著。

二、三种方案的底层原理

1. 链接表（Linked Tables）

原理：通过 ODBC 驱动在 Access 中创建指向 SQL Server 表的"快捷方式"。Access 的 ACE/Jet 引擎会将用户操作（筛选、排序、更新）转换为 ODBC 调用。

┌──────────────┐      ODBC       ┌──────────────┐
│   Access     │  ←──────────→   │  SQL Server  │
│  (ACE引擎)   │   链接表驱动     │   (T-SQL)    │
└──────────────┘                 └──────────────┘

技术特点：

  • 透明性：开发者可以像操作本地表一样使用 SELECT * FROM tblOrders。
  • 引擎介入：ACE 引擎会"尝试"优化查询，但复杂查询可能被拆解为多次网络往返。
  • 事务支持：受限于 ODBC 驱动的事务隔离级别。

创建方式：

' VBA 代码创建链接表
DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DRIVER={SQL Server};SERVER=192.168.1.100;DATABASE=SalesDB;Trusted_Connection=Yes", _
    acTable, "dbo.Orders", "lnkOrders"

2. 直通查询（Pass-Through Queries）

原理：绕过 ACE 引擎，将 原生 T-SQL 直接发送到 SQL Server 执行，结果集作为只读快照返回。

┌──────────────┐    原生 T-SQL    ┌──────────────┐
│   Access     │  ──────────────→ │  SQL Server  │
│  (仅传递)     │   不经过 ACE     │   (直接执行)  │
└──────────────┘                 └──────────────┘

技术特点：

  • 完全控制：可使用 SQL Server 特有语法（TOP、WITH (NOLOCK)、PIVOT 等）。
  • 只读限制：返回结果集默认不可编辑（除非配合链接表使用）。
  • 存储过程调用：最佳的存储过程执行方式。

创建方式：

-- 在查询设计器中设置 "直通" 属性为 "是"
-- 或通过 VBA 创建
SELECT TOP 100 OrderID, CustomerName, OrderDate
FROM dbo.Orders WITH (NOLOCK)
WHERE OrderDate >= '2025-01-01'
ORDER BY OrderDate DESC

VBA 动态执行：

Public Sub ExecutePassThrough(strSQL As String)
    Dim qdf As DAO.QueryDef
    
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "qryTemp"
    On Error GoTo 0
    
    Set qdf = CurrentDb.CreateQueryDef("qryTemp")
    With qdf
        .Connect = "ODBC;DRIVER={SQL Server};SERVER=192.168.1.100;DATABASE=SalesDB;Trusted_Connection=Yes"
        .SQL = strSQL
        .ReturnsRecords = True  ' 如果是 INSERT/UPDATE/DELETE，设为 False
    End With
    
    ' 绑定到窗体或报表
    Me.RecordSource = "qryTemp"
End Sub

3. ADO 编程（ActiveX Data Objects）

原理：通过 ADO 对象模型（ADODB.Connection、ADODB.Recordset）直接操作 OLE DB 或 ODBC 数据源，完全脱离 Access 的 DAO/ACE 体系。

┌──────────────┐     OLE DB      ┌──────────────┐
│   Access     │  ←──────────→   │  SQL Server  │
│  (ADO对象)   │   直接连接       │   (T-SQL)    │
└──────────────┘                 └──────────────┘

技术特点：

  • 最大灵活性：支持游标类型选择、批量更新、断开式记录集。
  • 可移植性：ADO 代码可直接迁移到 VB6、VBScript、Excel VBA。
  • 代码量大：需要手动管理连接生命周期和错误处理。

典型代码：

Public Function GetOrders(strCustomerID As String) As ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    
    ' 连接字符串
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=192.168.1.100;" & _
                            "Initial Catalog=SalesDB;Integrated Security=SSPI;"
    conn.Open
    
    ' 参数化查询防止 SQL 注入
    strSQL = "SELECT * FROM dbo.Orders WHERE CustomerID = ?"
    
    With rs
        .ActiveConnection = conn
        .Source = strSQL
        .CursorLocation = adUseClient  ' 客户端游标，支持断开连接
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .Open , , , , adCmdText
    End With
    
    ' 断开连接，返回独立记录集
    Set rs.ActiveConnection = Nothing
    conn.Close
    
    Set GetOrders = rs
End Function

三、性能对比测试

以下是在 万级数据量 下的典型场景测试结果（仅供参考，实际因网络环境而异）：

场景链接表直通查询ADO
SELECT 1000 条记录1.2s0.3s0.4s
复杂 JOIN（5表关联）8.5s0.8s0.9s
调用存储过程不支持0.2s0.2s
批量 INSERT 1000 条15s0.5s0.6s
单条记录更新0.1s0.1s0.1s

结论：

  • 简单 CRUD：三者差异不大。
  • 复杂查询/批量操作：直通查询和 ADO 优势明显。
  • 链接表的性能陷阱：多表 JOIN 时，ACE 引擎可能先拉取全表数据到本地再做关联，造成巨大的网络开销。

四、适用场景决策树

                        ┌─────────────────────────┐
                        │  需要连接 SQL Server？   │
                        └───────────┬─────────────┘
                                    │
                    ┌───────────────┴───────────────┐
                    ▼                               ▼
            需要绑定窗体/报表？               仅需执行命令/获取数据？
                    │                               │
        ┌───────────┴───────────┐                   │
        ▼                       ▼                   ▼
   简单表结构              复杂查询/存储过程      ───→  ADO
   单表或简单JOIN                │                    （最大灵活性）
        │                       │
        ▼                       ▼
    【链接表】              【直通查询】
   （最简单）              （高性能）

场景建议：

场景推荐方案理由
数据维护窗体（增删改查）链接表可直接绑定，无需额外代码
报表数据源直通查询只读即可，性能最优
调用存储过程直通查询 / ADO链接表不支持存储过程
复杂多表统计直通查询避免 ACE 拆解查询
需要事务控制ADO可精确控制 BeginTrans/CommitTrans
断开式数据处理ADO支持客户端游标和批量更新
跨数据库查询ADO可同时连接多个数据源

五、混合架构最佳实践

在实际项目中，三种方案往往需要混合使用：

┌─────────────────────────────────────────────────────────┐
│                    Access 前端                          │
├─────────────────────────────────────────────────────────┤
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  │
│  │   链接表      │  │   直通查询   │  │    ADO      │  │
│  │ (数据维护)   │  │  (报表/统计) │  │  (存储过程)  │  │
│  └──────────────┘  └──────────────┘  └──────────────┘  │
└─────────────────────────────────────────────────────────┘
                           │
                           ▼
              ┌─────────────────────────┐
              │      SQL Server         │
              │   (存储过程/视图/表)     │
              └─────────────────────────┘

架构建议：

  1. 基础表：使用链接表，方便窗体绑定。
  2. 复杂视图：在 SQL Server 端创建视图，Access 链接该视图。
  3. 统计报表：使用直通查询，发挥 SQL Server 的聚合能力。
  4. 业务逻辑：封装为存储过程，通过直通查询或 ADO 调用。

六、总结

维度链接表直通查询ADO
学习成本★☆☆★★☆★★★
开发效率★★★★★☆★☆☆
运行性能★☆☆★★★★★★
灵活性★☆☆★★☆★★★
可维护性★★★★★☆★★☆

核心原则：

  • 能用链接表解决的，不要过度设计。
  • 性能敏感的场景，优先考虑直通查询。
  • 需要精细控制（事务、游标、多数据源）时，使用 ADO。

「Access开发」 专注于 Microsoft Access 开发与企业级应用，提供以下服务：

📚 技术培训

  • Access VBA 从入门到精通（线上/线下）
  • Access + SQL Server 企业级开发实战
  • Access 系统性能优化与架构设计

💼 定制开发

  • 企业 ERP/CRM/进销存系统开发
  • 旧系统升级与性能优化
  • Access 迁移至 Web/Power Platform 咨询

🔧 技术支持

  • 代码审查与重构建议
  • 疑难问题远程诊断
  • 一对一技术辅导

技术改变业务，专注创造价值。