Why is Excel and Access SQL different?

[Guest]

Excel and Access differ in the way SQL looks at tables to pull information
from a database. Why are these not interchangeable so the same statement can
be used in both.

 

[Guest]

What difference are you referring to? Hard to answer the question without
knowing more specifically where you are noticing the difference. However,
the bottom line: Access is a database, Excel is not. Excel does not have a
real table structure, the data types are more limited, there is no provision
for indexing fields, ... Excel can act somewhat like a database, but the
SQL interpreter will have limitations due to these structural differences.
Then, on the other side of the spectrum, Access implements some non-standard
SQL syntax and functions. Access is not unique in bending the SQL standards
- it is the curse of any standard in computing: sticking strictly to the
defined standard ensures compatibility and portability but at the expense of
function. Having added functionality customized to a particular database
engine leads to compatibility issues...

 

[Guest]

Hi Blackhorse

It is more do to the fact that Access is a Rational Database set
up with certain database rules, and Excel is a spreadsheet.
It's more of a storage thing, in Access you store your data
the way Access wants you to store it and in Excel you store
it the way you want to store it.
Access


Good Luck
TK

 

[Jamie Collins]

Excel and Access differ in the way SQL looks at tables to pull information
from a database. Why are these not interchangeable so the same statement can
be used in both.

I don't get what you mean. When the data source is Excel, the SQL
engine is Jet, which what I assume you mean by 'Access'. Assuming the
same version of Jet (engine and provider/driver) then the syntax should
be the interchangeable between Excel and MS Access. The way Jet
identifies tables and data types is different for Excel but this is a
practical necessity rather that a syntax deviation. Please give an
example of non-interchangeable syntax.

P.S. I've just had a thought: when you say 'Excel' do you mean MS
Query? MS Query was designed to be used with a variety of data sources,
not just MS Jet/Access. It has its own quirky SQL syntax so it can
support its own functionality, most notably parameters (I suppose so it
can parse the SQL easily for the ? placeholders). If you don't like the
syntax you can alternatively use SQL that is used by your data source
(e.g. Jet SQL) and it should be passed untouched to the source to be
executed there. However, when you do this you lose MS Query
functionality such as parameters.

Jamie.

--