Excel VBA connection to MYSQL using Transactions

leeavery3

New Member
Joined
Mar 6, 2014
Messages
16
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,
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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,
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top