ODBC link to MySQL, can write, but not edit or delete

[Petr Danes]

I have a MySQL database on a server in our shop, which I'm running through
an ODBC link. Very simple table (experimental part of a larger project), one
text field, the primary key. I have two queries, one which loads the table,
one which clears it. The load runs fine, shovels in around 300 records with
nary a peep. But the delete query fails, as does VBA code with error 3027
when I try to clear it again, or even execute rst.Edit. I have to log onto
the server and run a query directly in MySQL to empty the table. I've looked
at all the permissions I can find and all are set to unrestricted access.

What I have, seemingly, is a write-only-once table. I can add records, but
not edit or delete them. Anybody?

Pete

 

[Banana]

I have a MySQL database on a server in our shop, which I'm running through
an ODBC link. Very simple table (experimental part of a larger project), one
text field, the primary key. I have two queries, one which loads the table,
one which clears it. The load runs fine, shovels in around 300 records with
nary a peep. But the delete query fails, as does VBA code with error 3027
when I try to clear it again, or even execute rst.Edit. I have to log onto
the server and run a query directly in MySQL to empty the table. I've looked
at all the permissions I can find and all are set to unrestricted access.

What I have, seemingly, is a write-only-once table. I can add records, but
not edit or delete them. Anybody?

Pete

Did you configure the ODBC driver to return matching rows instead of
default affected rows?

What kind of text is the primary key?

Does the table contain any datatypes not directly comparable to
JET/ACE's data types? (a good example is using BIGINT... there is no
such concept in JET/ACE so that may render the table non-updatable, even
though you were able to insert into it.) Also, don't forget to compare
the DESC <nameoftable> with what you see in Access's table design view
for the same table to verify that they actually line up.

See where it takes you.

 

[Petr Danes]

Did you configure the ODBC driver to return matching rows instead of

default affected rows?

Tried it both ways, no change.

What kind of text is the primary key?

Genus-species lichen names. Here're a few lines:


Abrothallus bertianus

Abrothallus caerulescens

Abrothallus tulasnei

Acaroconium punctiforme

Acarospora insolata

Acarosporium hospitans

Acremonium rhabdosporum

Agonimia opuntiella

Agonimia tristicula

Does the table contain any datatypes not directly comparable to JET/ACE's
data types?

Contains only this one text [varchar(250)] field, as the primary key.

Also, don't forget to compare the DESC <nameoftable> with what you see in
Access's table design view for the same table to verify that they actually
line up.

It does, I can open the table and scroll through the records. They are the
correct ones, only I can't modify or delete them. Also, the table view of
the data is not updateable, although the query to add records continues to
run fine.

Pete

 

[Banana]

Tried it both ways, no change.

As a FYI- MySQL documentation has said that you should have matching
rows enabled as that is what Access excepts, though this wasn't the
solution for this specific case.

Does the table contain any datatypes not directly comparable to JET/ACE's
data types?

Contains only this one text [varchar(250)] field, as the primary key.

Shouldn't be a problem... are you using a special character set or
collation?

It does, I can open the table and scroll through the records. They are the
correct ones, only I can't modify or delete them. Also, the table view of
the data is not updateable, although the query to add records continues to
run fine.

But did you open the linked table in design view and verify that column
was correctly identified as a primary key? This may be a case of Access
not recognizing the column as a primary key or unique index and if it
can't, the table becomes non-updatable.

Is the query a native JET query or a pass-through query? If latter, then
that's no surprise.

A quick test is to delete the linked table then create a new linked
table... if you get a little dialog asking to select a column, select
the column that's the primary key. That should then make the table
updatable.

 

[Petr Danes]

Hi Banana (sorry, but that's your signature),



It's fixed, I deleted the table reference and re-joined it through the ODBC
dialog, works fine now. I made some changes to the table structure after
initially creating and populating it; I did not have a primary key when I
first created the table, which was probably the source of the problem. Even
after creating one in MySQL, Access did not have the field listed as a
primary key; now it does and everything works.



As to your other points, no special character set or sort order, standard
Latin-1 set. And it was a Jet query, not a pass-through. Still seems odd
that I could add records, yet not edit or delete them. Although, now that I
think about it, maybe it does make sense. To add records, Access simply
hands them to the ODBC driver, and it's up to the DB on the other end how it
handles them, but if it loses track of an index, or doesn't have one to
begin with, then it can't with certainty identify which record is to be
deleted or updated, so to be safe, does not allow any such manipulation.



I should have thought to delete and re-create the table myself; since it's
only a link, that's a trivial operation, but I don't have that much
experience with ODBC work and it didn't occur to me at the time.



Thanks for the advice, next time I'll know better.



Pete

Tried it both ways, no change.

As a FYI- MySQL documentation has said that you should have matching rows
enabled as that is what Access excepts, though this wasn't the solution
for this specific case.

Does the table contain any datatypes not directly comparable to
JET/ACE's data types?

Contains only this one text [varchar(250)] field, as the primary key.

Shouldn't be a problem... are you using a special character set or
collation?

It does, I can open the table and scroll through the records. They are
the correct ones, only I can't modify or delete them. Also, the table
view of the data is not updateable, although the query to add records
continues to run fine.

But did you open the linked table in design view and verify that column
was correctly identified as a primary key? This may be a case of Access
not recognizing the column as a primary key or unique index and if it
can't, the table becomes non-updatable.

Is the query a native JET query or a pass-through query? If latter, then
that's no surprise.

A quick test is to delete the linked table then create a new linked
table... if you get a little dialog asking to select a column, select the
column that's the primary key. That should then make the table updatable.

 

[Banana]

Hi Banana (sorry, but that's your signature),

No need to apologize. This is what it is.

It's fixed, I deleted the table reference and re-joined it through the ODBC
dialog, works fine now. I made some changes to the table structure after
initially creating and populating it; I did not have a primary key when I
first created the table, which was probably the source of the problem. Even
after creating one in MySQL, Access did not have the field listed as a
primary key; now it does and everything works.

I'm glad you found the solution. As you now realize, Access can't work
with a table for which it doesn't know how to uniquely identify a single
row so it's required that the linked table have some kind of primary key
or at least a unique index for Access to work with.

I think you're good to go. If you need more information, you may want to
read up on this:

http://tinyurl.com/ODBCGuide

It also links to a whitepaper discussing in details about how Access
works with ODBC data as well other links.

HTH.

I should have thought to delete and re-create the table myself; since it's
only a link, that's a trivial operation, but I don't have that much
experience with ODBC work and it didn't occur to me at the time.

Well, as someone much wiser than I am once told me: Experience is the
ability to recognize the mistake when you make it again.

Thanks for the advice, next time I'll know better.

Best of luck!