用于访问SQL Server存储过程的访问的连接字符串 [英] Connection string for Access to call SQL Server stored procedure

问题描述

使用Access 2007，我想使用一个返回记录集的输入参数调用存储过程.

Using Access 2007, I want to call a stored procedure with one input parameter that returns a recordset.

使用ADODB，除了连接字符串之外，这非常简单.我希望能够从始终指向正确服务器和数据库的特定表中获取服务器和数据库名称. (我不时通过重新链接100个左右的表重新连接到开发数据库以进行测试.)

Using ADODB, this is pretty straightforward except for the connection string. I want to be able to derive the server and database names from a particular table, which always points to the correct server and database. (I reconnect to development dbs from time to time for testing by relinking the 100 or so linked tables.)

是否有一种方法可以从tabledef中获取服务器和数据库名称，而无需将整个内容解析出来?有财产吗?我还没有找到....

Is there a way to get the server and database name from the tabledef without parsing the whole thing out? Is there a property? I haven't found one yet....

最终查询非常简单:EXEC sp_DeleteProjects N'12,24,54'删除项目12、24和54，并返回一个记录集(单行)，其中包含各个子表条目的已删除记录计数.

Final query is pretty simple: EXEC sp_DeleteProjects N'12,24,54' deletes projects 12, 24, and 54, and returns a recordset (single row) with the deleted record counts of the various child table entries.

推荐答案

如果您已经有一个指向SQL Server数据库的Access链接表，则只需将其.Connect字符串与DAO.QueryDef对象一起使用即可执行存储过程，如以下VBA代码所示:

If you already have an Access linked table pointing to the SQL Server database then you can simply use its .Connect string with a DAO.QueryDef object to execute the Stored Procedure, as illustrated by the following VBA code:

Sub CallSP()
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = CurrentDb.TableDefs("dbo_MyTable").Connect
qdf.SQL = "EXEC dbo.MyStoredProcedure"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Debug.Print rst(0).Value
rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

这篇关于用于访问SQL Server存储过程的访问的连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！