Link to home
Start Free TrialLog in
Avatar of Milewskp
MilewskpFlag for Canada

asked on

Error 3420: Object invalid or no longer set.

I get the above error when I try to execute the third line of this snippet:

        Dim i as integer, tdf As DAO.TableDef
        Set tdf = CurrentDb.TableDefs("MyTable")
        i = tdf.Fields.Count
       
Any ideas?
Avatar of Paulo Pimenta
Paulo Pimenta
Flag of Portugal image

Set tdf = CurrentDb.TableDefs("MyTable")    <---------------- Is the error reported on this line?

I think you have lost some object reference, but it's not possible to confirm that. Can you please post the code before these lines?
SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
dqmq:

Yes ... that does work. Question is ... why?
The initial post *should* work - but I get the same error.

Your post works.  Seems they should be equivalent ...

mx

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Leigh ... thank you for chiming in.  But, how the HELL did you type all of that in just the few minutes from when I emailed you?? wow!  

Again ... thanks for the excellent explanation ...

mx
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Naturally I don't disagree with your right to disagree with me Markus ;-)

However I'll just mention a couple of things.

Try not to focus too hard on my use of the "connection" phrase.  My aim was to describe a generic OM concept.
I chose a word that would hopefully seem API independent (i.e. not DAO/ADO specific).  I obviously failed - as it shrieks of ADO lol.  
I suppose I could have perhaps used "Parent", (and "Catalog" wants to pop in to my head - but again... that's ADO :-s).  Of course "Owner" means something entirely else in a database... And "Database" itself was just too obvious and not the sentiment I wanted to convey.  You're seeing the problem I'm having with abstracting?  (There just aren't any words left!)
I definitely don't perceive a TableDef to have a *connection* to a database in the technical sense.  In fact, even the ADO equivalent (well ADOX) Table doesn't have a connection object - but a belongs directly to a parent Catalog (OK - *it* has a connection, but you gotta open the thing somehow :-).
Suffice to say that DAO was my first data love - and remains so my default choice (my dalliances with ADO came in later years :-)

It's also true though that I have less time under my belt than.. well - almost everyone around here and in the business in general lol.
Wow - actually 10 years already though... Man that was fast. (Scary).
(Where's Dave gone - he used to help me feel like an old timer lol).
Perhaps some "older" documentation that definitively described some of DAO's nuances floated around in the early days that I've not been able to lay my hands on.  I'd love it.
Mainly, in the absence of a definitive source, I tend to investigate things until I'm satisfied - armed with a theory and then push for empirical evidence to support it (which doesn't sit so well with the ex-mathematician in me - but you know).
So I'm always all ears for any good history lessons.

Mainly though - I need to ask... what is it you're disagreeing with Markus? lol :-p
We've not disagreed at all on the nuances of using CurrentDb - we've said the same things. ;-)
It's the determination of the TableDef member scope being a bug?
Are you saying that it's *because* TablesDefs are the *default* collection that they are prone to the described behaviour of scope?
My point is that the same issue doesn't present with, say, the QueryDefs collection.
It's another child collection of a Database - consisting of persisted Data objects.  Its new members also need to be refreshed unless automatically done so under a new CurrentDb object.
The only substantial difference as far as DAO goes would perhaps be the ability to create one (tables) using DDL and not the other (queries).  But DB objects can always be created from other database "connections" (:-p) and remain outside the collection of an already open object.

To my mind - that the problem code structure would fail just on the *default* collection members is either a bug - or inconsistent implementation by Microsoft.  (I'm entirely willing to accept that those as different things - indeed I've argued in favour of such distinction with other MVPs regarding certain Jet behaviour).  
But they're close. :-)  And if not an out-and-out bug, I'd still say it's a poor lack of consistency in DAO's construction / execution.

And when you mentioned:
>> "the code above shows another way to obtain the field count by using a one-liner -- I'm surprised he didn't mention that"
Well, isn't my
    i = CurrentDb.TableDefs("MyTable").Fields.Count
doing just that? :-D

Finally...
A Belated Happy Birthday! :-p
To all,

Obviously dqmq knew all of this already!
:-)

Thaks to all, for the great (If not over my head) explanations!
;-)
<Bookmarked!>

JeffCoachman
> a theory and [...] empirical evidence to support it

Well, here is one: TableDef objects need a live Database object, the one from which they have been created, because they implement other .Create??? methods, which are passed back to the parent. QueryDef objects, on the other hand, implements only the .CreateProperty method (which is direct library call); all other methods are (empirically) passed directly to Jet. That's why queries are called "pass through", didn't you know? (^v°)

Queries have is no permanent need for the Database object, other than to obtain the object builder... More evidence: the CreateQueryDef method automatically appends the new query to the collection when you specify a name.

More seriously, this offers other solutions to the original question (see below). They do not work if changes are to be made to the fields, naturally.

> it's a poor lack of consistency

I see your point, of course. Perhaps there were more than one reason to deprecate CurrentDb (around Access 95, or was it Access 2.0?) and today to push ADO as a more consistent object model (although it's incomplete for mdb files). And if you move from "bug" to "lack of consistency", we no longer disagree!

The only point remaining is then the validity of using a static Database object, as you suggested. First of all, it seems totally redundant with using DBEngine(0)(0) directly. What is the advantage? Also, have you ever tried to measure the overhead of CurrentDb? I often use static recordset objects, especially table-type recordsets in order to implement fast lookup functions, and hence a static Database for the BE (or for a temp-table DB). However, I've abandoned the idea for the FE and now use CurrentDb / DBEngine(0)(0) (almost) everywhere.

[This is moving quite far from the original question, I hope Milewskp doesn't mind...]

Finally, I knew there was the one-liner somewhere, but it was lost among so many other lines...

Cheers!
(°v°)
    Dim rec As DAO.Recordset
    Set rec = CurrentDb("Table1").OpenRecordset
    i = rec.Fields.Count
    
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", "TABLE Table1")
    i = rec.Fields.Count

Open in new window

Avatar of Milewskp

ASKER

Thanks!
I'll split points between dqmq who offered a solution, and to LPurvis and harfang for the insights.
Thanks for this fun thread! And success with your "current database"!
(°v°)
Indeed - apologies to Milewskp for the hijack...

Just to be definite on this point for readers... DAO really is very much MS' data access API of choice these days (again!)  ADO's development cycle being long since over (but remaining firmly in support - thankfully).  By comparison - since the ACCDB's introduction - DAO is indeed advancing again (albeit it mainly just to support new features of the database engine rather than fixes or new functionality - IMO both DAO and ADO each still have their advantages in certain scenarios.  Indeed am I guilty of polyamory? lol).

I'm willing to go along with your theory Markus.  (And to at least partially sit with "lack of consistency").
However ;-)  To my mind that the object requires a live database object because of it's *potential* need to created child database objects (fields or indexes) - speaks to a possible reason.
I'd still expect *those* methods which require that live parent object to fail if executed without such an object...
i.e. the .Create??? methods.

e.g. On a tabledef object with either a destroyed or lost scope parent db object...
executing:
tdf.CreateField("FieldX", dbText, 35)
and being able to receive a raised and handled error:
"Error XXX: Object has no parent database object."
But still being able to peruse the fields collection and other properties of the definition as it was when opened.

That the object itself simply drops out of scope due to it's subsurface requirements - speaks of such a poor lack of consistency - that it still has an air of "bug" about it for me. ;-)
That Container Objects actually behave the same just give me further cause for wonder.
(Yes - they're fundamental Database objects, but their child collections are built in and cannot be appended to... why then do *they* need the live database?  Less obvious...)

But we can't know the thinking / decision making / time saving that went on behind the scenes back then.
As an official standpoint (and to give MS the benefit of the doubt) I'll go with "poor consistency" for now. :-)

Perhaps a shame this didn't come up a few weeks ago... ;-)

Cheers all!
I had the previous post partly written for a long while (sorry for the posting after you've closed up Milewskp).
Been a busy weekend.
Yes, the plot thickens... and I can see you hold on to the "bug" view of things. About your scenario with a detached TableDef, knowing Microsoft, you would most certainly get "Object invalid or no longer set" (meaning the parent database object, but not stating it). Wait, this looks familiar...

About the Containers, I was meaning to...

Perhaps we should take this elsewhere:
For Experts: Exploring the DAO object model -- http:/Q_23357524.html

See you there!
(°v°)