ODBC error

[muppetmad]

My program is an Access 2003 database that connects to SQL 2000. I have a continuous form that shows between 10 an 25 records. I have coded an update button that when clicked updates a check box on each record displayed and then updates the form. Normally this works perfectly. However, sometimes when the button is clicked nothing happens for aproximately a minute then the user gets the error ODBC - call failed and no records are uodated. I have tried resetting the view from Dynaset to Dynaset (Incosistent Updates) and extending the timeout to 300. I have also tried extending the MSAceess Page timeout setting to 5000 instead of 5 under ODBC. Nothing has helped. Does any have any other solutions? Or a way to get a more specific error. I am really just guessing that it is a time out issue.

 

[tehNellie]

When you step through the underlying code, what command(s) are triggering the error? (ie can you post at least a snippet of the offending sub so we can see what your app is doing)

Are you using Windows Authentication in your Connection string? If so do all users get the error or only some of them?

How are you calling the Stored Procedure that performs the update? Is it wrapped up in a command object or are you just using objconn.execute "exec storedprocname"?

If you run a trace against the SQL Database can you see the connection/command being received by the server?

In short need more information to be able to make a useful suggestion as to where to look/what to do to identify and fix the problem.

 

[muppetmad]

Below is the code I am using to perform the action. This does not use a stored procedure. Who received the error and when appears to be random. The button can be used for several days by several approvers with out any error and the one day one user will have the problem. Also, I checked to see if particular records were affected as they relate to an employee record and I could not see and correlation. An employees records could be approved for several days and then suddenly they could not approve them. But the approver can go back and marks the check box one at the time. Also, I have notices that the problem does not occur when there are 10 or less records that need to be approved. But, it may approve 20 records one day and not approve 14 the next. That is why I think it is a timeout error.


Private Sub cmdAprv_Click()
Dim strSQL As String
Dim lngRvalue As Long
Dim lngCommit As Long
Dim conn As ADODB.Connection

On Error GoTo Err_Handler

DoCmd.SetWarnings False
strSQL = "UPDATE DailyLogs SET DailyLogs.DailyLogIsApproved = Yes WHERE DailyLogs.EmployeeID =" & cbWorkerName.Value & " AND DailyLogs.DailyLogDate = #" & tbDailyLogDate.Value & "#;"

Set conn = CurrentProject.Connection

conn.BeginTrans
conn.Execute strSQL, lngRvalue, adCmdText

If (lngRvalue > 0) Then
lngCommit = MsgBox("You are about to update " & lngRvalue & " records. Would you like to continue?", vbQuestion + vbOKCancel, "Confirm Update")

If (lngCommit = vbOK) Then
conn.CommitTrans
Else
conn.RollbackTrans
End If

End If

DoCmd.SetWarnings True
Me.Refresh

Set conn = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description
End Sub

 

[tehNellie]

My suspicion is you've got dead locking occuring. You're holding a transaction uncommitted waiting for user input (amongst other things that make the baby DB_Jesus cry) which isn't forthcoming and/or an error is occurring which is closing the form before the input to commit or rollback the transaction is sent to the db. This means you have a lock on the record concerned, preventing other changes being made, while the database waits for you to commit it or roll it back. In the mean time someone else tries to update the same record(s) and is effectively being put in a queue waiting for the first transaction to be resolved which is causing your application to time out.

I would strongly suggest wrapping that code up in a stored procedure and when the user says yes they want to update the record, excuting the whole thing as a single procedure. If for some reason you really, really dont want to use an SP, at least wrap the whole SQL statement up into a single command only send it to the Database once the user confirms whether they do or do not want to update the record.

Two Golden rules of Databases imo are:
1) Don't let your users/applications into the table structure, point them to Stored procedures/Queries or in the case of sql server at a pinch, a view.
2) Dont lock a table or a record on condition of user input (or update half a record). they will get halfway through and go to lunch or alt+f4 your application and leave a transaction waiting or not complete the rest of the update. Update everything at the same time or dont update it at all.