MySQL Bugs: #7160: ODBC link via ACCESS XP.

Bug #7160	ODBC link via ACCESS XP.
Submitted:	10 Dec 2004 9:58	Modified:	12 Dec 2004 22:41
Reporter:	Rob M	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (XP Pro)
Assigned to:	Vasily Kishkin	CPU Architecture:	Any

Description:
I linked to a table using ODBC 3.51 MySQL via Access 2002. The table has one Primary index. When complete I tried amending one record say Record 1 and it allowed me to update my amendments.
I then opened Record 2 and tried similiar amendment BUT without success on this occasion. It says someone else is updating the record and would I like to copy the data into Clipboard and wait for "other user" to finish amendments. NO OTHER users have access to system.
I then opened MySql Browser and made amendments successfully. However changing a field of type double to 0 from 0.00432 within Browser and then opening Access again and making amendments as before seemed to work!?!??

Am I missing something?

Rob

How to repeat:
1) Create table in database with the following fields:
acc_id - INTEGER - NOT NULL - AUTO INC - UNSIGNED - PRIMARY INDEX
acc_description - VARCHAR(30) - NOT NULL
acc_special - TINYINT(4)
acc_cr_int - DOUBLE
acc_dr_int - DOUBLE
etc

2) Create ODBC link to MySql database.

3) In Access link to new table visa ODBC 3.51.

4) Open table in Access to make amendments.

Thank you for this bug report. To properly diagnose the problem, we
need a backtrace to see what is happening behind the scenes. To
find out how to generate a backtrace, please read
http://www.mysql.com/doc/en/Making_trace_files.html

Once you have generated a backtrace, please submit it to this bug
report and change the status back to 'Open'. Thank you for helping
us make our products better.

Additional info:

Hi,

i have tested your bug report with 3.51.10 via MS Access, but the link are correctly works and all the amendments are successful.

Please, can you try it with new Version of myodbc3 (3.51.10).
I have use the following sql-command to link table via MS Access:

create table test (
acc_id int unsigned auto_increment not null primary key,
acc_description varchar(30) not null, 
acc_special tinyint(4), 	
acc_cr_int  double, 
acc_dr_int double);

Best Regards,
Harun

CREATE TABLE as follows:
tablename1  CREATE TABLE `tablename1` (
`fld1` int(10) unsigned NOT NULL auto_increment, 
`fld2` float default NULL, PRIMARY KEY  (`dddd`)) ENGINE=InnoDB DEFAULT CHARSET=latin1   

Create a link via Access using 3.51.10 ODBC.
Enter number in 'fld2' 0.003345. The number is accepted correctly. Then go back and amend 'fld2' by putting any number, on this occasion I put in zero. The same error occurs as described in bug report. There is however another problem and that is: It shows deleted if you don't put a number in 'fld1'!! This is due to 'auto_increment'.

I am using Access 2002 (10.6501.6714) SP3.

Rob

The 'ddddd' should be 'fld1'

Having the same problem running a vba script from Access XP through linked tables.  Using MySQL server 4.1, ODBC 3.5.  Server machine is Win Server 2003, client is Win XP pro.

Record inserts work fine, select works fine, update generates locking errors indicating someone else is trying to update the same record.

This is really annoying and I need a workaround soon, or I'm going to have to dump MySQL for something else.  Seems a pretty fundamental need to not work.

Would appreciate a direct e-mail with an answer if anyone has one.

Thanks.

This is definately a bug in the MySQL ODBC driver.  I have my table set up to auto-number with a timestamp default value of current timestamp.
I can update a record once and then Access thinks it is locked forever.

The record locking happens whether I have a timestamp or not in the table.  I heard somebody got around this by assigning randmom values to the timestamps though.

Having a primary key and timestamp, I can edit records in datagrid view now.  However, if I run an update query, I can update specific records only once.  I cannot update the same record through an update query again, or I get an error that the record is locked.  This persists even if I close Access, and do a "compact and repair".  I can still manually edit the record through an Access datagrid view.  Also, if I delete the data and re-append the same exact data, I can run my update once and only once, and then the same update query says the record is locked again.

Hello,

I have the same problem as described here.
Everything goes except update the second time.
I use Access 2007 as front end, the vba code .update doesn't work.
MySql-driver is: MySQL ODBC 5.1

Is there already a solution for this? Please help me?

Continued in Bug#46406 and many many other reports.