vba Recordset.Update 数据库或对象是只读的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20431018/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recordset.Update Database or Object is read-only
提问by Graham Warrender
Not sure this is going to work in the specifics I intend. The scenario is the text boxes are populated with data from a table, and the user edits the records and clicks a button to save.
不确定这是否会在我打算的细节中起作用。该场景是文本框填充有表中的数据，用户编辑记录并单击按钮进行保存。
Dim cdb As DAO.Database, rstEdit As DAO.Recordset
Set cdb = CurrentDb
DataValues = "SELECT * FROM Companies, Link_Table WHERE Companies.CompanyID = " & SelectedValue & ";"
Set rstEdit = CurrentDb.OpenRecordset(DataValues, dbOpenSnapshot)
With rstEdit
OldCompanyName = !CompanyName
OldCompanyDescription = !Description
OldFriendlyName = !FriendlyName
OldAddressLine1 = !AddressLine1
OldAddressLine2 = !AddressLine2
OldAddressLine3 = !AddressLine3
OldTown = !Town
OldPostcode = !AddressPostcode
OldCounty = !AddressCounty
OldMainTelephone = !MainTelephone
OldMainEmail = !MainEmail
OldWeb = !WebAddress
'Not sure this is necessary. It was quoted in the example, but unsure why? ^'
'不确定这是必要的。示例中引用了它，但不确定为什么？^'
!CompanyName = NewCompanyName
!Description = NewCompanyDescription
!FriendlyName = NewFriendlyName
!AddressLine1 = NewAddressLine1
!AddressLine2 = NewAddressLine2
!AddressLine3 = NewAddressLine3
!Town = NewTown
!AddressPostcode = NewPostcode
!AddressCounty = NewCounty
!MainTelephone = NewMainTelephone
!MainEmail = NewMainEmail
!WebAddress = NewWeb
.Update
End With
However, on clicking the save button, it errors stating that the Database or object is read only, and I'm unsure why. As far as I know it isn't open anywhere, nor should it get opened for any reason! Was hoping someone would be able to shed some light on the issue. The table has a primary key set, which is CompanyID however this isn't used in the routine.
但是，在单击保存按钮时，它会出错，指出数据库或对象是只读的，我不确定为什么。据我所知，它没有在任何地方打开，也不应该出于任何原因打开！希望有人能够对这个问题有所了解。该表有一个主键集，即 CompanyID，但在例程中未使用。
回答by pteranodon
The Type parameter dbOpenSnapShotin:
类型参数dbOpenSnapShot在：
Set rstEdit = CurrentDb.OpenRecordset(DataValues, dbOpenSnapshot)
makes rstEdit open as a read-only recordset(or snapshot). Consider using dbOpenDynasetinstead.
使 rstEdit 作为只读记录集（或快照）打开。考虑dbOpenDynaset改用。
回答by HansUp
As mentioned in another answer, the dbOpenSnapshotoption results in a read-only recordset. However once you change the OpenRecordsetoption, you will still be left with a read-only recordset.
如另一个答案中所述，该dbOpenSnapshot选项会生成只读记录集。但是，一旦您更改了该OpenRecordset选项，您仍然会得到一个只读记录集。
The query used as the recordset source includes an implicit cross joinbetween Companiesand Link_Table. The means every row from Companiesis matched with every row from Link_Table. And that means Access will consider the query read-only which in turn means the recordset will also be read-only.
用作记录集源的查询包括和之间的隐式交叉联接。这意味着每一行来自与每一行相匹配。这意味着 Access 会将查询视为只读，这反过来意味着记录集也将是只读的。CompaniesLink_TableCompaniesLink_Table
Build and test a new query in the query designer where you explicitly define a JOINcondition. Start with something simple; leave out the WHEREclause until after you have a working JOIN.
在您明确定义JOIN条件的查询设计器中构建和测试新查询。从简单的事情开始；WHERE在您完成工作之前，请忽略该条款JOIN。
SELECT *
FROM
Companies
INNER JOIN Link_Table
ON Companies.link_field = Link_Table.link_field;
Notes:
笔记：
- If the two tables don't include a common field (or set of fields) which you can use as link_field, the task will be much more challenging ... and you may not even be able to open an editable recordset.
- If the
JOINis one-to-many, you should be able to edit fields from the table on the "many" side, but may not be able to edit fields from the table on the "one" side of theJOIN.
- 如果这两个表不包含可用作link_field的公共字段（或字段集），则任务将更具挑战性……您甚至可能无法打开可编辑的记录集。
- 如果
JOIN是一对多，则您应该能够从“多”侧的表中编辑字段，但可能无法从JOIN.
回答by Shiva
Try specifying editable LockEditsargument in your OpenRecordset method.
尝试LockEdits在 OpenRecordset 方法中指定可编辑参数。
Set recordset=object.OpenRecordset(source, type, options, lockedits)
You create an editable recordset when you use one of the following constants in the LockEditsargument of the OpenRecordsetmethod:
当您LockEdits在OpenRecordset方法的参数中使用以下常量之一时，您将创建一个可编辑的记录集：
dbOptimistic, dbPessimistic, dbOptimisticValue, or dbOptimisticBatch
SourceVBA: Run-Time Error '3027' Using ODBCDirect to Open RecordSet: http://support.microsoft.com/kb/161252
源VBA：运行时错误“3027”使用 ODBCDirect 打开记录集：http: //support.microsoft.com/kb/161252
