Opening recordsets with MySQL backend

[303factory]

Hi

I'm having some trouble with updating recordsets by code since I moved to MySQL back end and was wondering if the code shoudl be any different.

Set RS = New ADODB.Recordset
strSQL = "SELECT * FROM tblExhibitInfo WHERE Exhibit =" & Me.Exhibit
RS.Open strSQL, gMIDdbase, adOpenKeyset, adLockOptimistic, adCmdText
 
RS.Fields("Name") = Me.Name
RS.Fields("Number") = Me.Number
 
RS.Update
RS.Close
 
Set RS = nothing

My code is always in the above format, recordsets are opened and closed in this manner frequently, sometimes when another recordset on a different table is open.
The problem is my code seems to frequently throw up the 'the microsoft jet engine stopped the process because you and another user are attempting to change the data at the same time ' error. This happens on the RS.Update line. It seems to happen more often if the data is being updated to the same set of data (ie no changes).

I've googled to see if my syntax shoudl have changed for mysql, and googled the error message but after about 5 hours investigating have failed to come up with a solution..

Anyone encountered the same problem as me here?

 

[Banana]

Are you running the code against same table where a form has it open? What is the context when users may execute the code? (e.g. clicking a button? automatic background processing?)

 

[303factory]

Are you running the code against same table where a form has it open? What is the context when users may execute the code? (e.g. clicking a button? automatic background processing?)

No this is a completely unbound form with no other forms or queries open. This code is executed when somebody types data into the text box and clicks a 'save' button

 

[303factory]

The plot thickens

My problem doesnt seem to be code based.

If I open the table in access and try to manually edit a field, it causes the write conflict If try to save the field as the same data it was before. If I enter a new value I dont get the conflict.

I've recreated this on a new datbase with only one simple table linked to an empty access client with just the one table link.

For some reason mysql doenst like me updating a value if the new value is the same as the old value. I *could* go through my entire client and tell it to only update fields if they haev changed but taht would be a monumental effort..

 

[Banana]

I've never seen that behavior before.

What's your MySQL's version? MyODBC's version? What engines are you using for the table you linked to?

On a more general note- are all of your forms unbound? If so, then why bother with Access? You would probably get better result doing it from scratch in Visual Studio or such; Access's strength comes in bound forms and it can be used even with linked table to good effects.

 

[Kiwiman]

Howzit

I'm not sure if this is relevant to MySQL- but we had a similar problems when we migrated to SQL Server 2005. This was resolved by putting a new field in each table with the data type as Timestamp. No need to worry about updating this field as SQL Server does this itself.

All our write conflicts went away.

 

[303factory]

I've never seen that behavior before.

What's your MySQL's version? MyODBC's version? What engines are you using for the table you linked to?

On a more general note- are all of your forms unbound? If so, then why bother with Access? You would probably get better result doing it from scratch in Visual Studio or such; Access's strength comes in bound forms and it can be used even with linked table to good effects.

It's MySQL Server 5.1 with the ODBC 5.1 driver (although error still happens with 3.51 driver). What do you mean by what engines?

We do use bound forms elsewhere, just not in the general admin area where I want to force staff to click 'save' before data is saved in the table.

 

[303factory]

Howzit

I'm not sure if this is relevant to MySQL- but we had a similar problems when we migrated to SQL Server 2005. This was resolved by putting a new field in each table with the data type as Timestamp. No need to worry about updating this field as SQL Server does this itself.

All our write conflicts went away.

I tried adding a timestamp field, now when I try to update any value in a table it says: Reserved error (-7776)..

 

[Banana]

MySQL allow you to choose and mix from variety of storage engines for different capabilities. The usual choices are MyISAM (default, non-transactional but fast), InnoDb (transactional with support for foreign key constraints), and then couple others. You can find out what engine a table uses by doing a:

SHOW CREATE TABLE <name of table>;

Also, paste the table definition. It may help help us what is the issue.

 

[303factory]

MySQL allow you to choose and mix from variety of storage engines for different capabilities. The usual choices are MyISAM (default, non-transactional but fast), InnoDb (transactional with support for foreign key constraints), and then couple others. You can find out what engine a table uses by doing a:

SHOW CREATE TABLE <name of table>;

Also, paste the table definition. It may help help us what is the issue.

Ah right, it's InnoDB.

---------------------------------------------+
| addressbook | CREATE TABLE `addressbook` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) DEFAULT NULL,
`Number` varchar(255) DEFAULT NULL,
`testBoolean` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |

 

[Banana]

This is for a table named 'addressbook' but your code in OP references a 'tblExhibitInfo'? Just wanted to be sure we're looking at the right table.

If this is the right table, does your testBoolean have negative numbers in it?

 

[303factory]

This is for a table named 'addressbook' but your code in OP references a 'tblExhibitInfo'? Just wanted to be sure we're looking at the right table.

If this is the right table, does your testBoolean have negative numbers in it?

To get to the bottom of the problem I created a new database with just one table 'addressbook' and linked it to an empty Access client. The problem is occuring even on this simple database. I've created a query log of what happens when you try to open the table in access and change the values.

Yes my testBoolean has negative numbers. As far as I was aware 0 was FALSE and -1 was true?

 

[303factory]

Query log for if you try to update table:


SET NAMES utf8
SET character_set_results = NULL
SET SQL_AUTO_IS_NULL = 0
SELECT Config, nValue FROM MSysConf
SELECT `addressbook`.`ID` FROM `addressbook`
SELECT `ID`,`Name`,`Number`,`testBoolean` FROM `addressbook` WHERE `ID` = 1 OR `ID` = 2 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3
SELECT `ID`,`Name`,`Number`,`testBoolean` FROM `addressbook` WHERE `ID` = 1
SET AUTOCOMMIT=0
UPDATE `addressbook` SET `Name`='c' WHERE `ID` = 1 AND `Name` = 'b' AND `Number` = '546' AND `testBoolean` = -1
COMMIT
SET AUTOCOMMIT=1
SELECT `ID`,`Name`,`Number`,`testBoolean` FROM `addressbook` WHERE `ID` = 2
SET AUTOCOMMIT=0
UPDATE `addressbook` SET `Name`='a' WHERE `ID` = 2 AND `Name` = 'a' AND `Number` = '456' AND `testBoolean` = 0
ROLLBACK
SET AUTOCOMMIT=1
SELECT `ID`,`Name`,`Number`,`testBoolean` FROM `addressbook` WHERE `ID` = 2
SELECT `ID`,`Name`,`Number`,`testBoolean` FROM `addressbook` WHERE `ID` = 1
SET AUTOCOMMIT=0
UPDATE `addressbook` SET `Name`='c' WHERE `ID` = 1 AND `Name` = 'c' AND `Number` = '546' AND `testBoolean` = -1

 

[Banana]

I'd bet the negative numbers are the source. If you open the table definition on Access side, you would probably see that testBoolean is defined as a Byte, not a Yes/No. Even galling, Byte cannot be signed so it can't accept a negative number.

Do this on MySQL side.

UPDATE addressbook SET testBoolean = 1 WHERE NOT testBoolean = 0;
ALTER TABLE addressbook CHANGE COLUMN testBoolean testBoolean NOT NULL UNSIGNED TINYINT;

This will now make your testBoolean a positive number, and a unsigned type which is compatible with Access's Byte data type.

Note that it'll still work as a Yes/No on the form when you bind a checkbox to the field.

The definition of False is universal (e.g. it's always defined as zero) while True is defined as nonzero (it happens to be -1 in Access and 1 in MySQL because of different underlying conventions but it still remains the case that it's nonzero that is evaluated to true), so you want to take advantage of that.

Instead of testing for true:

If testBoolean = True Then

or even

If testBoolean = -1 Then

It's best to do it implicitly:

If testBoolean Then

Let me know if this fixed the problem.

 

[303factory]

I'd bet the negative numbers are the source. If you open the table definition on Access side, you would probably see that testBoolean is defined as a Byte, not a Yes/No. Even galling, Byte cannot be signed so it can't accept a negative number.

Do this on MySQL side.

UPDATE addressbook SET testBoolean = 1 WHERE NOT testBoolean = 0;
ALTER TABLE addressbook CHANGE COLUMN testBoolean testBoolean NOT NULL UNSIGNED TINYINT;

This will now make your testBoolean a positive number, and a unsigned type which is compatible with Access's Byte data type.

Note that it'll still work as a Yes/No on the form when you bind a checkbox to the field.

The definition of False is universal (e.g. it's always defined as zero) while True is defined as nonzero (it happens to be -1 in Access and 1 in MySQL because of different underlying conventions but it still remains the case that it's nonzero that is evaluated to true), so you want to take advantage of that.

Instead of testing for true:

If testBoolean = True Then

or even

If testBoolean = -1 Then

It's best to do it implicitly:

If testBoolean Then

Let me know if this fixed the problem.

I'll make sure I fix that for my big database, however I don't think this is the source of the problem. I completely removed the testBoolean field, made a new access client and linked it to the even simpler table and the problem persists. Here the log file:

SELECT `addressbook`.`ID` FROM `addressbook`
SELECT `ID`,`Name`,`Number` FROM `addressbook` WHERE `ID` = 1 OR `ID` = 2 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3
SELECT `ID`,`Name`,`Number` FROM `addressbook` WHERE `ID` = 1
SET AUTOCOMMIT=0
UPDATE `addressbook` SET `Name`='c' WHERE `ID` = 1 AND `Name` = 'c' AND `Number` = '547'
ROLLBACK
SET AUTOCOMMIT=1
SELECT `ID`,`Name`,`Number` FROM `addressbook` WHERE `ID` = 1
SELECT `ID`,`Name`,`Number` FROM `addressbook` WHERE `ID` = 2
SET AUTOCOMMIT=0
UPDATE `addressbook` SET `Name`='a' WHERE `ID` = 2 AND `Name` = 'a' AND `Number` = '456'
ROLLBACK

A MySQL user friend informs me that the queries are being executed as transactions when they shouldnt be but has no idea how to fix access so this isnt happening..

 

[Banana]

The logs looks OK to me. The transactions are actually started from Access's side either by Jet or the library managing the connection and I can assure you that it is not a problem.

I just realized I never asked you what options do you have enabled for your MyODBC driver?

At a minimum, you should have "Return Matching Rows" enabled.

 

[303factory]

The logs looks OK to me. The transactions are actually started from Access's side either by Jet or the library managing the connection and I can assure you that it is not a problem.

I just realized I never asked you what options do you have enabled for your MyODBC driver?

At a minimum, you should have "Return Matching Rows" enabled.

Aaaaaaaaah the hidden 'make it work' tickbox.. I've changed that now and problem solved. Thank you so much I never would have found that!

 

[Banana]

I'm so sorry I didn't think of it sooner and going on a wild goose chase.

FWIW, this is the usual options I have enabled: 213975098

The options are enumerated here

 

[303factory]

I'm so sorry I didn't think of it sooner and going on a wild goose chase.

FWIW, this is the usual options I have enabled: 213975098

The options are enumerated here

Heh no worries maybe if I'd read the documentation properly this wouldnt have happened cheers I'll take a look at that

 

[dfenton]

I have to ask why you're using ADO for ODBC linked tables. With linked tables, DAO is preferred, as the data is coming through Jet, anyway, so you ought to use Jet's native interface. Using ADO means you're using Jet, ODBC, ADO and OLEDB, instead of just DAO, Jet and ODBC. I'm not suggesting this would resolve any problem, just that the use of ADO with data being handled by Jet (i.e., any ODBC linked table) makes no sense.

Now, if you had a MySQL OLEDB driver and used it directly (instead of using ODBC linked tables), then ADO would be perfectly sensible. But that doesn't appear to be what you're doing.