Access 2007 - INSERT 和即时 SELECT 不检索插入的数据 [英] Access 2007 - INSERT and instant SELECT doesn't retrieve the inserted data
问题描述
我通过 OleDB 在表中插入几行，并立即选择插入的行.
I'm inserting a few rows in a table via OleDB, and select instantly the inserted row.
我无法以这种方式检索行，我必须等待大约.3-5 秒.然后插入的行出现在表格中.
I can't retrieve the row in this way, i have to wait for approx. 3-5 seconds. And then the inserted row appears in the table.
我在数据库本身中观察到了这种行为，我通过 OleDB 插入了行，并在 Access 中观察了打开的表.该行在 3-5 秒后出现在表格中.
I observed this behavior in the database itself, i inserted the row via OleDB and watched the opened table in Access. The row appeared 3-5 seconds later in the table.
Access 是否缓冲行?我是否必须通过 OleDB 发送刷新或提交等?
Does Access buffer rows? Do i have to send a flush or commit, etc. via OleDB ?
任何建议都会非常有帮助.
Any suggestions would be very helpful.
(请不要问我为什么不通过我的业务逻辑或者其他方式来解决这个问题……)
(Please don't ask why I don't solve this through my business logic or other ways....)
推荐答案
我做了一些测试，ACE OleDb 似乎确实有一些缓冲.我发现了
I did some testing and there does seem to be some buffering going on with ACE OleDb. I found that
如果我执行了一些 INSERT，然后立即在同一个 OleDbConnection 上执行 SELECT，则新行立即可用.
If I did some INSERTs and then immediately did a SELECT on the same OleDbConnection the new rows were available right away.
如果我打开两个 OleDbConnection 对象，在 con1 上插入，然后在 con2 上立即选择新行大约需要 5 秒才能在 con1 上可用code>con2 连接.
If I opened two OleDbConnection objects, INSERTed on con1, and then immediately SELECTed on con2 the new rows took approximately 5 seconds to become available on the con2 connection.
如果我打开两个 OleDbConnection 对象，在 con1 上插入，在 con1.Close() 上执行，然后立即在 con2 新行立即可用.
If I opened two OleDbConnection objects, INSERTed on con1, did con1.Close(), and then immediately SELECTed on con2 the new rows were available right away.
因此，关闭 OleDbConnection 似乎具有刷新"写入的效果，并使它们更快地可用于其他连接.我的猜测是 OleDb 正在隐式缓冲(或批处理")更新，因为 ACE/Jet 是一个共享文件数据库，并且它试图减少对数据库文件的争用.
So, it appears that closing the OleDbConnection has the effect of "flushing" the writes and making them available to other connections sooner. My guess is that OleDb is implicitly buffering (or "batching") updates because ACE/Jet is a shared-file database and it is trying to reduce the contention on the database file.
(当我使用 SQL Server 进行相同的测试时，我关闭 con1 的情况与打开它的情况之间没有显着差异.在每种情况下，新记录都可以立即使用.)
(When I did the same test using SQL Server there was no significant difference between the case when I closed con1 and the case when I left it open. In every case the new records were available instantly.)
这篇关于Access 2007 - INSERT 和即时 SELECT 不检索插入的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
