摘要后台是 SQL Server 却不想用链接表本文介绍 DAO 直连绑定窗体和 ADO 参数化查询两种方案配完整可运行代码直接拿走用。access开发|access培训|access框架|请添加edonsoft。Hi大家好上一篇讲了 Access 和 SQL Server 的差别。有读者看完之后问了一个很实际的问题后台已经是 SQL Server前端 Access 用的是链接表但现在不想用链接表了有没有别的办法让窗体继续能查数据、能编辑这个需求我遇到过不止一次原因也各不相同——有的是网络环境里链接表刷新太慢有的是服务器迁移了链接路径失效还有的是想做更严格的权限控制不想让 Access 直接看到表结构。办法有两种。一种是 DAO 直连用DBEngine.OpenDatabase在 VBA 里打开 ODBC 连接把记录集直接绑给窗体新增、修改、删除全部照常体验和链接表几乎没差别。另一种是 ADO 参数化查询用ADODB.Command带参数跑 SQL把结果填进列表框或子窗体更适合复杂筛选、存储过程调用以及需要控制事务的保存操作。两种都给完整代码。先说 DAO 直连DAOData Access Objects是 Access 的原生数据访问接口很多人不知道它其实也能不靠链接表直接连 ODBC 数据源。做法是用DBEngine.OpenDatabase打开一个 ODBC 连接然后拿到的DAO.Recordset直接赋给窗体的Recordset属性窗体就有数据了。这种方式最大的好处是窗体保持绑定状态新增、修改、删除照常用Access 的导航按钮、记录锁都还在几乎和链接表的使用体验一样只是数据源换成了 ODBC 直连。DAO 方案完整代码新建一个标准模块命名modSQLConn把下面的连接字符串函数放进去后面窗体代码会用到Option Compare Database Option Explicit 返回 SQL Server 无 DSN 连接字符串 根据实际情况修改 SERVER、DATABASE Public Function SQLConnStr() As String SQLConnStr ODBC; _ DRIVER{ODBC Driver 18 for SQL Server}; _ SERVERSQL01; _ DATABASESalesDb; _ Trusted_ConnectionYes; _ EncryptYes; _ TrustServerCertificateYes; End Function然后打开需要绑定的窗体设计视图把窗体的记录源清空在窗体模块里写Option Compare Database Option Explicit 注意必须声明在模块顶部不能放在 Form_Load 里 生命周期和窗体绑定窗体关闭前不能释放 Private mDb As DAO.Database Private mRs As DAO.Recordset Private Sub Form_Load() Dim sql As String 打开 ODBC 直连不用预先建 DSN dbDriverNoPrompt连接失败直接报错不弹驱动选择框 Set mDb DBEngine.OpenDatabase( _ , dbDriverNoPrompt, False, SQLConnStr()) 写你真正需要的查询必须包含主键列否则记录集不可更新 sql SELECT OrderID, CustomerID, OrderDate, Amount, Remark _ FROM dbo.Orders _ ORDER BY OrderDate DESC; dbOpenDynaset动态集支持编辑 dbSeeChanges表有 IDENTITY 自增列时必须加否则新增报错 Set mRs mDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges) 把记录集绑给窗体完成后窗体控件自动按字段名匹配 Set Me.Recordset mRs End Sub Private Sub Form_Unload(Cancel As Integer) 窗体关闭时释放资源顺序不能反 If Not mRs Is Nothing Then mRs.Close Set mRs Nothing End If If Not mDb Is Nothing Then mDb.Close Set mDb Nothing End If End Sub窗体里的文本框控件名字只要和查询字段名一致不区分大小写绑定自动生效不需要手动设控件来源但是你的控件一定要添加控件来源。有三个地方容易出问题写这段代码之前先说清楚。mDb和mRs必须声明在窗体模块顶部不能放在Form_Load里。放进过程里就成了局部变量Form_Load跑完就释放窗体打开之后数据随时会变成空白或者弹出对象无效的错误。这是我见过最多人踩的地方而且报错时机不固定有时候立刻报有时候要等用户翻几页记录才出。查询必须包含主键且查询本身可更新。两表联接、带GROUP BY、带DISTINCT的查询基本上都是只读的这种结果集赋给窗体之后能看数据但改不了。如果窗体只需要显示问题不大如果需要编辑就得把查询拆开或者改用 ADO 加存储过程来保存。dbSeeChanges这个参数SQL Server 表有IDENTITY自增列时必须加。不加的话新增一条记录之后 Access 找不到刚插入的那行会弹找不到记录。加上之后 Access 在 INSERT 完成后会自动定位到新行这个问题就消失了。加筛选条件如果窗体需要按条件筛选比如按订单日期范围不要拼接 SQL 字符串改用Recordset.FilterPrivate Sub btnFilter_Click() Dim d1 As String Dim d2 As String 取文本框里的日期转成 SQL Server 认识的格式 d1 Format(Me.txtDateFrom, yyyy-mm-dd) d2 Format(Me.txtDateTo, yyyy-mm-dd) Filter 条件用字段名日期用单引号括起来 mRs.Filter OrderDate d1 AND OrderDate d2 用筛选后的克隆集重新绑窗体 Set Me.Recordset mRs.OpenRecordset() End Sub如果筛选条件变化很大比如字段都不固定更简单的做法是重新执行mDb.OpenRecordset用新 SQL 替换旧的再重新赋给Me.Recordset。再说 ADO 参数化查询ADOActiveX Data Objects连接 SQL Server 时走 OLE DB 或 ODBC写法和连接其他数据库基本一样。我一般在这几种情况下选 ADO 而不是 DAO筛选条件多、带多个参数的查询需要调用 SQL Server 存储过程执行写入操作时需要拿回影响行数或者输出参数。ADO 的一个重要习惯是参数化——用?占位符传值不把变量直接拼进 SQL 字符串。日期格式、单引号转义这些问题直接绕开SQL 注入的风险也没有了。我见过不少人图省事用WHERE CustomerID Me.cboCustomer这种拼法字段是数字还好一旦遇到字符串或者日期调试起来很麻烦。ADO 公共模块用 ADO 之前要先在 Access 引用库里勾上Microsoft ActiveX Data Objects。VBE 菜单 → 工具 → 引用找到Microsoft ActiveX Data Objects 6.1 Library或者 2.8装了什么版本就选哪个勾上确定。没有这一步代码里的ADODB.Connection会报用户自定义类型未定义。新建标准模块modADOOption Compare Database Option Explicit 建立 ADO 连接成功返回 ADODB.Connection失败返回 Nothing 调用方负责关闭和释放 Public Function ADO_Connect() As ADODB.Connection Dim conn As ADODB.Connection Set conn New ADODB.Connection OLE DB Provider for SQL Server MSOLEDBSQL 是微软 2018 年后推荐的新驱动需独立安装 https://learn.microsoft.com/zh-cn/sql/connect/oledb/download-oledb-driver-for-sql-server 如果没装换成 SQLNCLI11SQL Server 2012 自带 ProviderSQLNCLI11; OLE DB Windows 集成验证用 Integrated SecuritySSPI 不是 ODBC 的 Trusted_ConnectionYes那个 OLE DB 不认 改用 SQL 账号的话替换为UIDsa;PWDyourpwd; conn.ConnectionString _ ProviderMSOLEDBSQL; _ ServerSQL01; _ DatabaseSalesDb; _ Integrated SecuritySSPI; _ TrustServerCertificateyes; On Error GoTo ConnErr conn.Open Set ADO_Connect conn Exit Function ConnErr: Set conn Nothing Set ADO_Connect Nothing MsgBox 连接 SQL Server 失败 Err.Description, vbCritical End Function如果机器上没有 MSOLEDBSQL也可以换成 ODBC 方式ProviderMSDASQL;DRIVER{ODBC Driver 18 for SQL Server};SERVERSQL01;...两种写法功能上没区别Driver 18 装了就能用。参数化查询 Demo按客户和日期范围查询订单 查询订单结果填入列表框 lstOrders lstOrders 的列数需要提前设好列宽也要配好 Private Sub btnQuery_Click() Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim rows As String Set conn ADO_Connect() If conn Is Nothing Then Exit Sub Set cmd New ADODB.Command cmd.ActiveConnection conn 参数用 ? 占位不拼字符串 cmd.CommandText _ SELECT OrderID, CustomerName, OrderDate, Amount _ FROM dbo.Orders _ WHERE CustomerID ? _ AND OrderDate BETWEEN ? AND ? _ ORDER BY OrderDate DESC; cmd.CommandType adCmdText 按顺序追加参数类型、方向、大小、值 adInteger, adDate, adDate cmd.Parameters.Append cmd.CreateParameter(CustID, adInteger, adParamInput, , CLng(Me.cboCustomer)) cmd.Parameters.Append cmd.CreateParameter(D1, adDate, adParamInput, , CDate(Me.txtDateFrom)) cmd.Parameters.Append cmd.CreateParameter(D2, adDate, adParamInput, , CDate(Me.txtDateTo)) Set rs cmd.Execute 用 ValueList 方式填列表框 也可以改用 rs 直接赋给子窗体的 Recordset rows Do While Not rs.EOF rows rows rs(OrderID) ; _ rs(CustomerName) ; _ Format(rs(OrderDate), yyyy-mm-dd) ; _ Format(rs(Amount), #,##0.00) ; rows rows Chr(10) rs.MoveNext Loop rs.Close conn.Close Set rs Nothing Set cmd Nothing Set conn Nothing Me.lstOrders.RowSourceType Value List Me.lstOrders.RowSource rows End Sub参数化执行 Demo保存一条订单 保存窗体上的订单数据到 SQL Server 成功返回 True失败返回 False Public Function SaveOrder( _ ByVal customerID As Long, _ ByVal orderDate As Date, _ ByVal amount As Currency, _ ByVal remark As String _ ) As Boolean Dim conn As ADODB.Connection Dim cmd As ADODB.Command Set conn ADO_Connect() If conn Is Nothing Then SaveOrder False Exit Function End If Set cmd New ADODB.Command cmd.ActiveConnection conn cmd.CommandText _ INSERT INTO dbo.Orders (CustomerID, OrderDate, Amount, Remark) _ VALUES (?, ?, ?, ?); cmd.CommandType adCmdText cmd.Parameters.Append cmd.CreateParameter(CustID, adInteger, adParamInput, , customerID) cmd.Parameters.Append cmd.CreateParameter(Date, adDate, adParamInput, , orderDate) cmd.Parameters.Append cmd.CreateParameter(Amount, adCurrency, adParamInput, , amount) cmd.Parameters.Append cmd.CreateParameter(Remark, adVarWChar, adParamInput, 500, remark) On Error GoTo SaveErr cmd.Execute conn.Close Set cmd Nothing Set conn Nothing SaveOrder True Exit Function SaveErr: MsgBox 保存失败 Err.Description, vbCritical If Not conn Is Nothing Then conn.Close Set cmd Nothing Set conn Nothing SaveOrder False End Function调用的地方很简单Private Sub btnSave_Click() If SaveOrder(Me.cboCustomer, Me.txtDate, Me.txtAmount, Me.txtRemark) Then MsgBox 保存成功, vbInformation Me.txtAmount Null Me.txtRemark Null End If End Sub调用 SQL Server 存储过程如果保存逻辑放在 SQL Server 存储过程里——比如要做库存扣减、写操作日志、或者要保证多张表同时写入——ADO 也可以直接调把CommandType换成adCmdStoredProc就行。假设 SQL Server 端有这样一个存储过程CREATEPROCEDUREdbo.usp_AddOrderCustomerIDINT,OrderDateDATE,AmountDECIMAL(18,2),RemarkNVARCHAR(500),NewOrderIDINTOUTPUT-- 返回新生成的订单号ASBEGINSETNOCOUNTON;INSERTINTOdbo.Orders(CustomerID,OrderDate,Amount,Remark)VALUES(CustomerID,OrderDate,Amount,Remark);SETNewOrderIDSCOPE_IDENTITY();ENDVBA 这边这样调Public Function CallAddOrder( _ ByVal customerID As Long, _ ByVal orderDate As Date, _ ByVal amount As Currency, _ ByVal remark As String, _ ByRef newOrderID As Long _ ) As Boolean Dim conn As ADODB.Connection Dim cmd As ADODB.Command Set conn ADO_Connect() If conn Is Nothing Then CallAddOrder False Exit Function End If Set cmd New ADODB.Command cmd.ActiveConnection conn cmd.CommandText dbo.usp_AddOrder cmd.CommandType adCmdStoredProc 改这里 输入参数 cmd.Parameters.Append cmd.CreateParameter(CustomerID, adInteger, adParamInput, , customerID) cmd.Parameters.Append cmd.CreateParameter(OrderDate, adDate, adParamInput, , orderDate) cmd.Parameters.Append cmd.CreateParameter(Amount, adCurrency, adParamInput, , amount) cmd.Parameters.Append cmd.CreateParameter(Remark, adVarWChar, adParamInput, 500, remark) 输出参数adParamOutput不传值执行后从这里读回来 cmd.Parameters.Append cmd.CreateParameter(NewOrderID, adInteger, adParamOutput, , 0) On Error GoTo SPErr cmd.Execute newOrderID cmd.Parameters(NewOrderID).Value 读取存储过程返回的新订单号 conn.Close Set cmd Nothing Set conn Nothing CallAddOrder True Exit Function SPErr: MsgBox 调用存储过程失败 Err.Description, vbCritical If Not conn Is Nothing Then conn.Close Set cmd Nothing Set conn Nothing CallAddOrder False End Function这种写法Access 前端不需要知道存储过程里写了什么库存怎么扣、日志怎么写、哪几张表要联动都在 SQL Server 里处理VBA 只管传参数、拿结果。两种方案怎么选我自己的习惯是这样区分的场景推荐方案窗体需要连续编辑多条记录新增、修改、删除频繁DAO 直连绑定筛选条件复杂、带多个参数的查询ADO 参数化查询需要调用存储过程或有事务、返回输出参数ADO Command只读报表、统计汇总ADO 查询结果填子窗体或列表框核心业务录入有严格的校验和审计要求非绑定窗体 ADO 调存储过程保存两种方案在同一个项目里混用很常见。比如查询列表用 ADO点进某条记录打开编辑窗体用 DAO 绑定——查询这边灵活编辑这边省事各取所长。还有一种更轻量的方式是传递查询Pass-through Query在 Access 查询设计器里直接建连接字符串写 SQL Server ODBC 地址SQL 在服务器端跑结果返回给 Access。适合只读查询或者不需要在 VBA 里动态拼参数的场景。这个我之前专门写过这里不重复了。去掉链接表之后DAO 方案改动最小和原来链接表绑窗体的体验几乎没差别迁移起来也快。ADO 的价值在往后走——等你开始需要存储过程、输出参数、事务控制那套代码不用大改加参数就行。
