跳过记录集输入的代码,ADODB Excel VBA
问题描述:
今天一直是YouTube和互联网的淘汰日,我还没有弄清楚如何让这段代码运行。它打开和关闭连接没有麻烦,但是当我尝试写入记录集时,它会完全跳过所有内容并关闭连接而不输入任何数据。跳过记录集输入的代码,ADODB Excel VBA
Const AccessConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Vision\Database\BVAS.accdb;Persist Security Info=False;"
Private Sub BtnSave_Click()
Dim DbConn As ADODB.Connection
Dim Inventory As ADODB.Recordset
Dim r As Range
Set DbConn = New ADODB.Connection
Set Inventory = New ADODB.Recordset
DbConn.ConnectionString = AccessConnStr
DbConn.Open
On Error GoTo CloseConnection
With Inventory
.ActiveConnection = DbConn
.Source = "T3Scaffold_Inventory"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
On Error GoTo CloseRecordset
End With
CountSheet.Activate
'WTF, why does it skip this ? Research if and IF or Do WHILE statement will work here for so that only the Items with quantity >0 are sent to ACCESS
For Each r In .Range("A2", Range("A2").End(xlDown))
With Inventory
.AddNew
.Fields("ScaffoldID") = Range("ScaffoldID")
.Fields("ItemNumber") = Cells(i + 1, 1).Value
.Fields("Quantity") = Cells(i + 1, 3).Value
.Fields("WorktypeID") = Range("WorktypeID")
.Fields("RentStartDate") = Range("Date")
.Fields("OnRent?") = "Yes"
.Update
End With
Next r
CloseRecordset:
Inventory.CancelUpdate
Inventory.Close
CloseConnection:
DbConn.Close
Set Inventory = Nothing
Set DbConn = Nothing
End Sub
谢谢大家。我希望这是一个超级复杂的解决方案。 -MC
答
你因为
For Each r In .Range("A2", Range("A2").End(xlDown))
上的错误错误,对错误转到CloseRecordset
代码被改变这样
For Each r In Activesheet.Range("A2", Activesheet.Range("A2").End(xlDown))
或
For Each r In Range("A2", Range("A2").End(xlDown))
+0
我唯一不得不将'CountSheet.activate'修改为'Worksheets(“CountSheet”)。Activate'。 –
所以它g ets到“For Each r in .range”开始的行,然后直接跳到“CloseRecordset:”? – jamheadart
我在每条语句中都放了一个Break点,并跳过它。它从'结束'到'CloseRecordset:'不迭代循环。 –
直接跳到'CloseRecordset'标签的事实表明'CountSheet.Activate'产生了一个错误条件。就在“结束”之前,你有'On Error GoTo CloseRecordset'。试着在'CloseRecordset'标签后面添加一行,看看错误是什么:'MsgBox“Error”&Err.Number&“ - ”&Err.Description' – Skippy