How to execute Mysql Queries in Ms Access 2007

Question

I want to run an mysql query in MS ACCESS 2007 using DoCmd.RunSql

DoCmd.RunSQL "update Table1 Join (select Table1.*, (@rn := if(@u = uname, @rn := @rn + 1, if(@u := uname, 1, 1) ) ) as rn from Table1 cross join (select @rn := 0, @u := '') params order by uname, id desc ) tt on t1.id = tt.id set Table1.flag = tt.rn;"

It showing Syntax Error.

When writing portable code, ANSI SQL is my number one tip. (The SQL Validator can quickly verify ANSI SQL syntax, developer.mimer.com/validator/parser200x/index.tml, a great time-saver when writing portable code.) — jarlh
– jarlh, Commented Jan 29, 2016 at 10:43

Gustav · Accepted Answer · 2016-01-29 11:15:20Z

2

You can't run MySQL syntax in Access this way.

The normal method to do that is to create a Pass-through query that connects to MySQL. It will pass the SQL directly to the server, thus this has to be of the dialect used by the server.

Or, of course, convert the MySQL syntax to Access SQL. Then DoCmd.RunSQL will work.

answered Jan 29, 2016 at 11:15

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute Mysql Queries in Ms Access 2007

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related