Access 2007 DAO VBA Error 3381 causes objects in calling methods to "break".
- by MT
---AFTER FURTHER INVESTIGATION---
"tblABC" in the below example must be a linked table (to another Access database).
If "tblABC" is in the same database as the code then the problem does not occur.
Hi,
We have recently upgraded to Office 2007.
We have a method in which we have an open recordset (DAO). We then call another sub (UpdatingSub below) that executes SQL. This method has its own error handler. If error 3381 is encountered then the recordset in the calling method becomes "unset" and we get error 3420 'Object invalid or no longer set'. Other errors in UpdatingSub do not cause the same problem.
This code works fine in Access 2003.
Private Sub Whatonearth()
Dim rs As dao.Recordset
set rs = CurrentDb.OpenRecordset("tblLinkedABC")
Debug.Print rs.RecordCount
UpdatingSub "ALTER TABLE tblTest DROP Column ColumnNotThere"
'Error 3240 occurs on the below line even though err 3381 is trapped in the calling procedure
'This appears to be because error 3381 is encountered when calling UpdatingSub above
Debug.Print rs.RecordCount
End Sub
Private Sub WhatonearthThatWorks()
Dim rs As dao.Recordset
set rs = CurrentDb.OpenRecordset("tblLinkedABC")
Debug.Print rs.RecordCount
'Change the update to generate a different error
UpdatingSub "NONSENSE SQL STATEMENT"
'Error is trapped in UpdatingSub. Next line works fine.
Debug.Print rs.RecordCount
End Sub
Private Sub UpdatingSub(strSQL As String)
On Error GoTo ErrHandler:
CurrentDb.Execute strSQL
ErrHandler:
'LogError'
End Sub
Any thoughts? We are running Office Access 2007 (12.0.6211.1000) SP1 MSO (12.0.6425.1000). Perhaps see if SP2 can be distributed?
Sorry about formatting - not sure how to fix that.
