A Simple MySql update query

Can you update the record in the linked table when using the set where the query fails?

I'm sorry OxonDev, I'm  not sure what you meant by that

Are you trying to rename table1 or update data in table1?
The UPDATE command updates data in table records and does NOT rename tables.
If you want to rename the table (which I would if I had a table named "table1" :p ), the command in MySQL is

RENAME TABLE table1 TO new_name;

If you need more help, please clarify what you are actually trying to do.
HTH,
Lee

Can you manually update a record in the linked table rather than using a query.  If you can't the table is read only and you have a permissions issue.  Lee555J5 also has a point in that do you really have a column in table1 called name? Name is a property of the table (and therefore a reserved word) and you'd run into problems.

I'm not trying to rename the table. I'm trying to update the table

First, change the MySQL table1 "name" field to something other than "name" such as FirstName, LastName, FullName, CompanyName, etc. "name" may work sometimes; but it is a reserved word, and you are begging for problems.
Second, the statement should read

UPDATE table1 SET newnamefield = 'New Name';

As it is, this is a very dangerous statement. You should limit the records this affects by using a WHERE clause as in

UPDATE table1 SET newnamefield = 'New Name' WHERE city = 'Chicago';

Otherwise, you will update every record in table1.
Also, watch your quotes - ' vs ", as different DBMSs treat them slightly differently.
Your specific problem could have been the field named "name" or using "SET table1.name" instead of simple "SET name". You should not say "table1.name" here because you are already telling the UPDATE statement which table.
HTH,
Lee

try this

UPDATE table1 SET table1.[name] = "New Name";

and make sure table1 is actually a table in MySQL (not a view)
and make sure you have permissions...

when you open table1 from access, can you browse and edit data?

HainKurt
I tried your statement, but I'm still getting the error message.

I'm not able to to edit the table1 when I open it up.

Where do I set the permission; phmMyAdmin? ODBC Data Adaministrator settings?

The permissions to edit table1 records are going to be set in MySQL.  I'll assume you'll be logging on with a specific account (in the DSN you created to add the linked tables).  Make sure that account has the necessary table permissions...

can you try to add another table (add link, open) and edit that one?
maybe the problem is on table1 only...

Your problem is most likely that you haven't specified which field is the unique ID of the table. Relink the table and do this. If not, ODBC cannot update or create records.

/gustav

I've tried adding another table with 3 fields
fld1 - One index field
fld2 - one unique field
fld3 - one regular varchar(10)

I could now add a record to it, but not update or delete an existing record.
This table shows the following, instead of the actual record

fld1                       fld2                       fld3
#Deleted      #Deleted      #Deleted
#Deleted      #Deleted      #Deleted
#Deleted      #Deleted      #Deleted

It seem like the field attribute, when the field are created has control how MS access behave, and I'm not an expert to say that is a fact. Need some export advise on this.

Thanks

What numeric data types are in use?
Only Byte, Integer, and Long, but not BigInt, is supported by Access.

/gustav

Here is the attribute of the fields. 2 fields are text and one is varchar(10)

Field  Type        Collation        Attributes        Null        Default        Extra        Action
fld1         text       cp1257_general_ci       No             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
fld2        text       cp1257_general_ci       Yes       NULL             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
fld3        varchar(10)       cp1257_general_ci       No                   Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext

I'm sorry the heading did not matched the column. Here is the correct info.


Field  Type              Collation                           Attributes        Null        Default        Extra
fld1         text              cp1257_general_ci                              No            
fld2        text               cp1257_general_ci                              Yes          NULL            
fld3      varchar(10)  cp1257_general_ci                              No                  


Keyname        Type        Cardinality        Field      Length
fld2             UNIQUE         None                   fld2         2
fld1                   INDEX        None               fld1                5

> Operation must use an updateable query.

Well, somehow the primary key of the linked table is missing.

/gustav

Answer was partially correct