Harlan Grove wrote... ...
It's a registry key:
Hkey_current_user\Software\ODBC\ODBC.INI\Excel
Files\Engines\Excel\FirstRowHasNames
where 0 (zero binary) is FirstRowHasNames=No and 1 (one binary) is
FirstRowHasNames=Yes.
One *should* be able to override the registry setting in the
connection string i.e.
DSN=Excel Files;DBQ= C:\foo\bar.xls;FirstRowHasNames=0;
However, as is all too common with drivers, there is a bug:
Microsoft Knowledge Base Article - 288343
BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header
Setting
http://support.microsoft.com/default.aspx?scid=kb;EN-US;288343
So you have to manually or programmatically toggle the registry
settings, easy to do but permissions can be a problem. Anyhow, it
means that if your cell formulas are relying on column headers being
present you should be checking that the registry setting for the
current user will yield the behavior you expect. Could be even more of
a pain that toggling it yourself.
...
Right. So as a practical matter if one wants to avoid VBA, one must either set
this registry value to 0 or 1 and adapt all SQL.REQUEST calls to the setting. If
it defaults to first row having names, then since I didn't tell the OP to go
changing registry settings, it was appropriate to tell him/her to make sure
there were field/column names in the top row.
Because we're not talking in theoretical terms. It strikes me as more
pragmatic to use 'column', 'row' and 'table' when talking about Excel
data because they have some meaning to Excel users, 'fields',
'records' and 'relations' less so. It's just convenient to my argument
that 'column' and 'row' are also used in RDBMS theory. I regret that
the Jet, MS Access and SQL Server documentation use 'field' and
'record' because this seems to be theoretically and technically
incorrect (depends on whether you consider Jet to be a file-based ISAM
or a true DBMS: the use of 'field' and 'record' suggest the former but
I'm sure MS would like us to conclude the latter so why the
terminology? Beats me).
Intruding a bit of history, breadth and low level representations into this
discussion, fields and records are terms that predate computing - see
http://www.cs.uiowa.edu/~jones/cards/history.html
Codd intentionally used different terminology in laying the mathematical
foundation for the relational model, but everyone who had used databases prior
to that knew that columns = fields and rows = records. If we're worried about
what terminology would make the most sense for newer/younger users, if they have
any programming experience, they know about composite mixed type data
structures, which are called records in COBOL and Pascal, structs in C/C++
(classes are something else), and types in VB[A]. Most scripting languages,
certainly awk and Perl, use field/record terminology. And if users have any
experience with, um, record keeping they may also be familiar with field/record
terminology.
Then there's your equivalence of ISAM with field/record terminology. The
relational model doesn't specify storage or access mechanisms, and a fair number
of RDBMS tasks are performed at low level using ISAM. Then there's the fact that
computer memory is one big linear, single dimension expanse. In memory there are
no columns and rows, just offsets from various base memory addresses.
Fields and records are colloquially equivalent to columns and rows in the
relational model even if that model distinguishes between them.
I use neither, and don't doubt your expertise on this, but assume they
could be programmatically refreshed in e.g. a Worksheet_Calculate
event handler.
Yup. And if the goal were database access, then ADO would make more sense still.
However, if one wants to avoid VBA, which is often the case, one can't use event
handlers.
I can offer no evidence that it is. I *think* I've seen it mentioned
(by Jake Marx?) in this ng but I can't find any Usenet threads in the
google archive to support this. Querying an open workbook using ADO
results in a demonstrable memory leak; I've previously done some
testing with ODBC and could detect no equivalent memory leak. I will
offer that problems could arise because there is no way AFAIK of
effectively specifying database-style lock type, cursor type, etc for
an Excel workbook so querying and especially executing SQL against an
open (and potentially modified and unsaved) workbook may yield
unexpected results.
Good point. It's still a design trade-off - reliability of data refresh on
recalc vs reliability in terms of avoiding the nastier aspects of such a bug. If
the workbook would be open for long periods and recalced often, then such a bug
may argue against using SQL.REQUEST against open workbooks. If the workbook
wouldn't be open for long periods or the SQL.REQUEST calls wouldn't recalc
often, then such a bug may not affect normal use of the workbook.
With regard to cursors, not an issue. SQL.REQUEST would return full query
results. Cursors wouldn't/couldn't be used. With respect to table locking, not
an issue querying a workbook open in the same Excel instance since the normal
recalc sequence would effectively lock the table during SQL.REQUEST access.
However, I suppose there could be mucho nastiness if there were any circular
references between cell formulas in the table and SQL.REQUEST results. That
could be dangerous.
I have to conclude by saying that anyone (like me) who got used to 123's @DSUM
criteria expressions as opposed to criteria ranges and its ability to operate on
multiple tables via effective inner joins just has to laugh (ruefully) at
Excel's archaic DSUM criteria ranges. SUMPRODUCT formulas are usually a better
approach, but they're inefficient, cumbersome and complicated. SQL.REQUEST, if
there were no or not too severe memory leak bugs, is a far more elegant way to
achieve the same results.
], I'd argue that 'column' and 'row' have