通过 VBA 从传递查询返回值

发布于 2024-10-16 11:59:40 字数 407 浏览 4 评论 0原文

我有 VBA 代码来在 SQL-Server 2008 中运行查询。它运行良好并显示我需要的表。执行此操作的代码位于：

Set db = CurrentDb
Set qdf = db.QueryDefs("MyStoredProcedure")

qdf.SQL = "exec [WCNS_Ops].[dbo].MyStoredProcedure [plus a bunch of parameters]"   
DoCmd.OpenQuery "MyStoredProcedure"

显示此表：

存储过程返回的表的图片

我的问题是这样的：如何以编程方式将这些值返回到 VBA 代码而不显示表格？

原文

I have VBA code to run a query in SQL-Server 2008. It runs fine and displays the table that I need. The code that does this is here:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyStoredProcedure")

qdf.SQL = "exec [WCNS_Ops].[dbo].MyStoredProcedure [plus a bunch of parameters]"   
DoCmd.OpenQuery "MyStoredProcedure"

which displays this table:

Picture of the table the stored procedure returns

My question is this: How do I programmatically return these values to VBA code without displaying the table?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

五里雾 2024-10-23 11:59:40

以下代码未经测试，但应该可以帮助您指明正确的方向：

Set db = CurrentDb

Set qdf = db.QueryDefs("MyStoredProcedure")
qdf.ReturnsRecords = True
qdf.SQL = "exec [WCNS_Ops].[dbo].MyStoredProcedure [plus a bunch of parameters]"  

With qdf.OpenRecordset(dbOpenSnapshot)  'could also be dbOpenDynaset, etc. '
    Do Until .EOF
        Debug.Print !firstid
        Debug.Print !lastid
        .MoveNext
    Loop
End With

The following code is untested, but should get you pointed in the right direction:

Set db = CurrentDb

Set qdf = db.QueryDefs("MyStoredProcedure")
qdf.ReturnsRecords = True
qdf.SQL = "exec [WCNS_Ops].[dbo].MyStoredProcedure [plus a bunch of parameters]"  

With qdf.OpenRecordset(dbOpenSnapshot)  'could also be dbOpenDynaset, etc. '
    Do Until .EOF
        Debug.Print !firstid
        Debug.Print !lastid
        .MoveNext
    Loop
End With

回复收藏 0 原文

水水月牙 2024-10-23 11:59:40

您所需要做的就是执行查询并将其输出设置为记录集。我的脑海里浮现出类似这样的东西

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset
Dim cmd as new ADODB.Command

dbCon.ConnectionString=”Your Connection String”
with cmd
    .comandtype=adCmdStoredProc
    .commandtext=”Your SP name”
    .Parameters.Append .CreateParameter("@Pram1", adVarChar, adParamInput, 50, “WhatEver”)
    .ActiveConnection=dbCon
    .NamedParameters = True
    Set rst = .Execute
end with

with rst
    if .EOF=false then
        myVar=!Column1
    end if
end with

rst.close
dbcon.close
set cmd=nothing

All you need to do is execute the query and set its output to a recordset. Off the top of my head something like this

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset
Dim cmd as new ADODB.Command

dbCon.ConnectionString=”Your Connection String”
with cmd
    .comandtype=adCmdStoredProc
    .commandtext=”Your SP name”
    .Parameters.Append .CreateParameter("@Pram1", adVarChar, adParamInput, 50, “WhatEver”)
    .ActiveConnection=dbCon
    .NamedParameters = True
    Set rst = .Execute
end with

with rst
    if .EOF=false then
        myVar=!Column1
    end if
end with

rst.close
dbcon.close
set cmd=nothing

回复收藏 0 原文

~没有更多了~