Field Name Error with ADO?

[cefrancke]

Can anybody tell me why I get "Automation Error/Unspecified Error" from
the following ADODB.recordset open method? (Access 2003) (ADO 2.8
Library referenced)

rs.Open "SELECT MyTable.Size FROM MyTable

or

rs.Open "SELECT Size FROM MyTable


This works, however...
rs.Open "SELECT [Size] FROM MyTable
rs.Open "SELECT [MyTable].[Size] FROM MyTable"
rs.Open "SELECT [MyTable.Size] FROM MyTable"


Any Ideas?

 

[Douglas J Steele]

I don't have a list of ADO reserved words handy, but I suspect that Size is
probably on that list. If changing the field name is out of the question, at
least you have a work-around. (for what it's worth, MyTable.[Size] would
work as well)

 

[cefrancke]

Thanks Doug,

That's what I was thinking too. It would be amazing if this is not a
hot topic among engineering/science database power users and
programmers. "Size" is a term very common in those industries. However,
I must admit that I dont recall using it anywhere, yet, till now.

Note: All these SQL statements work in the QBE. I usually confirm the
SQL in the QBE before I code it.

Wouldn't a "reserved" word suggest that one could send "messages" to
ADO through the SQL statement, instead of just ADO parsing like the
QBE? What I mean is, ADO does more with the SQL than simply parse and
pass the SQL on to JET?
I did not realize that ADO was sensitive to things other than SQL
syntax errors in that ADODB.Recordset.Open method.

Would it be recommended to stick with DAO for Access VBA (to internal
Access or external Access databases) and save ADO for external calls
(to non-Access databases) or external programming IDE's?

TIA

 

[Douglas J. Steele]

For querying Jet databases (i.e. MDB files), DAO is preferred.

I'm not sure I understand your comments about reserved words and sending
messages.

 

[cefrancke]

I figured that, the rs.Open call parsed the Source parameter for
standard SQL errors, not ADO reserved words and such.

Thanks for your comments, it's a big help.

 

[Brendan Reynolds]

I haven't been able to find a list of ADO or OLEDB reserved words, but
'Size' is a SQL-92 reserved keyword (they're listed in SQL Server Books
Online) so it *may* be that ADO is doing exactly that - adhering to the
standard - the SQL-92 standard, that is.