unable to update / delete records via odbc from access to mySQL

[DaveRussell]

Hi!

I'm unable to update or delete any records in a remote (online) mySQL database from my local access 2000 database.

Apperently my mySQL permissions are fine because when i update the mySQL database through php online, there isn't a problem... so i'm assuming that the problem is either in my odbc connection or in the actual access database. I posted something like this in the odbc forum, i'm just wondering if anyone here could give some advice?

Thanks, Dave.

 

[PHV]

Any chance you could post the error message and the relevant code to help us to help you ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DaveRussell]

I knew i forgot something...

Well, there really isn't any relevant code, anytime i run a Docmd.RunSQL("UPDATE...") query i get this error:

Run-Time Error 3073: Operation must use an updateable query

And everytime i run a delete sql i get this :

"Could not delete from specified tables"

 

[PHV]

Have you tried to link the tables (menu File -> External data -> Link tables) and do some manual update/delete directly from table view ?
If OK, you don't have permissions issue, so please post the relevant SQL code.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DaveRussell]

yeah, i can't even update or delete from table view I'm looking at my permissions for the specified user, and there's no problem there.

Here's the code anyways, just in case...

DoCmd.RunSQL ("UPDATE rmtCustomerInfo SET ciUserName = '" & tbUserName.Value & "', " & _
"ciPassword = '" & tbPassword.Value & "' " & _
"WHERE AcctID = tbAcctID.Value;")

 

[Axoliien]

Have you verified your permissions through an outside host, AKA the '%' host (% host being any host) In case you have not, even if you have permissions set up for a user from a specific host, the MySQL database will use whatever username and host best fits your current login.

Example: I login from home to my remote SQL server as Axoliien. I have full permissions set up for Axoliien at Localhost. I am actually seen as Axoliien at %, or in other words since I have not set up permissions for Axoliien on % I am seen as Anyone at %. In this case, Anyone at % has permissions to view, but not to update or delete (default).

Also, create a dummy table with a primary key and some Trial char space, and put in a few records with random data, and link the table. Try

Dim sqlStr as String
sqlStr = "UPDATE Dummy SET Trial ='Worked'"
CurrentDb.Execute sqlStr

If your tables are linked, that should have no problems if permissions are set up, and all data in the dummy table should be changed. If not, it is most likely permission problems.

 

[DaveRussell]

I'm officially a moron.... i figured it out. thanks for all the input... i neglected to select unique record identifiers when i linked the tables originally!

- Dave.

 

[Axoliien]

I looked back over some notes I took a few months ago, and realized that what I told you was out of order.

First we were able to connect to the MySQL database with Access, however we could not change data. After struggling with it, we found out that we had been logging in as a registered user, but had only set up permissions for that registered user at localhost and at the specific host name.

Next we set the user up at '%' which meant 'Any' host. Then we set up permissions to allow the access we wanted for the Access database.

Next we connected to the database and found that we could read data and write data, however very often we were getting errors that said we could not save the changed data because someone else had changed it since we had retrieved the data. Making sure we had upgraded to the latest ADO 2.8 and the MySQL ODBC drivers on the windows machine fixed this for many tables, but we still saw some errors.

Next we found out that data types in MySQL and Access do not always match. Access and MySQL numerical types, for example, cause errors regularly when you use values too large for primary keys. We could only remedy these errors by setting all primary key columns to integer values and not long or big integer values. Also, memo fields or large text fields cause this same error in non-primary keys.

There are several more pages on problems between MySQL and Access, and truthfully I would suggest shying away from MySQL if you need a true relational database. Without upgrading to the latest Alpha version you are without subqueries, and no MySQL release so far includes views, stored procedures, or triggers. Also, to put in foreign keys are a pain, you must change all tables to InnoDB tables and set up your foreign keys after all table definitions, than any changes to table definitions require you to remove all foreign keys on a table, rededine the table, redefine indexes, and then pray that MySQL recognizes the foreign keys again.

As one last note, we have a table right now that will not recognize the primary key in another table as a foreign key because it is not a valid index, but you can't index primary keys and the other tables recognize it... The only fix was to export the entire sql database, type in the line of foreign key definition, and then import the sql again into a new database.