A Simple MySql update query

Hi-
I'm new to working with MySql within Access.
I've successfully created a connection via MySql ODBC and is able to link/view the table in MS Acess2002
When I create an update query (in Access), like the following:I

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

I get an error:
Operation must use an updateable query.
vindoesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OxonDevCommented:
Can you update the record in the linked table when using the set where the query fails?
0
vindoesAuthor Commented:
I'm sorry OxonDev, I'm  not sure what you meant by that
0
lee555J5Commented:
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


0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

OxonDevCommented:
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.

0
vindoesAuthor Commented:
I'm not trying to rename the table. I'm trying to update the table
0
lee555J5Commented:
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


0
HainKurtSr. System AnalystCommented:
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?
0
vindoesAuthor Commented:
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?

0
OxonDevCommented:
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...
0
HainKurtSr. System AnalystCommented:
can you try to add another table (add link, open) and edit that one?
maybe the problem is on table1 only...
0
Gustav BrockCIOCommented:
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
0
vindoesAuthor Commented:
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
0
Gustav BrockCIOCommented:
What numeric data types are in use?
Only Byte, Integer, and Long, but not BigInt, is supported by Access.

/gustav
0
vindoesAuthor Commented:
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
0
Gustav BrockCIOCommented:
So you have a compound (= more than one field) primary key?
I'm not sure what influence that may have.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vindoesAuthor Commented:
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
0
Gustav BrockCIOCommented:
> Operation must use an updateable query.

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

/gustav
0
vindoesAuthor Commented:
Answer was partially correct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.