“无效的SQL语句"尝试从Access执行SQL Server存储过程时发生错误 [英] "Invalid SQL statement" error when trying to execute SQL Server stored procedure from Access

问题描述

我正在SQL Server和MS Access所在的虚拟机上运行.我建立了从Access到SQL的ODBC连接，并链接了一些表.我可以对这些表执行常规操作(选择/更新等).

I'm operating on a virtual machine where my SQL server and MS Access sit. I set up an ODBC connection from Access to SQL and linked a few tables. I can perform the usual operations on these tables (select/update etc.).

但是，由于某些奇怪的原因，我无法运行存储过程！该过程可以在SSMS上完美运行，但是当我从Access VBA调用该过程时却不能.以下是我用来执行proc的代码(我也需要传递3个参数，但为简单起见，我从下面的代码中排除了这些参数):

BUT, I'm unable to run a stored procedure for some weird reason! The procedure runs perfectly on SSMS but not when I call it from Access VBA. Following is the code I'm using to execute the proc (I need to pass 3 parameters as well, but I've excluded that from the code below for simplicity):

With CurrentDb.QueryDefs("qPass")
  .SQL = "exec [HS].[spGetXMLExtract]"
  .Execute
End With

MS Access返回的错误是

The error returned by MS Access is

无效的SQL语句:预期为'DELETE'，'INSERT'，'PROCEDURE'，'SELECT'或'UPDATE'.

Invalid SQL statement: expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

不知道这是否正在发生，因为: 1.我已经在虚拟机上安装了这些应用程序；和/或 2.我需要以某种方式将存储过程链接到MS Access，就像我对表所做的那样(也许无法完成).

Don't know if this is happening because: 1. I've got these applications on a VM; and/or 2. I need to somehow link the stored procedure to MS Access just like I did for the tables (perhaps this can't be done).

我猜想我可能会采用一些复杂的方法，但我不想这样做.示例:

I guess there are convoluted methods I 'might' be able to adopt but I don't want to. Example:

1..在SQL中创建一个表，其中包含用于存储参数的列；在执行我的存储过程的表上编写一个AFTER UPDATE触发器；从MS Access触发更新"查询，该查询将更新该表中的参数，然后触发SQL触发器.或；

1. Create a table in SQL with columns that store the parameters; Write an AFTER UPDATE Trigger on that table which executes my stored procedure; Fire an 'Update' query from MS Access that would update the parameters in that table and a SQL trigger then gets fired. OR;

2..从方程式中完全消除存储过程，并通过MS Access中的Sub执行其单独的语句(选择/更新/插入等).不知道这是否会导致创建临时表时出现问题.

2. Eliminate the stored procedure from the equation completely and execute it's individual statements (select/update/insert etc.) through a Sub in MS Access. Don't know if this would cause problems with creating temp tables though.

有人可以提供建议吗，这是一个真正的阻止者！

Can someone please advise on this, this is a real blocker!

提前谢谢！

推荐答案

如果QueryDef没有有效的"ODBC;..."连接字符串作为其.Connect属性，您将收到该错误消息.这就是Access将QueryDef识别为传递查询的方式.

You will receive that error message if the QueryDef does not have a valid "ODBC;..." connection string as its .Connect property. That is how Access identifies the QueryDef as a pass-through query.

如果已经定义了ODBC链接表，则可以将其.Connect属性值用作QueryDef的.Connect属性，如下所示:

If you already have an ODBC linked table defined, you can use its .Connect property value for the .Connect property of the QueryDef, like so:

Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_table1").Connect  ' grab .Connect string from linked table
qdf.sql = "exec [HS].[spGetXMLExtract]"
qdf.ReturnsRecords = False
qdf.Execute

...，或者，如果存储过程确实返回了结果集:

... or, if the stored procedure does in fact return a result set:

Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_table1").Connect  ' grab .Connect string from linked table
qdf.sql = "exec [HS].[spGetXMLExtract]"
qdf.ReturnsRecords = True
Dim rst As DAO.Recordset
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rst.EOF
    ' do stuff
    rst.MoveNext
Loop
rst.Close

这篇关于“无效的SQL语句"尝试从Access执行SQL Server存储过程时发生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！