Mysterious 3420 error

[Tim Johnson]

Hi there,

I consider myself fairly experienced with MS Access (I'm no MVP, but I can
definitely find my way around most scenarios); but this one leaves my jaw
dropped and my brain befuddled.

Any idea why Access 2003 would throw a 3420 error (object invalid or no
longer set) with the following code:

Sub Test()
Dim tdf As TableDef

Set tdf = CurrentDb.TableDefs("Table1")

Debug.Print tdf.Name
End Sub

?

Seems pretty simple to me: 1.) Declare variable, 2.) Set object variable,
3.) Print object name, but it throws an error.

I have thus far taken the following courses to identify the source of the
problem, in this order:

Operating under the assumption that this could be because of a corrupt
database, or an invalid declaration in another module, I quickly put this
code into a brand new mdb file (as well as the table) and it STILL throws the
error.

Tried using Option Explicit instead of Option Compare Database

I've checked the VBA references and can verify the following *seem* to be
intact:

Visual Basic for Apps, Microsoft Access 11.0 Object Library, OLE Automation,
Microsoft DAO 3.6 Object Library, and Microsoft ActiveX Data Objects 2.5
Library

In the Add or Remove Programs section of the Control Panel, I have opted to
"Repair Office", as well as using the "Change" button to remove and then add
the Access feature.


I haven't been able to find useful information on google regarding this
specific scenario; so any help is GREATLY appreciated.

Thanks in Advance,
Tim

 

[James A. Fortune]

Hi there,

I consider myself fairly experienced with MS Access (I'm no MVP, but I can
definitely find my way around most scenarios); but this one leaves my jaw
dropped and my brain befuddled.

Any idea why Access 2003 would throw a 3420 error (object invalid or no
longer set) with the following code:

Sub Test()
Dim tdf As TableDef

Set tdf = CurrentDb.TableDefs("Table1")

Debug.Print tdf.Name
End Sub

?

Seems pretty simple to me: 1.) Declare variable, 2.) Set object variable,
3.) Print object name, but it throws an error.

I have thus far taken the following courses to identify the source of the
problem, in this order:

Operating under the assumption that this could be because of a corrupt
database, or an invalid declaration in another module, I quickly put this
code into a brand new mdb file (as well as the table) and it STILL throws the
error.

Tried using Option Explicit instead of Option Compare Database

I've checked the VBA references and can verify the following *seem* to be
intact:

Visual Basic for Apps, Microsoft Access 11.0 Object Library, OLE Automation,
Microsoft DAO 3.6 Object Library, and Microsoft ActiveX Data Objects 2.5
Library

In the Add or Remove Programs section of the Control Panel, I have opted to
"Repair Office", as well as using the "Change" button to remove and then add
the Access feature.


I haven't been able to find useful information on google regarding this
specific scenario; so any help is GREATLY appreciated.

Thanks in Advance,
Tim

Try:

Sub Test()
Dim MyDB As DAO.Database
Dim tdf As TableDef

Set MyDB = CurrentDb
Set tdf = MyDB.TableDefs("Table1")

Debug.Print tdf.Name

Set MyDB = Nothing
End Sub

James A. Fortune
(e-mail address removed)

 

[Tim Johnson]

Try:

Sub Test()
Dim MyDB As DAO.Database
Dim tdf As TableDef

Set MyDB = CurrentDb
Set tdf = MyDB.TableDefs("Table1")

Debug.Print tdf.Name

Set MyDB = Nothing
End Sub

James A. Fortune
(e-mail address removed)

Thanks James,

Yes, this did work, although I'm still a little perplexed, as the other
method should work as well, shouldn't it?

AFAIK there is no difference between setting the database object as
CurrentDB and then using the object to identify a TableDef vs using the
CurrentDB.TableDefs method to set the TableDef object (other than one seems
to work in my case and the other doesn't). Should I be concerned at all by
this?

Thanks again,
Tim

 

[Brent Spaulding \(datAdrenaline\)]

The error occurs because the pointer CurrentDb returns is temporary and is
immediate discarded as soon as the line of code is done executing, thus
making the tabledef object variable invalid almost immediately upon setting
it.

This behavior has been around since day one ... here is a quick blurb about
it from MS ...
http://support.microsoft.com/kb/167173

The article is for A95 and A97, but the principle still holds true.
---
You can use CurrentDb in a With..End With block if you wish ...

Public Sub MyTest()
Dim td As DAO.TableDef
With CurrentDb
Set td = .TableDefs("Invoices")
Debug.Print td.RecordCount
End With
End Sub

But ... the following will raise the same error as you received ...

Public Sub MyTest()
With CurrentDb.TableDefs("Invoices")
Debug.Print .RecordCount
End With
End Sub

But ... amazingly enough ... the following does NOT raise the error ...

Public Sub MyTest()
Dim rs As DAO.Recordset
Set rs = CurrentDb.TableDefs("Invoices").OpenRecordset
Debug.Print rs.RecordCount
End Sub

----

Seems like voodoo magic if you ask me!! ... LOL! ... I'll bet there is a
logical explaination with the why, probably centered on the assumption that
once a recordset object is intantiated, it carves its own path (connection)
back to the datasource, so it does not need the temp pointer provided by
CurrentDb, where as a table def object needs that path (connection) in order
to maintain its instantiation.

 

[Tim Johnson]

Thank you for clarifying that Brent!

The error occurs because the pointer CurrentDb returns is temporary and is
immediate discarded as soon as the line of code is done executing, thus
making the tabledef object variable invalid almost immediately upon setting
it.

This behavior has been around since day one ... here is a quick blurb about
it from MS ...
http://support.microsoft.com/kb/167173

The article is for A95 and A97, but the principle still holds true.
---
You can use CurrentDb in a With..End With block if you wish ...

Public Sub MyTest()
Dim td As DAO.TableDef
With CurrentDb
Set td = .TableDefs("Invoices")
Debug.Print td.RecordCount
End With
End Sub

But ... the following will raise the same error as you received ...

Public Sub MyTest()
With CurrentDb.TableDefs("Invoices")
Debug.Print .RecordCount
End With
End Sub

But ... amazingly enough ... the following does NOT raise the error ...

Public Sub MyTest()
Dim rs As DAO.Recordset
Set rs = CurrentDb.TableDefs("Invoices").OpenRecordset
Debug.Print rs.RecordCount
End Sub

----

Seems like voodoo magic if you ask me!! ... LOL! ... I'll bet there is a
logical explaination with the why, probably centered on the assumption that
once a recordset object is intantiated, it carves its own path (connection)
back to the datasource, so it does not need the temp pointer provided by
CurrentDb, where as a table def object needs that path (connection) in order
to maintain its instantiation.

 

[James A. Fortune]

The error occurs because the pointer CurrentDb returns is temporary and is
immediate discarded as soon as the line of code is done executing, thus
making the tabledef object variable invalid almost immediately upon setting
it.

This behavior has been around since day one ... here is a quick blurb about
it from MS ...
http://support.microsoft.com/kb/167173

The article is for A95 and A97, but the principle still holds true.
---
You can use CurrentDb in a With..End With block if you wish ...

Public Sub MyTest()
Dim td As DAO.TableDef
With CurrentDb
Set td = .TableDefs("Invoices")
Debug.Print td.RecordCount
End With
End Sub

But ... the following will raise the same error as you received ...

Public Sub MyTest()
With CurrentDb.TableDefs("Invoices")
Debug.Print .RecordCount
End With
End Sub

But ... amazingly enough ... the following does NOT raise the error ...

Public Sub MyTest()
Dim rs As DAO.Recordset
Set rs = CurrentDb.TableDefs("Invoices").OpenRecordset
Debug.Print rs.RecordCount
End Sub

----

Seems like voodoo magic if you ask me!! ... LOL! ... I'll bet there is a
logical explaination with the why, probably centered on the assumption that
once a recordset object is intantiated, it carves its own path (connection)
back to the datasource, so it does not need the temp pointer provided by
CurrentDb, where as a table def object needs that path (connection) in order
to maintain its instantiation.

Brent,

Those are nice examples and the KB article is apropos, but your guess
that "probably centered on the assumption that once a recordset object
is intantiated, it carves its own path (connection) back to the
datasource, so it does not need the temp pointer provided by CurrentDb"
doesn't seem to correspond to what I've experienced. When a recordset
that is set using something like MyDB is passed to a function or
subroutine, Access seems to lose the database object associated with the
recordset. If the Database variable (MyDB) is also passed to the
function or subroutine Access seems to have no trouble with the recordset.

As to the With behavior, I'll guess that:

With CurrentDb

preserves the CurrentDb pointer within the scope of the With.. End With, but

With CurrentDb.TableDefs("Invoices")

preserves just the TableDef itself.

James A. Fortune
(e-mail address removed)