Re: Why does Access and Excel use different SQL formats?

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.




"Jim in Spokane" <JiminSpokane@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:66D570D6-CAE7-4AE0-A446-A94002FF928B@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • RE: Excel 2007 is a Joke
    ... I have an app that generates about 100 large spreadsheets. ... The problem is caused by the treatment of CSV files using Excel 2007. ... I did contact Microsoft support on this issue and sent the consultant my ... This post is a suggestion for Microsoft, ...
    (microsoft.public.excel.programming)
  • Re: Is it possible to load the excel file by using external table without converting it into csv com
    ... I don't like VB, I don't like microsoft, but sometimes, it can be useful ... to do quick prototyping or to hack something with word or excel. ... I think "hack" is the appropriate word. ... SQL> SELECT * FROM ABC@xxxxxxxxx; ...
    (comp.databases.oracle.server)
  • Re: ODBC connection to mdb for MacIntosh
    ... You can take the data that is input and use SQL commands to update Microsoft Access tables. ... You will need to learn some visual basic and some SQL, neither of which is trivial, but both subjects are not too difficult to master for most people. ... If you do a Google search in the excel macintosh newsgroup for Access you'll find a lot of information about why there's no Access for Macintosh. ...
    (microsoft.public.mac.otherproducts)
  • Re: Secure OleDb Connection to a Microsoft Excel Workbook
    ... I realise that Excel isn't a database, but I am unable to use Microsoft ... SQL or the like as the users in the organisation do not have ... The only software standard on the machines is Microsoft Word, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: controling the spread of fields in the excel
    ... Dim SQL As String ... DoCmd.TransferSpreadsheet acExport,, qryExportQuery ... Microsoft IT Academy Program Mentor ... I'm trying to find a way to export the data in the form into excel. ...
    (microsoft.public.access.forms)