SQL Server的传递查询保存到Access VBA记录 [英] SQL Server Passthrough query stored into an Access VBA recordset

问题描述

我们最近创建和迁移我们的Access数据库后端的SQL Server。我想，用VBA code，创建一个到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. What am I doing wrong?

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功能的前pression用户定义函数GETDATE返回运行时错误3085。如果我创建这个相同的查询在MS-Access查询生成器直通，对VBA code之外，它运行良好，并且它不是通过正常返回服务器的日期和时间，只有在code。

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.

我敢肯定，这是简单的东西和任何帮助，将AP preciated！

I'm sure this is something simple and any help would be appreciated!

推荐答案

您需要通过使用的QueryDef 对象来创建一个传递查询，然后打开记录中的QueryDef的 .OpenRecordset 方法。下面code对我的作品：

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 VBA记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！