SQL Server Passthrough 查询作为 Access 中 DAO 记录集的基础 [英] SQL Server Passthrough query as basis for a DAO recordset in Access

问题描述

我们最近创建了 Access DB 后端并将其迁移到 SQL Server.我正在尝试使用 VBA 代码创建与 SQL Server 后端的连接，并使用存储在 VB 记录集中的结果运行直通查询.当我尝试这个时，查询没有通过.

We've recently created and migrated our Access DB backend to SQL Server. I'm trying to, using VBA code, create a connection to the SQL Server backend and run a passthrough query with the results stored in a VB recordset. When I try this, the query is NOT passing through.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strConnect As String

strConnect = "DRIVER=SQL Server;SERVER=55.55.55.55 SQLExpress;UID=UserName;PWD=Password"

Set db = OpenDatabase("DBName", dbDriverNoPrompt, True, strConnect)

Set rs = db.OpenRecordset("SELECT GetDate() AS qryTest", dbOpenDynaset)

MsgBox rs!qryTest

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

我遇到的问题是完全合适的 GetDate() SQL Server 函数返回运行时错误 3085表达式中的用户定义函数‘GetDate’".如果我在 MS-Access 查询生成器中创建相同的查询作为传递，在 VBA 代码之外，它运行良好并返回服务器日期和时间，只有在代码中它不能正确传递.

The problem I'm getting is that the totally appropriate GetDate() SQL Server function is returning Runtime Error 3085 "User Defined Function 'GetDate' in expression". If I create this same query as a passthrough in MS-Access Query Builder, outside of VBA code, it runs fine and returns the server date and time, only in code is it not passing through properly.

推荐答案

您需要使用 QueryDef 对象来创建 Pass-Through 查询，然后通过 .OpenRecordset 打开 Recordset QueryDef 方法.以下代码对我有用:

You need to use a QueryDef object to create a Pass-Through query, then open the Recordset via the .OpenRecordset method of the QueryDef. The following code works for me:

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;Server=.SQLEXPRESS;Trusted_Connection=Yes;"
qdf.SQL = "SELECT GetDate() AS qryTest"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Debug.Print rst!qryTest
rst.Close
Set rst = Nothing
Set qdf = Nothing

这篇关于SQL Server Passthrough 查询作为 Access 中 DAO 记录集的基础的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！