We have customers testing using EDB ODBC various Excel-based queries they originally created against our older DBISAM-based product. Inner joins are a problem - see SQL generated by MS Query below.

SELECT Orderh.REF, Orderh.COMPANY, ORDERI.LINE, ORDERI.PRODUCT, ORDERI.DESC1, ORDERI.QUANTITY
FROM "_DEFAULT".Orderh Orderh, "_DEFAULT".ORDERI ORDERI
WHERE Orderh.REF = ORDERI.REF

This runs at a truly glacial speed as EDB does not optimize SQL-89 style joins - from the EDB manual:-

<< ElevateDB does not optimize join expressions in the WHERE
clause, otherwise known as SQL-89 style joins. You must use
the JOIN clause in order to have ElevateDB optimize the
joins. >>

Is there any way to work around this to get some level of performance, short of the users having to edit their SQL directly? Are there any alternatives to MS Query for use with EDB and Excel?

Charles,

<< Is there any way to work around this to get some level of performance, short of the users having to edit their SQL directly? Are there any alternatives to MS Query for use with EDB and Excel? >>

Unfortunately, there's really no way around this other than to edit the SQL directly.  MS Query is just really, really old and hasn't been updated in years.

Tim Young
Elevate Software
www.elevatesoft.com

Charles


If most of the queries are similar to the one posted it wouldn't be overly difficult to interpose your own translator to give the more modern syntax.

Roy Lambert

Charles,

It is not too hard to substitute custom SQL in Excel, rather than use the default generated by MS Query, which is absolutely awful.

I usually start from the "Data" tab of excel, click on "Connections" and click "Add", from here you can create a connection to an EDB DB (provided the ODBC is installed), and fill in the SQL in the Connection Properties.

It is fiddly, and not obvious, but it all works and allows you to write the best SQL.

I do have a word document explaining how to do it in steps for my users if you want this I will post it to the NGs.

Adam Brett wrote:

It is fiddly, and not obvious, but it all works and allows you to write the best SQL.

I do have a word document explaining how to do it in steps for my users if you want this I will post it to the NGs.

Hi Adam,

That would be useful, thanks.

Though I suspect the people using MS Query like it as they don't have to roll their own SQL.

Charles,

My documentation skills leave a lot to be desired. Feel free to use the attached Word Document as you wish. It is not particularly well written or clear, but does show how to set up an Excel sheet and then link it to your own SQL.

The main point is that once a "Data Connection" is created, it is fairly easy to paste your own SQL into the Data Connection >> Definition >> Command Text.

You definitely need skills to write the SQL, you are right that customers may not have these. I usually provide the base SQL for my customers, and then give them tips and training on extending it.

Charles,

<< Though I suspect the people using MS Query like it as they don't have to roll their own SQL. >>

I'm looking into the new Power Query functionality in Excel to see if it will work as a substitute.  The only problem is that it looks like there's some issues with it and the EDB ODBC Driver, so I need to iron those out and then it should be a good substitute.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

I'm looking into the new Power Query functionality in Excel to see if it will work as a substitute.  The only problem is that it looks like there's some issues with it and the EDB ODBC Driver, so I need to iron those out and then it should be a good substitute.



Much appreciated Tim. We've also been looking Power Query too.