Is there a Limit to where you can place a table to query it from ADODB?

[brusk]

I have a workbook with about 80 Tables in it. Most of the tables on a single sheet that I use to save configuration data from multiple userforms. I haven't encountered any major issues accessing the tables until tonight I inserted 3 new tables I'm playing with. All my tables start at Row 20 and just keep getting created increasing the width of the sheet. Now any table new or existing past Column IV I get an error running basic queries.

Error:
Run-time error '-2147217865(80040e37)':
The Microsoft Jet database engine could not find the object 'ConfigurationData$IW20:JE21'. Make sure the object exists and that you spell its name and the path name correctly.

Code Snippet for the query:

Call RunSQLQuery(ThisWorkbook.Name, "SELECT * FROM [" & GetTableRange("cnfTableUIPChat") & "]", arResults, True, False)

Translated query when it hits that sub:

SELECT * FROM [ConfigurationData$IW20:JE21]

I can change it to a different table at IC20:IU21 with no errors.

[brusk]

Just for kicks I created a 5 field test table further left on the sheet and made sure I could query it. I moved it over to where these tables are and it stopped working. I moved it back and it worked, so I started moving it cell by cell and it started giving errors and column IX.

Not sure if there's any way around this or if I need to see if I can stack a few of the same size tables or move some of them off to a new sheet.

[SamT]

My guess is that it has to do with compatibility with earlier Excel's 256 column limit. IV = 256 base26. Avoid columns IT - IZ. at the table edge. Honestly :dunno

My guess is that if you cut a table from one sheet and paste it on another sheet, it will not break your code. I think it depends on the name protocol you used.

I am thinking of the protocols for naming ranges. If, at the time of name creation, you preceed the Range Name with the sheet's name and exclamation mark (exactly like a formula referring to another sheet
'SheetName'!RangeName that name is specific to that sheet and you can reuse the RangeName on another sheet by using that sheet's name before the exclamation mark.

Only one copy of a Range Name in a workbook can exist without the SheetName before it.

The other consideration is
How many Millions of Rows does your sheet have?

Can you use Transpose in the code and turn the tables on their sides?

There are many ways to skin a catfish.

[brusk]

The tables are setup as ListObject style tables. This way I can stack like tables and just insert the records into them so that the sheet is automatically resize and I do not have to worry about formatting of headers and such. I looked over my connection string and did some more research. I tried switching over to an ACE connection so I can specify a newer version of Excel but it looks like it has the same limitation of 256. I guess will probably have to play around with the data and see if I can stack more tables on top of each other and make more room.