EXCEL ADODB查询本地工作表不包括新插入的记录 [英] EXCEL ADODB Query on local worksheet not Including newly inserted records

问题描述

我正在使用ADODB在Active工作簿中查询工作表中的数据。数据位于其自己的表单上，并具有列标题。我将表定义为一个excel ListObject - excel的自动表格格式化结构。

I am using ADODB to query data form a worksheet in the Active workbook. The data resides on it's own sheet, and has column headers. I've defined the table as an excel ListObject - excel's automatic table formatting construct.

我打开这样的连接：

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\" & _ 
   ThisWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon

然后我可以使用简单的SQL语句来获取记录集： / p>

Then I can fetch a recordset using a simple SQL statement:

strSQL = "SELECT * from [sheet1$]
rs.Open strSQL, cn, 0, 1  'cursortype = adOpenForwardOnly, locktype = adOpenReadonly

这一切都很好，直到我在表中插入一个新行在sheet1上，即使关闭，设置为无，并重新打开代码中的连接和记录集变量，新行也不包括在后续查询中。

This all works fine... until I insert a new row in the table on sheet1. The new row is not included in subsequent queries, even if I close, set to nothing, and re-open both the connection and recordset variables in my code.

如果我保存并关闭工作簿，然后重新打开它，新的记录被包含在查询中，这导致我相信这可能是一个缓存问题。我搜索过ADODB缓存刷新等等，但大多数结果似乎与PHP或Access有关，我还尝试了各种其他选项，用于游标类型和锁类型，没有区别。

If I save and close the workbook, and then re-open it, the new records ARE included in the query, which leads me to believe this might be a caching issue. I've searched for ADODB Cache Flush etc, but most results appear to be related to PHP or Access. I've also tried a variety of other options for Cursor Type and Lock Type, with no difference.

任何人都可以建议我如何可以确保每次运行我的查询时，我得到所有的行，e在表格中插入新行后，是否会出现？

Can anyone suggest how I can ensure that each time I run my query I get all the rows, even after I insert new rows in the table?

推荐答案

找出解决方案：

由于我使用Excel 2010，我发现我可以使用较新版本的ADODB。

Since I'm using Excel 2010, I discovered that I can use a newer version of ADODB.

所以，而不是像这样定义我的连接字符串：

So, instead of defining my connection string like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="...

我将其更改为：

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="...

问题已经解决了。新插入和编辑现在在我制作之后立即显示。这也消除了OLEDB.4.0中已知内存泄漏的问题，这是一个很大的好处。

and the problem is solved. New inserts and edits are now showing up immediately after I make them. This also removes the issue of the known memory leak in OLEDB.4.0, so that's a bonus.

这篇关于EXCEL ADODB查询本地工作表不包括新插入的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！