操作必须使用一个可更新的查询/ SQL - VBA [英] Operation must use an Updateable Query / SQL - VBA
问题描述
我想创建一个充满活力的宏,将在数据库中更新不同的表,这取决于用户选择的内容。每个表都有,当然不同的标题和信息。 (当用户添加新的记录到旧表)我具有与所述更新的问题。这是code部分,问题是当它到达了.update,我得到了操作必须使用一个可更新的查询的错误。
昏暗DBCnn作为ADODB.Connection
昏暗RecSet作为ADODB.Recordset
昏暗sQRY作为字符串
昏暗的文件路径,TITULO,塔布拉作为字符串
朦胧LASTROW,LastColumn,总记录,ID作为龙
设置DBCnn =新ADODB.Connection
设置RecSet =新ADODB.Recordset
DBCnn.Mode = adModeReadWrite
DBCnn.Open供应商= Microsoft.ACE.OLEDB.12.0; &放大器; 数据源=&放大器;文件路径和放大器; ;
sQRY =SELECT * FROM客户&放大器;塔布拉'和; WHERE PopID =&放大器; lngid
RecSet.CursorLocation = adUseClient的
RecSet.Open _
资料来源:= sQRY,_
的ActiveConnection:= DBCnn,_
的CursorType:= adOpenDynaset,_
锁定类型:= ADLOCKOPTIMISTIC
做虽然范围(A&放大器; LASTROW).value的<>
重复,直至在A列的第一个空单元格
随着RecSet
。添新
点域(ID)=范围(A&放大器; LASTROW).value的
点域(名称)=范围(B&放大器; LASTROW)。文
点域(时代)=范围(C和放大器; LASTROW).value的
.Update(这是我的错误)
结束与
LASTROW = LASTROW + 1
循环
弃掉此行:
RecSet.CursorLocation = adUseClient的
或尝试像这样:
RecSet.CursorLocation = adUseServer
请参阅在 CursorLocation属性的备注部分( ADO) MSDN上:
如果的CursorLocation 属性设置为为adUseClient ,该记录将被映射为只读,并记录更新主机是不可能的。 的
I am trying to create a very dynamic macro that will update different tables in a Database, depending on what the user chooses. Each table has of course distinct titles and information. I'm having a problem with the update (when the user adds new records to an old Table). This is part of the code, the problem is when it gets to the ".update", I get the "Operation must use an Updateable Query" error.
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
Discard this line:
RecSet.CursorLocation = adUseClient
Or try it like this instead:
RecSet.CursorLocation = adUseServer
See the Remarks section at CursorLocation Property (ADO) on MSDN:
"If the CursorLocation property is set to adUseClient, the recordset will be accessible as read-only, and recordset updates to the host are not possible."
这篇关于操作必须使用一个可更新的查询/ SQL - VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!