the following statement works in Access 2002 but not in Microsoft.Jet.OLEDB.4.0 (in Asp Page)
SELECT dat
FROM [SELECT t_rid as klant, t_dat as dat FROM tijden WHERE t_rid=1663
UNION
SELECT c_rid as klant,c_dat as dat FROM Contact WHERE c_rid=1663
]. AS tmp LEFT JOIN klanten ON [tmp].klant = klanten.k_uniek

in JET it produces:
Database not available
![-2147217900]Syntaxerror(operator missing) in query-expression dat
.

All my other SQL statement do work on both!
Anyone any clue where JET is missing the boat?

Thanks in advance
Ton

http://support.microsoft.com/default...b;en-us;181489

But if this is a cut & paste of the query

try putting a space after the comma and see if it works.

BTW, where is your ending semi-colon? Or am I being an idiot?

Try this:

SELECT dat
FROM (SELECT t_rid as klant, t_dat as dat FROM tijden WHERE t_rid=1663
UNION
SELECT c_rid as klant,c_dat as dat FROM Contact WHERE c_rid=1663) AS tmp
LEFT JOIN klanten ON tmp.klant = klanten.k_uniek


Note that there are no square brackets and no dot before the AS. Jet seems not to like them.
I've had several problems translating ACCESS queries to JET to use them in Delphi applications. The syntax is not just the same!

www.antartidasistemas.com

Just to clarify things a little:

Access 2002 and Microsoft.Jet.OLEDB.4.0 both are accessing the JET, but with different methods.

Access 2002 uses DAO to work with JET and allows Access SQL syntax in queries.

Microsoft.Jet.OLEDB.4.0 is an OLEDB "open" driver that talks to JET ans requires ANSI SQL syntax not Access SQL. The ideas is that you can write one SQL statement that can be used by any backend database but just changing the OLEDB "driver".

Your error probably not from JET but from the OLEDB layer.



Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

I've got the same problem. When trying to Inject an SQL command on a site that runs ODBC (just checking vulnerability, not going to do any harm) this runs just fine:

UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--

But when I try to use it on a site that runs JET it returns me this error:

Microsoft JET Database Engine error '80004005'

Syntax error on the clause FROM.

/encuestas.asp, line 111

I translated the error as it was in spanish.

Should I change de command? What should I do?