Why does Access and Excel use different SQL formats?

[Jim in Spokane]

Anyone know why Access uses a different format than Excel for SQL?
When I copy and paste one SQL Statement from Access to Excel I get an error
and vice versa. Seems odd to me that Microsoft would use a different format.

Access SQL View:
SELECT SYSADM_GJ.ID, SYSADM_GJ_DIST.AMOUNT, SYSADM_GJ_DIST.AMOUNT_TYPE,
SYSADM_GJ_DIST.BATCH_ID, SYSADM_GJ_DIST.POSTING_DATE
FROM SYSADM_GJ INNER JOIN SYSADM_GJ_DIST ON SYSADM_GJ.ID =
SYSADM_GJ_DIST.GJ_ID;

Excel SQL View:
SELECT GJ.ID, GJ_DIST.AMOUNT, GJ_DIST.AMOUNT_TYPE, GJ_DIST.BATCH_ID,
GJ_DIST.POSTING_DATE
FROM SYSADM.GJ GJ, SYSADM.GJ_DIST GJ_DIST
WHERE GJ_DIST.CURRENCY_ID = GJ.CURRENCY_ID

Thanks!
Jim

 

[Douglas J. Steele]

Looks to me as though no relationships were known to Excel, so that it
didn't use an Inner Join.

FWIW, both syntaxes should work in both products.

 

[Ed Adamthwaite]

I have recently done a job for a company that needed to use Microsoft Query
within Excel.
MsQuery must be another app that Microsoft bought in holis bolis. Or it
could be that the Excel team never speaks to the Access team. "The left hand
doesn't know what the right hand is doing."
Hence the differences with SQL in Access. I thought it would be a quick job
but no! Ms strikes again! Just call me "Under-Quote-ED"
It varies depending on the datasource. Access, Excel, SQL Server etc.
Some differences I discovered:
MsQuery doesn't seem to like hard returns within the different clauses of
the SQL string or double-quotes as string delimiters, they must be
single-quotes.
If you are querying a single table, the FROM clause repeats the table name.
"FROM tblMyTable tblMyTable"

It beats my why Ms doesn't just use the Access QDE to in a different app
that can use different datesource interfaces. The Access QDE is a fairly
reliable and robust tool that is easy to use. It's dumb to re-invent the
wheel each time a new app is created.

I hope part of this helps, I just had to have a gripe about my experience
with Excel + Ms Query.
Regards,
Ed.

 

[blvda via AccessMonster.com]

This is M$ ! Even the day of week between 1st of January 1900 and 1st of
March 1900 aren't equal if you compare Excel and Access. So how would you
believe a complete language would be the same ;-)

 

[missinglinq via AccessMonster.com]

Even Visual Basic and Visual Basic for Applications isn't 100% compatible!

 

[Ed Adamthwaite]

Hmm, sure, but the syntax is pretty much the same. VBA is latched onto
another app, so doesn't have to work on it's own. VB is for creating
stand-alone apps that may or may not be linked to another application.
I have no problems with that. Different apps all have slight differences
with their objects and methods.
I was talking about how the SQL is parsed and treated by Ms Query.
I admit that there are differences between T-SQL and Access SQL. Access has
a lot of extra stuff to make it easier for beginners. There are enought
different types of SQL already without MsQuery requiring it's own.

On another subject, why bother with Crystal Reports when the Access report
writer is so good? If it had been split off to work with data from other
datasources, it would have been a much less tiresome upgrade path to more
serious apps. Crystal is better now, but it was an absolute dog in VB6 days.