Join expression not supported - works in MA scces but not via ADO

A

Acie

I'm writing a VB code with ADO to execute an sql that was copied from MS
Access 2003, after it was sucessfully tested. However, when I run this code
from an ADO routine, I got the above subject error message. It was run
agaisnt MS Acccess 2003 db.

Basically the query joins two tables (left join), checks if a curency is not
in USD and converts it and also includes a few other columns and stores it
into a new table.

Again this works fine when running it in MS Access 2003.

code sample below: code works fine & it is imbedded properly.
sql = "SELECT SEC_ID, ID_ISIN,
IIf(IsNumeric([EQY_DVD_HIST_ALL]),IIf([dvd_crncy]<> "USD" &
[exchange_rate]*[nbr2],[my_nbr]),Null) AS nbr_converted
Into mytable_temp FROM tbl1 LEFT JOIN tbl2 ON DVD = CODE;"

I have "USD" surounded by chr(34)...so it's fine.
....
cmd_sql.CommandText = sql
cmd_sql.Execute

Thanks for any ideas or thoughts ? Is it perhaps ADO that does not allow
this ?
 
A

Alex Dybenko

hi,
try to put CODE in square brackets, perhaps this is a reserved word:
LEFT JOIN tbl2 ON DVD =
Code: 
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

join expression not supported..run in Access 2003 but not in ADO ? 1
Query in VB access returns less records than via DAO ? 7
Ado Recordset updatable in Code but not via Form 3
expression in SQL works in control property but not in VBA 4
left join via ado 3
Left Join query not working in Access 2010 0
Join expression not supported with outer joins 5
Retrieve data by Self join via SQL under MS access VBA 1

Top