Update of tabel hangs access under transaction control

[Rotsey]

Hi,

I am writing a function to update a number of tables under transaction
control.

One of the updates fail on the rsEmp.Update statement.

When I run the same function without transaction control it runs fine.

The code where it fails is labelled below.

Does anyone know of reasons why updates do fail under transactions.????

rotsey

gSQL = "SELECT PayFrequency,TempPayFrequency FROM Employees WHERE
EmployeeID = " & !EmployeeID
If Not GetODBCRecordset(gSQL, rsEmp) Then GoTo ErrExit
If Not rsEmp.BOF Then
If Not IsBlank(rsEmp!PayFrequency) Then
If Left$(rsEmp!PayFrequency, 3) = "Non" Then
PayFreqType = "Non"
Else
PayFreqType = "Res"
End If
gSQL = "SELECT * FROM PayFrequency WHERE PayFrequencyCode =
'" & rsEmp!PayFrequency & "' "
If Not GetODBCRecordset(gSQL, rsPay) Then GoTo ErrExit
If Not rsPay.BOF Then
If (CInt(!WeeksInvoiced) = 2 And rsEmp!PayFrequency =
"resHalfMonthly") Or (CInt(!WeeksInvoiced) = 2 And rsEmp!PayFrequency =
"NonResHalfMonthly") Then
' Do Nothing
ElseIf CInt(rsPay!NumWeeksInPeriod) <>
CInt(!WeeksInvoiced) Then
gSQL = "SELECT * FROM PayFrequency WHERE
Substring(PayFrequencyCode,1,3) = '" & PayFreqType & "' "
gSQL = gSQL & " AND NumWeeksInPeriod = " &
!WeeksInvoiced
If Not GetODBCRecordset(gSQL, rsPay) Then GoTo
ErrExit
If rsPay.BOF Then
MsgBox "Error: Weeks Invoiced does not exist in
PayFrequency table for employee " & EmpName & " Rolling back process.",
vbCritical, "Temp Pay Frequency"
CreateSalaryInvoice = False
GoTo ErrExit
Else
rsEmp.Edit
rsEmp!TempPayFrequency = rsPay!PayFrequencyCode
MsgBox ("Step E6 " & rsPay!PayFrequencyCode)
rsEmp.Update 'THE UPDATE FAILS HERE
MsgBox ("Step E7")
End If
End If
End If

 

[Allen Browne]

Does it make any difference if you uncheck the box under:
Tools | Options | Advanced | Use Record-level locking
We had some transactions that failed while that option was set, and ran
successfully once the box was unchecked.

 

[Bill Mosca, MS Access MVP]

Rotsey

You left out quite an important bit of code...your connection. The
GetODBCRecordset function suggests to me that this is a recordset in an SQL
Server , Oracle or other database. Some connections and recordsets do not
allow updating. Yours may be one of them.

Also, are you getting a specific error number?

 

[Rotsey]

Bill,

I tried unchecking the record level locking, no good.

No I get know error message just hangs.

Yes it is a ODBC connection to a SQL db.

Here is the code that I did not give. The first is a function to open
global connections.
The second is how I open a recordset.


Public Function Connection(Optional ODBCDirect As Boolean) As Boolean
On Error GoTo ErrHandler
'-------------------------------------------------
'MS 23/10/02 Added
Dim db As Database
Dim rs As Recordset

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblDSN", dbOpenTable)
'--------------------------------------------------

'--Sets the ODBC Direct workspace and database.
Set gobjWSodbc = CreateWorkspace("ODBCWorkspace", "dbo", "", dbUseODBC)
Workspaces.Append gobjWSodbc
gobjWSodbc.DefaultCursorDriver = dbUseDefaultCursor

Set gobjDBodbc = gobjWSodbc.OpenDatabase(rs!DSN, dbDriverNoPrompt,
False, _
"ODBC;DSN=" & rs!DSN & ";SERVER=" & rs!Server &
";WSID=LESTER34;DATABASE=" & rs!Database &
";TranslationName=Yes;QueryLogFile=Yes")
Set gobjConn = gobjWSodbc.OpenConnection(mConnectName, dbDriverNoPrompt,
False, _
"ODBC;DSN=" & rs!DSN & ";SERVER=" & rs!Server &
";WSID=LESTER34;DATABASE=" & rs!Database &
";TranslationName=Yes;QueryLogFile=Yes")

'--Sets the Jet workspace and database.
Set gobjWSjet = CreateWorkspace("", "admin", "", dbUseJet)
Set gobjDBjet = gobjWSjet.OpenDatabase(CurrentDb.Name, False)
Connection = True

ErrExit:
Exit Function
ErrHandler:
GeneralErrorHandler ("Connection")
Resume ErrExit
End Function


Public Function GetODBCRecordset(gSQL As String, rs As DAO.Recordset,
Optional AutoReconnect As Boolean = False) As Boolean

'--Assigns recordset to variable.
On Error GoTo ErrHandler

If AutoReconnect Then
If gobjConn Is Nothing Then
If Not Connection Then
GoTo ErrExit
End If
End If
End If

Set rs = gobjConn.OpenRecordset(gSQL, dbOpenDynaset, dbExecDirect,
dbOptimistic)
GetODBCRecordset = True

ErrExit:
Exit Function
ErrHandler:
GeneralErrorHandler ("GetODBCRecordset")
Resume ErrExit
End Function