locked
Can multiple ADODB commands committed at once? RRS feed

  • Question

  • I like to use ADODB commands because I can create paramaters and avoid SQL injection and all that fun stuff.

    But I come across times when I need to insert or update records from multiple tables in a single transaction.

    For those cases I am using DAO.Workspace, DAO.Database, and DAO.Recordset.

    Is there any way I can create multiple ADODB commands and execute them all in the same transaction or insert into or update multiple tables using a single ADODB.Command in a single transaction?

    Tuesday, April 23, 2019 11:38 PM

Answers

  • Transaction processing in ADO is much the same as in DAO, but in the former the methods are methods of the Connection object rather than the Workspace object, and the equivalent of the DAO Rollback method is the RollbackTrans method in ADO.

    Ken Sheridan, Stafford, England

    • Marked as answer by Acquire Logic Friday, April 26, 2019 6:49 PM
    Thursday, April 25, 2019 3:48 PM

All replies

  • The main reason you would create a Workspace object is because of ws.BeginTrans, no?

    In a transaction you can issue multiple commands, and they all succeed or all are rolled back, assuming you write your error handler correctly.

    If your BE is SQL Server, you can create a Stored Procedure that runs multiple commands, and invoke it with a single ADODB.Command.


    -Tom. Microsoft Access MVP

    Tuesday, April 23, 2019 11:55 PM
  • I understand I can pass almost whatever I want into SQL Server and use stored procedures. But I specifically am only interested in being able to commit multiple ADODB commands in one transaction. Thinking more about it, I don't think its possible. DAO workspaces are alot different than ADODB.

    Thanks for your help in any case.

    Is it safe to mark the answer as no?



    Wednesday, April 24, 2019 2:29 AM
  • You are asked to "click "Mark as Answer" on the post if your question was answered appropriately"


    -Tom. Microsoft Access MVP

    Wednesday, April 24, 2019 1:34 PM
  • Transaction processing in ADO is much the same as in DAO, but in the former the methods are methods of the Connection object rather than the Workspace object, and the equivalent of the DAO Rollback method is the RollbackTrans method in ADO.

    Ken Sheridan, Stafford, England

    • Marked as answer by Acquire Logic Friday, April 26, 2019 6:49 PM
    Thursday, April 25, 2019 3:48 PM
  • Your right Ken Sheridan.

    I just discovered this and logged in to post the answer but you got to it first.

    Thanks!


    Friday, April 26, 2019 6:49 PM