OLEDB连接支持超过65536行(从Excel工作表更新访问) [英] OLEDB Connection to support more than 65536 rows (Update Access from Excel Sheet)

问题描述

我可以使用以下SQL命令从Excel工作表中选择要插入到Access表中的数据:

I can use the following SQL command to select data from an Excel sheet to be inserted into an Access table:

SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=K:\FolderName\FileName.xlsb].[SheetName$A1:W100000] WHERE Data=#01/01/2018#;

但是此方法仅限于旧版本的excel中的65536行...这是我得到的错误:

But this method is limited to the 65536 rows from old versions of excel...this is the error I get:

我应该如何修改此代码以允许更多行?

How should I adapt this code to allow for more rows?

我尝试了以下代码，适合使用ACE.OLEDB

I tried the following code, adapted to use ACE.OLEDB

SELECT * FROM [Microsoft.ACE.OLEDB.12.0;Excel 12.0;HDR=YES;DATABASE=K:\FolderName\FileName.xlsb].[SheetName$A1:W100000] WHERE Data=#01/01/2018#;

但是出现错误:找不到可安装的ISAM"

but I get the error: "Cannot find installable ISAM"

如何使用相同的括号格式正确指定较新的ACE.OLEDB?

How do I correctly specify the newer ACE.OLEDB using the same bracket format?

谢谢！

推荐答案

好吧，如果有人遇到同一问题:

Well, if somebody else comes accross the same problem:

此处报告的问题是查询范围时发生的已知错误:

The issue reported here is a known bug that happens when a range is queried: Problems in Excel 2013 when using ranges that extend beyond row 65536

和

https://chandoo.org/forum/threads/excel-recordset-only-returns-65536-rows-if-you-try-to-pull-data-from-a- range.12492/)

要绕过它，解决方案是使用"[SheetName $ A:W]"而不是"[SheetName $ A1:W100000]"查询范围

To bypass it the solution was to query the range using "[SheetName$A:W]" instead of "[SheetName$A1:W100000]"

SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=K:\FolderName\FileName.xlsb].[SheetName$A:W] WHERE Data=#01/01/2018#;

这篇关于OLEDB连接支持超过65536行(从Excel工作表更新访问)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！