Consulting

Results 1 to 4 of 4

Thread: ERROR: EXECUTE READER REQUIRE THE COMMAND TO HAVE TRANSACTION WHEN....

  1. #1
    VBAX Regular
    Joined
    Feb 2019
    Posts
    23
    Location

    ERROR: EXECUTE READER REQUIRE THE COMMAND TO HAVE TRANSACTION WHEN....

    Hi, I am trying Rollback and commit in my VB (VBA with ms access) Code but getting error-
    CODE-

    Dim tra As OleDb.OleDbTransaction = Nothing
    Try
    'inserting data into table
    Dim myCmd As New OleDb.OleDbCommand


    cn.open()
    tra = cn.BeginTransaction()
    myCmd.Connection = cn
    myCmd.CommandText = "INSERT INTO query 1"
    myCmd.Transaction = tra
    myCmd.ExecuteNonQuery()

    myCmd.CommandText = "INSERT INTO query 2" myCmd.ExecuteNonQuery()



    tra.Commit()

    Catch ex As Exception
    MessageBox.Show(ex.Message, "Error-" & Transaction_name, MessageBoxButtons.OK, MessageBoxIcon.Error)


    tra.Rollback()
    End Try

    ERROR-




    PLEASE HELP !!!!!!!!

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry, the majority of your code doesn't look like anything I am used to in Access VBA.
    Are you working from outside Access?

  3. #3
    VBAX Regular
    Joined
    Feb 2019
    Posts
    23
    Location
    OK MY BAD, Let me put my code here for you to analyse-

    Dim tra As OleDb.OleDbTransaction = Nothing
    Dim da4 As OleDb.OleDbDataAdapter
    Dim dt4 As New DataTable
    Dim myCmd As OleDb.OleDbCommand = cn.CreateCommand()

    Try
    ConnectDatabase(CSS_FE_DB_PATH)
    tra = cn.BeginTransaction()
    myCmd.Transaction = tra
    myCmd.Connection = cn
    myCmd.CommandText = "INSERT Query 1........."
    myCmd.ExecuteNonQuery()

    da4 = New OleDb.OleDbDataAdapter("SELECT Query....., cn)
    da4.Fill(dt4)

    myCmd.CommandText = "INSERT INTO query 2........."
    myCmd.ExecuteNonQuery()

    ra.Commit()

    Catch ex As Exception
    MessageBox.Show(ex.Message, "Error-" & Transaction_name, MessageBoxButtons.OK, MessageBoxIcon.Error)
    tra.Rollback()
    End Try


    Error- Execute Reader require the command to have a transaction when the connection assigned to the command is in a pending local transaction. the transaction property of the command has not be initialized

    My Guess is this has something to do with the SELECT Query code right after Insert Statement. Can you help now !

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    No, sorry, I am only familiar with Access based VBA, not with code from an external source.
    Your code does not look correct for initiating an Insert query.
    I would normally use a Recordset to add data to an access table.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •