[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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.
0
vindoes
Asked:
vindoes
  • 7
  • 4
  • 3
  • +2
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now