我试图创建一个非常动态的宏,该宏将根据用户的选择更新数据库中的不同表。每个表当然都有不同的标题和信息。我在更新时遇到问题(当用户将新记录添加到旧表时)。这是代码的一部分,问题是当它到达“ .update”时,出现“操作必须使用可更新查询”错误。
Dim DBCnn As ADODB.Connection Dim RecSet As ADODB.Recordset Dim sQRY As String Dim FilePath, Titulo, Tabla As String Dim LastRow, LastColumn, TotalRecords, Id As Long Set DBCnn = New ADODB.Connection Set RecSet = New ADODB.Recordset DBCnn.Mode = adModeReadWrite DBCnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & FilePath & ";" sQRY = "SELECT * FROM Customers" & Tabla ' & " WHERE PopID = " & lngid RecSet.CursorLocation = adUseClient RecSet.Open _ Source:=sQRY, _ ActiveConnection:=DBCnn, _ CursorType:=adOpenDynaset, _ LockType:=adLockOptimistic Do While Range("A" & LastRow).Value <> "" ' repeat until first empty cell in column A With RecSet .AddNew .Fields("Id") = Range("A" & LastRow).Value .Fields("Name") = Range("B" & LastRow).Text .Fields("Age") = Range("C" & LastRow).Value .Update '(Here's my error) End With LastRow = LastRow + 1 Loop
舍弃此行:
RecSet.CursorLocation = adUseClient
或者像这样尝试:
RecSet.CursorLocation = adUseServer
请参阅MSDN上的CursorLocation属性(ADO)的“备注”部分:
“如果 CursorLocation 属性设置为 adUseClient ,则记录集将以只读方式访问,并且无法更新记录集到主机。”