sql - 操作必须使用可更新查询/SQL - VBA

Question

我正在尝试创建一个非常动态的宏，它将根据用户的选择更新数据库中的不同表。每个表当然都有不同的标题和信息。我在更新时遇到问题(当用户将新记录添加到旧表时)。这是代码的一部分，问题是当它到达“.update”时，我得到“Operation must use an Updateable Query”错误。

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

Answer 1

丢弃这一行:

RecSet.CursorLocation = adUseClient

或者像这样尝试:

RecSet.CursorLocation = adUseServer

请参阅 CursorLocation Property (ADO) 的备注部分在 MSDN 上:

“如果 CursorLocation 属性设置为 adUseClient，记录集将以只读方式 Access ，并且无法对主机更新记录集。 "