英文:
Cannot save MS Access module after running action query
问题
我在MS Access 2019数据库模块的某处有这段代码：
BeginTrans
DBEngine(0)(0).Execute "delete from table1 where field1='value1'"
CommitTrans
运行此代码后，无法保存任何数据库文档对象：模块、表单或报告。尝试保存时，会出现以下两个消息选项之一：
1) Microsoft Access无法保存设计更改或保存到新的数据库对象，因为文件已被其他用户打开。要保存设计更改或保存到新对象，您必须独占访问该文件。
2) 您目前没有独占访问权。如果继续进行更改，将无法稍后保存它们。
我尝试使用`currentdb()`代替`DBEngine(0)(0)`，但没有成功。只有`docmd.runsql`能正常工作，但这不是执行查询的首选方法。
还尝试使用`workspaces`、`databases`、`tabledefs`集合的刷新方法，但没有成功。
在Access的早期版本（2003及更早版本）中没有此类问题。
这是否是已知问题，如何解决？
**更新**
问题已解决。将数据库分为前端和后端，问题消失了。
（但似乎是某种bug）
英文:
I have this code somewhere in MS Access 2019 database module:
BeginTrans
DBEngine(0)(0).Execute "delete from table1 where field1=""value1"""
CommitTrans
After running this code, I cannot save any database document object: module, form or report.
When try to save, one of two message options appears:
-
Microsoft Access cannot save design changes or save to a new database object because the file is open by another user.
To save design changes or save to a new object, you must have exclusive access to the file. -
You do not currently have exclusive access.
If you keep making changes, you won't be able to save them later.
I tried to use currentdb() instead of DBEngine(0)(0) but with no success.
Only docmd.runsql works as it should, but it is not the preferred method for executing queries.
Also tried to use refresh method of workspaces, databases, tabledefs collections with no success.
I had no such issue in earlier versions of Access (2003 and earlier).
Is this known problem and how to resolve it?
Update
Solved it. Splitted the database into frontend/backend and the problem went away.
(But it seems as a kind of bug)
答案1
得分: 1
建议在打开事务的上下文中限定事务方法，并在同一上下文中运行查询。
例如：
With DBEngine.Workspaces(0)
.BeginTrans
.Databases(0).Execute "myQuery"
.CommitTrans
End With
英文:
It is advisable to qualify transaction methods, and run queries in the same context where you opened the transaction.
E.g.
With DBEngine.Workspaces(0)
.BeginTrans
.Databases(0).Execute "myQuery"
.CommitTrans
End With
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论