SQL Server直通查询作为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对象创建直通查询，然后通过QueryDef的.OpenRecordset方法打开Recordset.以下代码对我有用:

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直通查询作为Access中DAO记录集的基础的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！