Whay does an update CRASH Access?

  • Thread starter Thread starter OceanView
  • Start date Start date
O

OceanView

Running Access 2003 on XP, doing a select from a table and passing the
results to this subroutine to update another table. When running in
debug
mode, it works. When I stop it an anaylze the update statement, copy and
paste into a new query, it works. Running without debug turned on,
Access
crashes (on three different installations of Access).

What I know to be true:
1. the input record set contains records, all fields are non-null
2. the datatypes are correct
3. it crashes on the first record

Any clues?
----
Sub UpdLicenseGrantedOutput(rstOutput As Recordset)

Dim SQL_Upd As String
Dim dbs As Database
Set dbs = CurrentDb

' Enumerate the specified Recordset object.
With rstOutput
Dim rc
Do While Not .EOF And Not .BOF
rc = rc + 1
SQL_Upd = " Update tbllicenseRightsSummary Set licensegranted =
"
& .Fields(4) & _
" where tbllicenseRightsSummary.Country = '" &
..Fields
(1) & "' " & _
" and companyName = '" & .Fields(0) & "'"
On Error GoTo updLicenseGrantedOutputError
dbs.Execute SQL_Upd, dbFailOnError <------ Access crashes
.MoveNext
Loop
End With
rstOutput.Close
dbs.Close

Exit Sub
updLicenseGrantedOutputError:
MsgBox "updLicenseGrantedOutput: Update failed: " & Err.Description &
vbNewLine & "Sql=" & SQL_Upd

End Sub
 
Hmm: you're performing an update in a loop. We don't know what's in the
rstOutput recordset, so perhaps something in there is being modified?

Is there any chance of replacing the entire thing with just an UPDATE query
statement? Perhaps it could be done with a JOIN?

Presumably you have tried the obvious things, like ensuring that Name
AutoCorrect is off, and doing a compact/repair.

There's a couple of things that could be done differently in the code, such
as settup up the error handler before the loop. Also, closing the default
database is not correct, but it does not to be de-referenced even if control
gets passed to the error handler. We are assuming that the input recordset
is the correct initialized (at the right record.) Since it was opened in a
preceeding procedure, I'm not sure if you want to destroy it in this one.

So, a slightly modified version of the code is below. I doubt it will make
the difference you need though:
Sub UpdLicenseGrantedOutput(rstOutput As DAO.Recordset)
On Error GoTo updLicenseGrantedOutputError
Dim SQL_Upd As String
Dim dbs As Database
Dim rc As Long
Set dbs = CurrentDb

' Enumerate the specified Recordset object.
With rstOutput
Do While Not .EOF
SQL_Upd = "UPDATE tbllicenseRightsSummary " & _
"SET licensegranted = " & .Fields(4) & _
" WHERE ((tbllicenseRightsSummary.Country = """ & _
.Fields(1) & """) AND (companyName = """ & _
.Fields(0) & """));"
dbs.Execute SQL_Upd, dbFailOnError '<--- Access crash
rc = rc + 1
.MoveNext
Loop
End With

rstOutput.Close 'Are you sure you want to do this?

Exit_Handler:
Set dbs = Nothing 'Don't close! dbs.Close
Exit Sub

updLicenseGrantedOutputError:
MsgBox "updLicenseGrantedOutput: Update failed: " & _
Err.Description & vbNewLine & "Sql=" & SQL_Upd
Resume Exit_Handler
End Sub
 
Could it simply be matter of disambiguating dbs?

Dim dbs As DAO.Database

I could be wrong, but always start w/ simplest first...

good luck,

gary
 
Thanks, Allen

I think I found a solution, but still not entirely sure of the cause.
This is inherited code and, yes, does violate some conventions like
'scope of control' by closing the recordset that were passed, though
most of them would happen after the problem point. Because of that I'm
reluctant to do any rewrites. (I won't go into it, but this is a duct-
tape situation!)

What I found is that the passed query should not have been opened in
'read-only' mode, even though it's not being updated. Removing that
seems to fix it. I don't know why this would cause a problem. It's
possible I just moved the problem rather than fix it, but for now, the
duct tape is holding!
 
Back
Top