sql query causing error 2950

Question

Hello,

This SQL query was generated by Microsoft Access 2003, and works fine when run, but fails when trying to run from a Macro. Is there any obvious error within the query, or any reason it would not work?

SELECT tblAuction.article_no, tblAuction.article_name, tblAuction.subtitle, tblAuction.current_bid, tblAuction.start_price, tblAuction.bid_count, tblAuction.quant_total, tblAuction.quant_sold, tblAuction.start, tblAuction.ends, tblAuction.origin_end, tblUser.user_name, tblAuction.best_bidder_id, tblAuction.finished, tblAuction.watch, tblAuction.buyitnow_price, tblAuction.pic_url, tblAuction.private_auction, tblAuction.auction_type, tblAuction.insert_date, tblAuction.update_date, tblAuction.cat_1_id, tblAuction.cat_2_id, tblAuction.article_desc, tblAuction.countrycode, tblAuction.location, tblAuction.condition, tblAuction.revised, tblAuction.paypal_accept, tblAuction.pre_terminated, tblAuction.shipping_to, tblAuction.fee_insertion, tblAuction.fee_final, tblAuction.fee_listing, tblAuction.pic_xxl, tblAuction.pic_diashow, tblAuction.pic_count, tblAuction.item_site_id
FROM tblUser INNER JOIN tblAuction ON tblUser.id = tblAuction.seller_id;

Answer 1

Did you mean from a Access Macro or from VBScript or from VBA? If you have a macro that is invoking an action then my recommendation would be to convert it to a VBA statement. I assume when you say it works fine when run you mean run as an Access query. You don't specify whether the database is local or is a remote one (i.e. SQL Server). Assuming it is local then running that SQL statement from VBA must work.

Answer 2

This article is for Access 2007, but perhaps you're experiencing this as well.

What's the security level you're using in Access? (Open an MDB, then go to Tools\Macro\Security...). Try lowering the security level and see if that helps.

Answer 3

I noticed that you were using key words like INSERT and UPDATE as parts of you field names. Such words might be analysed as SQL instructions when sent through a macro, leading to a bug.

Can you try to put them between [brackets]? Anyway it would be better to change the fields names.