Jet not playing nice with ODBC and Server (1 Viewer)

[Banana]

I'm a bit frustrated with how Jet is behaving with the server (MySQL).

Apparently it loves to issues commands like those:

		   48 Query	SELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
		   48 Query	SELECT `ID`,`Name`,`CountryCode`,`District`,`Population`  FROM `city` WHERE ( 'ABW' = `CountryCode` )  limit 10
071114 13:44:25   48 Query	SET AUTOCOMMIT=0
		   48 Query	COMMIT
		   48 Query	SET AUTOCOMMIT=1
		   48 Query	CALL BEGINTRAN
		   48 Query	SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2`  FROM `country`  WHERE `Code` = 'ABW'
071114 13:44:27   48 Query	SET AUTOCOMMIT=0
		   48 Query	UPDATE `country` SET `Name`='Aruba'  WHERE `Code` = 'ABW' AND `Name` = 'Arubz' AND `Continent` = 'North America' AND `Region` = 'Caribbean' AND `SurfaceArea` = 1.93000000000000000e+002 AND `IndepYear` IS NULL AND `Population` = 103000 AND `LifeExpectancy` = 7.84000015258789060e+001 AND `GNP` = 8.28000000000000000e+002 AND `GNPOld` = 7.93000000000000000e+002 AND `LocalName` = 'Aruba' AND `GovernmentForm` = 'Nonmetropolitan Territory of The Netherlands' AND `HeadOfState` = 'Beatrix' AND `Capital` = 129 AND `Code2` = 'AW'
		   48 Query	COMMIT
		   48 Query	SET AUTOCOMMIT=1
071114 13:44:28   48 Query	SET AUTOCOMMIT=0
		   48 Query	COMMIT
		   48 Query	SET AUTOCOMMIT=1
		   48 Query	CALL ROLLBACKTRAN

I want to use transactions, but with Jet's behavior, it's useless because Jet insists on committing every update it does.

Disabling transactions in the ODBC driver didn't stop it. (This is why you see "CALL [Transaction method] in the query- I figured that if I made a stored procedure, disabling transactions, Jet would STFU and let me decide when to commit or rollback the transaction, but it blithely ignores the setting...)

There's no "Safe Transactions" value anywhere, and I don't know where I'd look to alter Jet's behavior, already having looked at regedit and reading the whitepaper on JET and ODBC.

I also seems can't to use ODBCDirect workspace for bound forms, and even if I set the recordset to a ODBCDirect workspace, I lose all Jet's intelligence in selecting only needed records (in which it does a wonderful job, I'd think). I also can see that if I create a ODBCDirect workspace, I would be creating one more (unwanted) connection to the server, which indicates to me that Jet/Default workspace will connect to the server anyway, and because the default workspace isn't an ODBCDirect, I can't modify/view connection properties.

Can I please have my cake and eat it? Anybody knows whether the behavior can be altered?

 

[Banana]

Maybe anyone can help me out?

 

[CraigDolphin]

Can I please have my cake and eat it?

Buy two cakes? Sorry Banana....would love to help but you're swimming too far from the beach for me

Can give your post a bump though

 

[Banana]

Thanks for the bump.

However, it seems to have been solved now.

I do know why but my experiment was based on the code from this article. First time I ran it, it didn't work correctly, and after several failures, reverted to practically original code to look at the SQL traces carefully, and now it works, even for linked subform.

I swear, Access is playing games with me.

 

[CraigDolphin]

I know the feeling...I have a query that keeps changing the links between tables from the pk/fk fields to a non-pk y/n field connceted to the fks! I've manually changed it back and saved it 4 times since this morning. I swear Bill Gates has heard my mutterings about going to Linux on my next machine.