Update MySQL database with Excel using Microsoft Query

[ABL_Leblanc]

Hi,

I'm using Excel and a MySQL database to store data from an AB PLC.
Using Microsoft query, i can see data from the database in Excel but I can't change it in a dynamic way.

I've modified the command text (in Data-->Connection-->Properties-->Definition - or something like that ... My Excel is in french ).
I've remplaced the "SELECT table_0.field FROM database.table table_0" by "UPDATE database.table table_0 SET table_0.field='I3' "

This allow me to update all of the lines of the table by the characters "I3" but I would like to be able to enter a value in Excel and update a line of the table with this value.

Even if my problem is'nt directly related to PLC, may be someone have already done this before ?

Thanks for any help .


Ps : Sorry for my english

 

[Andybr]

If you import a table into Excel using Microsoft Query the default setting gives you read only access. If you open the table using the full MS Query rather than the Wizard you can select "Allow Editing" from the Records menu which will allow you to edit records directly in Excel.

 

[ABL_Leblanc]

Ok, so I can't modify the data directly in the Worksheet ? Or is there a way to integrate the full Microsoft Query functionnalities ?

PS: I've uploaded a screenshot with the MS Query window to be sure that we talk of the same thing .

 

[ABL_Leblanc]

I did it !

For those who could have the same problem as me :

You need to use Visual Basic. This webpage will help you :

Don't forget to change the driver version
Ex : Cn.Open "Driver={MySQL ODBC 5.2 Unicode Driver};