Excel VBA connection to MYSQL using Transactions

[leeavery3]

I need to write to multiple tables in my MySql database from excel (possibly 20 plus). I will be needing to use PK of certain tables i update as FK of others. I would really like to use Transactions to insure all information is update and if something errors out for any reason then i can rollback the data.

Will an ADODB connection be able to accomplish this? If so are their options i need to set in my connection string to turn on Transactions or Multiple statements?

Should i set up a DSN though ODBC and select the proper options there?

Are there options that are not shown in the ODBC admin that need to be set in the connection string (Enable Transactions, Enable Multiple_Statements)?

Is there a workaround that will accomplish this if there is no way to run transactions in Excel VBA on a MySql DB.

I know this post is lacking my code but i have written and tried so many different ways to no avail. I'm not looking for the exact code as i can write it just need to be point in the right direction if possible.

Thank you in advance,

 

[leeavery3]

Also if this is outside of the normal scope of Mr. Excel forms or this should be posted in a different section please me know.

Thank you,

 

[leeavery3]

An update on progress.

When i set up an ODBC DSN and use a ADODB connection and recordset i am able to send more than one SQL SELECT statements per recordset.open command. Multiple recordset are stored in my recordset.

Issues Still:
-I have to use a DSN as the connection and check the box in setup to enable multiple statements.
-When i include START TRANSACTION and COMMIT in the at the beginning and end of the SQL statements i get an error.

What I want to do:
-Create a connection string instead of using a DSN created through ODBC Admin.
-Use Transactions