using ADO for database in sheet

[Guest]

hi,

Despite MS said that ExcdlADO should be reference in the following format:

strSQL = "SELECT * FROM [Sheet1$B1:C20]"

But you should also try:

strSQL = "SELECT * FROM [DATABASE$B1:C20]"

where DATABASE is the name of Sheet9. I don't know how I have programmed,
(Using Jet database), but I couldn't get the 1st scenario working, and got
the following Run-time error:

Run-time error '-2147217865 (80040e37)':
The Microsoft Jet Database could not find the object 'Sheet1$B1:C20'. Make
usre the object exists and that you spell its name and the path correctly.

If someone know about what cause this behaviour, please let me know.

Regards
Augustus

 

[Jamie Collins]

Despite MS said that ExcdlADO should be reference in the following format:

strSQL = "SELECT * FROM [Sheet1$B1:C20]"

But you should also try:

strSQL = "SELECT * FROM [DATABASE$B1:C20]"

where DATABASE is the name of Sheet9. I don't know how I have programmed,
(Using Jet database), but I couldn't get the 1st scenario working, and got
the following Run-time error:

Run-time error '-2147217865 (80040e37)':
The Microsoft Jet Database could not find the object 'Sheet1$B1:C20'. Make
usre the object exists and that you spell its name and the path correctly.

I'm a bit confused here. Is you worksheet named DATABASE, Sheet1 or
Sheet9? It can't be more than one. I'll assume it is named DATABASE.

Which provider/driver are you using? If you are using the odbc driver,
success may depend of the way the data is organized. For example, say
your UsedRange, being the part of the sheet that contains or has
contained data and therefore determines the extents of the 'table' for
the sheet, is A1:A3. If you then queried the sheet using

SELECT * FROM [DATABASE$B1:C20];

the specified Range is outside of the table extents and the odbc
driver throws an exception; the description I get via the OLE DB
provider for odbc is:

[Microsoft][ODBC Excel Driver] This table contains cells that are
outside the range of cells defined in this spreadsheet.

In the same circumstance using the OLE DB provider for Jet 4.0, I get
no error and two columns, F1 and F2 with three null rows i.e. the
number of columns corresponds to the number of columns requested and
the number of rows is consistent with the UsedRange.

In conclusion, the Jet 4.0 provider is more flexible and can be useful
for querying within or outside of the usual bounds of a sheet's
'table'.

Jamie.

--

 

[Guest]

Sorry for the confusion, sheet9 should be sheet1, and DATABASE is the name
for Sheet1.
I remember I read somewhere that you cannot use the name of a sheet if using
Jet4 for Excel. I tested it out, which is true. But when I code the
application that I am doing now, I have to use the name and not sheet1.
To clarify, Sheet1 = DATABASE , when I view them inside Visual Basic Editor.

Thanks
Augustus

 

[keepITcool]

your test leads to wrong conclusions.

you MUST reference the sheetname else you'll retrieve the data from the
sheet that was active when the file was saved.

that just might to unexpected results <g>

also your clarificaiton is again "multiinterpretable"

sheet1=database... VERY logical.. or do you mean to say
the first sheet is named "Database"
then nineth sheet is names "Sheet1" ??




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


augustus wrote :

 

[Guest]

(Say you name your Sheet1 to Database, but when you open up VBE, under
Project Explorer, you see it as "Sheet1 (Database)".

Also, I realised something too, the reason why the test code work is because
I didn't name any of the sheet.

 

[keepITcool]

dont confuse the .Codename with the .Name property

when you use Ado you can only use the .Name
(which is the one you see on the tab in Excel
and see inside the brackets in VB project explorer

 

[Jamie Collins]

your test leads to wrong conclusions.

you MUST reference the sheetname else you'll retrieve the data from the
sheet that was active when the file was saved.

keepITcool,
Sorry, your test also leads to wrong conclusions <g>.

If you omit the sheet name e.g.

SELECT * FROM [A:A]

the worksheet queried will be the first i.e.
ThisWorkbook.Worksheets(1), even if it is hidden.

You can also omit the sheet name if you are using a workbook-level
defined Name, of course <g>.

Jamie.

--