如何在VBA中更新SQL Server表？ [英] How do I update a SQL server table in VBA ?

问题描述

我的代码似乎有效，但有些问题......如果我执行以下VBA代码，它会更新数据库，但它们在CLOSE语句中失败，说错误3704，文件已经关闭。所以，显而易见的是只删除CLOSE语句，但我已经读过我不需要打开表来执行更新，所以我应该在执行SQL语句的行上使用不同的选项（I试过UPDATE但是没有用）？！？

  Sub  UpdateDatabaseLeague（temp）
 
  On  错误  GoTo   0  
 
 SQLstr =   UPDATE League SET SessionNo =& myEmailSessionNo&   WHERE League。[ID] =& temp 
 
 设置 KA_RS_League = 新 ADODB.Recordset 
 
 如果 KA_RS_League.State = adStateOpen 然后 KA_RS_League.Close 
 
 KA_RS_League。打开SQLstr，KA_DB，adOpenDynamic，adLockOptimistic 
 
 KA_RS_League.Close 
 
 结束  Sub  

我尝试过：



我尝试使用UPDATE而不是OPEN，我在线研究发现我不需要打开桌子......

 KA_RS_League.Update SQLstr，KA_DB，adOpenDynamic，adLockOptimistic 

解决方案

如果您正在做某事结果不需要记录（即更新，插入或删除），然后您可以使用ADODB.Connection对象并使用其Execute方法执行你的SQL。



谷歌adodb.connection或adodb.connection执行代码示例。

你必须使用 ADODB.Command [ ^ ]而不是 ADODB.Recordset 。

为了帮助像我这样编码但不理解所有复杂性和术语的人，此代码可以正常工作对我来说:-)

 选项 明确 
 选项基数 1  
 选项比较文本
 
  Sub  UpdateDatabaseLeague（temp）
 
  On  错误  GoTo   0  
 
 SQLstr =   UPDATE League SET SessionNo =& myEmailSessionNo&   WHERE League。[ID] =& temp 
 
 设置 KA_RS_League = 新 ADODB.Recordset 
 
 如果 KA_RS_League.State = adStateOpen 然后 KA_RS_League.Close 
 
 设置 KA_RS_League = KA_DB.Execute（SQLstr）
 
 结束 子 

I have code that seems to work, but something is awry ... If I execute the following VBA code it DOES update the Database, but them fails on the CLOSE statement saying Error 3704, the file is already Closed. So, the obvious thing is to just delete the CLOSE statement, but I have read that I don't need to OPEN the Table to do the Update, so should I be using a different option on the line that executes the SQL statement (I tried UPDATE but that didn't work) ?!?

Sub UpdateDatabaseLeague(temp)

On Error GoTo 0

SQLstr = "UPDATE League SET SessionNo = " & myEmailSessionNo & " WHERE League.[ID] = " & temp

Set KA_RS_League = New ADODB.Recordset

If KA_RS_League.State = adStateOpen Then KA_RS_League.Close

KA_RS_League.Open SQLstr, KA_DB, adOpenDynamic, adLockOptimistic

KA_RS_League.Close

End Sub

What I have tried:

I tried using UPDATE instead of OPEN and I researched on line to find out that I don't need to Open the Table ...

KA_RS_League.Update SQLstr, KA_DB, adOpenDynamic, adLockOptimistic

解决方案

If you are doing something that doesn't need records as a result (ie update, insert or delete) then you can use the ADODB.Connection object instead and use its Execute method to execute your SQL.

Google "adodb.connection" or "adodb.connection execute" for sample code.

You have to use ADODB.Command[^] instead of ADODB.Recordset.

To help anybody else like myself who codes but doesn't understand all the intricacies and terminology, this code works for me :-)

Option Explicit
Option Base 1
Option Compare Text

Sub UpdateDatabaseLeague(temp)

On Error GoTo 0

SQLstr = "UPDATE League SET SessionNo = " & myEmailSessionNo & " WHERE League.[ID] = " & temp

Set KA_RS_League = New ADODB.Recordset

If KA_RS_League.State = adStateOpen Then KA_RS_League.Close

Set KA_RS_League = KA_DB.Execute(SQLstr)

End Sub

这篇关于如何在VBA中更新SQL Server表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！