Powershell - ODBC MSAccess Commit transaction refresh data for Select Query after Insert

Question

I'm connecting with ODBC in powershell to an .accdb Access Database. After inserting new data into a table (insert into via ExecuteNonQuery), I'm unable to pull the data with a select command for futher processing (create a PDF table). The resulting object of the query is empty (0 rows), though "Select @@IDENTITY" returns the most recent index identifier. The Workflow is something like this: Data entered into a forms window -> new record is created -> data is pulled off and processed into a report -> report is sent by mail -> record is flagged as sent

I tried to use the SQL transaction mechanism, but even after the commit is done, the data is not immediately retrievable with a select-query. After inserting another record, the previously inserted one can be pulled with a select query, but not the new most recent one.

I have not found any working solution to this behavior. I've heard about this, when using Access forms, where the data will be available when the form has been moved to the next record. Any ideas how I can solve this?

Below are my getter and setter functions. In general they work as intended but using Set-Data with an insert first and firing Get-Data with a select afterwards does not provide any records.

Function Get-Data($SQLCommand){
    try{
        $Connection = New-Object System.Data.Odbc.OdbcConnection($DSN)
        $Connection.Open()
        $AccdbCommand = New-Object System.Data.Odbc.OdbcCommand($SQLCommand, $Connection)
        $AccdbAdapter = New-Object System.Data.Odbc.OdbcDataAdapter($AccdbCommand)
        $AccdbDataSet = New-Object System.Data.DataSet
        $AccdbAdapter.Fill($AccdbDataSet)
        $Connection.Close()
        return $AccdbDataSet
    }
    catch
    {
        [System.Windows.Forms.MessageBox]::Show($_.Exception.Message + "`r`n" + $_.Exception.InnerException, "Fehler",'OK','Error')
    }
}

Function Set-Data($SQLCommand){
    try
    {
        $Connection = New-Object System.Data.Odbc.OdbcConnection($DSN)
        $Connection.Open()

        $Transaction = $Connection.BeginTransaction()

        $AccdbCommand = New-Object System.Data.Odbc.OdbcCommand($SQLCommand, $Connection, $Transaction)
        $AccdbCommand.ExecuteNonQuery()
        $transaction.Commit()

        $AccdbCommand = New-Object System.Data.Odbc.OdbcCommand("Select @@IDENTITY", $Connection)
        $result = $AccdbCommand.ExecuteScalar()

        $Connection.Close()
    }
    catch
    {
        [System.Windows.Forms.MessageBox]::Show($_.Exception.Message + "`r`n" + $_.Exception.InnerException, "Fehler", 'OK', 'Error')
    }
}

Answer 1

I've resolved this Problem. The commit itself is working as intended. The missing data record in the output process was a result of an error in looping the rows in the dataset with a for Statement.