2

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#;

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

Error Image

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

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#;

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

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

Thanks!

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Rascio
  • 61
  • 6
  • I can't replicate that row limit for an xlsb file. Can you provide more details on it? Does it just return a limited amount of rows? – Erik A Mar 28 '18 at 21:15
  • When the sheet I'm querying has more than 65536 rows I get this error: [Error PrintScreen](https://i.stack.imgur.com/NzG6G.jpg) – Rascio Mar 28 '18 at 21:31
  • That's a VBA error, and I can't find that range name in your sample. Please provide a [mcve]. This is not complete, nor is it verifiable. – Erik A Mar 28 '18 at 21:32
  • I'm sorry, but I could not upload my own picture as I don't have enough reputation on the forum, but in the picture, instead of "HighRange", my error message reads "SheetName$A1:W100000". Pretty sure it is not VBA error for it works when I have 65536 rows with data and crashes when I have 65537 rows with data. – Rascio Mar 28 '18 at 21:37
  • If it's not VBA, then run it outside of VBA and provide the error. I'm not going to troubleshoot a VBA error without seeing any VBA... – Erik A Mar 28 '18 at 21:41
  • 1
    Isn't there something wrong if you have more than 2^16 rows? Shouldn't this data be stored in database linked to excel? https://stackoverflow.com/questions/24472183/excel-as-database-query-more-than-65536-rows – ComputerVersteher Mar 28 '18 at 22:33
  • Sales data is extracted from SAP, adjusted and modified, and then stored in Access (for BI applications). One year worth of data easily get bigger than 2^16.... – Rascio Mar 29 '18 at 00:59
  • Why not import to access from sap, then adjust in access or linked excel. – ComputerVersteher Mar 29 '18 at 01:15
  • Could you point me a direction for me to learn what a linked excel is and how it works? Could be a viable option! – Rascio Mar 29 '18 at 14:00
  • e.g Data tab -> external data -> from Access – ComputerVersteher Mar 29 '18 at 17:21

1 Answers1

2

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

and

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

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#;
Rascio
  • 61
  • 6