Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 9th, 2005, 09:49 AM
Registered User
 
Join Date: Aug 2005
Location: , , Netherlands.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL-Union works in Access but not in Jet

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
Reply With Quote
  #2 (permalink)  
Old August 10th, 2005, 02:54 AM
Registered User
 
Join Date: Aug 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

But if this is a cut & paste of the query

Quote:
quote: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
try putting a space after the comma and see if it works.
Reply With Quote
  #3 (permalink)  
Old August 10th, 2005, 03:00 AM
Registered User
 
Join Date: Aug 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

BTW, where is your ending semi-colon? Or am I being an idiot?
Reply With Quote
  #4 (permalink)  
Old October 13th, 2005, 07:39 AM
Registered User
 
Join Date: Oct 2005
Location: Linares, , Spain.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old October 16th, 2005, 01:42 PM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old February 20th, 2006, 04:37 PM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Union query in Access?? lisabb Access 5 October 24th, 2007 10:04 PM
Insert into error with union query in Access 2002 roniestein Access 8 December 21st, 2004 07:47 PM
JET database Engine and Access georgizas Classic ASP Databases 2 November 3rd, 2004 03:52 AM
Union joins in SQL sellis Access 3 April 7th, 2004 12:25 AM



All times are GMT -4. The time now is 06:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.