gpt4 book ai didi

sql-server - 如何在 MS Access 中查找 ADO 查询的结果

转载 作者:行者123 更新时间:2023-12-03 07:56:20 24 4
gpt4 key购买 nike

微软访问 2019
SQL 服务器 2017
VBA 代码中是否有任何直接、简单的方法来确定此查询是否导致提交或回滚。
这不是不可解决的,但我正在寻找一种不太复杂的方法。
在 MS 访问模块中:

  Dim SQLDB As Object
Dim ADOcom As Object
Dim sql As String

sql = _
"BEGIN TRY " & _
" BEGIN TRAN " & _
" UPDATE MyTable SET MyColumn=100 WHERE AnotherColumn=5 " & _
" COMMIT TRAN " & _
"END TRY " & _
"" & _
"BEGIN CATCH " & _
" ROLLBACK TRAN " & _
"END CATCH"

Set SQLDB = CreateObject("ADODB.Connection")
Set ADOcom = CreateObject("ADODB.Command")
SQLDB.Open "Driver={SQL Server Native Client 11.0};Server=SQL;Database=MyDatabase;Trusted_Connection=yes;"
SQLDB.CursorLocation = adUseClient
Set ADOcom.activeconnection = SQLDB
With ADOcom
.CommandText = sql
.Execute sql

' Code to find out if the .execute resulted in Commit

End With
Set ADOcom = Nothing
SQLDB.Close
End Sub
完成:
在此示例中,两个查询从任意 MS Access 模块发送到任意 SQL Server。
第一个查询返回预期值,但第二个查询不返回。
这两个查询在 SSMS 中都能正常工作。
第二个查询有什么问题?
Sub Example()
Dim SQLDB As Object
Dim query_1 As String, query_2 As String
Dim rs As Object

Set SQLDB = CreateObject("ADODB.Connection")
SQLDB.Open "Driver={SQL Server Native Client 11.0};Server=MySQLserver;Database=MyDatabase;Trusted_Connection=yes;"
Set rs = CreateObject("ADODB.Recordset")
Set rs.activeconnection = SQLDB

query_1 = _
"SELECT 1"

rs.Open query_1
Debug.Print rs(0) ' Expected 1, got 1.
rs.Close

query_2 = _
"DECLARE @Success INT " & _
"BEGIN TRY " & _
" BEGIN TRAN " & _
" SELECT 1/0 " & _
" SET @Success=1 " & _
" COMMIT TRAN " & _
"END TRY" & _
" " & _
"BEGIN CATCH " & _
" ROLLBACK TRAN " & _
" SET @Success=0 " & _
"END CATCH " & _
"SELECT @Success AS [Success]"

rs.Open query_2
Debug.Print rs(0) ' Expected 0 but rs is Nothing and got Error 3265
rs.Close

SQLDB.Close
End Sub

最佳答案

您可以返回 status值或@variable。例如,

  sql = _
"BEGIN TRY " & _
" BEGIN TRAN " & _
" UPDATE MyTable SET MyColumn=100 WHERE AnotherColumn=5 " & _
" SELECT 0 AS [ErrorStatus] " & _
" COMMIT TRAN " & _
"END TRY " & _
"" & _
"BEGIN CATCH " & _
" ROLLBACK TRAN " & _
" SELECT 1 AS [ErrorStatus] " & _
"END CATCH"
您可以在以下代码中使用此结果。在 CATCH您可以使用 THROW获取原始错误并仍然回滚事务:
BEGIN CATCH
ROLLBACK TRAN;
THROW;
END CATCH
这意味着您需要在 vb.code 本身中有一个 try/catch。如果您需要错误详细信息,另一种方法是使用以下函数(在 SSMS 中执行):
BEGIN TRY  
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
他们正在返回错误的详细信息。您可以将它们返回或将它们分配给变量并稍后处理。

关于sql-server - 如何在 MS Access 中查找 ADO 查询的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/64058063/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com