UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run-time error 3420: Invalid Object no Longer Set    
 
   
LittleMojo
post Jun 15 2010, 05:21 PM
Post#1



Posts: 6
Joined: 11-June 09



Hi! Can anyone tell me why the following code produces the run-time error 3420 (Invalid Object no Longer Set)?
im CurrentTable As DAO.TableDef
Dim ReportData As DAO.TableDef
Set ReportData = CurrentDb.TableDefs("Basic Reporting Data")
For Each CurrentTable In CurrentDb.TableDefs
MsgBox CurrentTable.Connect
Next CurrentTable
MsgBox ReportData.Name
MsgBox Mid(ReportData.Connect, 11, Len(ReportData.Connect) - 23)
MsgBox Right(ReportData.Connect, 23)
Table "Basic Reporting Data" is a linked table into another Access database. I get the error whenever I attempt to reference any properties of the ReportData object. Referencing the connect in other ways (for example, the for...each loop through all the tables in the database) works fine, including for this table. It doesn't like this, and I can't figure out why. It's probably something really dumb on my part that I'm overlooking.
Would appreciate any help anyone could give! Thank you!!!
Go to the top of the page
 
pbaldy
post Jun 15 2010, 05:47 PM
Post#2


UtterAccess VIP
Posts: 5,077
Joined: 15-February 03
From: Nevada, USA


There are times you can get away without a variable for CurrentDb, and times you can't. I think this is one of those "can't" times. That's where I'd start.
Go to the top of the page
 
vtd
post Jun 15 2010, 08:13 PM
Post#3


Retired Moderator
Posts: 19,667
Joined: 14-July 05



I think the second call to CurrentDB destroys the first instance and therefore the Recordset ReportData is orphaned and cannot be accessed.
ry:
CODE
Dim db As DAO.Database
Dim CurrentTable As DAO.TableDef
Dim ReportData As DAO.TableDef
  
Set db = CurrentDb
Set ReportData = db.TableDefs("Basic Reporting Data")
  
For Each CurrentTable In db.TableDefs
  MsgBox CurrentTable.Connect
Next CurrentTable
MsgBox ReportData.Name
MsgBox Mid(ReportData.Connect, 11, Len(ReportData.Connect) - 23)
MsgBox Right(ReportData.Connect, 23)
  
' Clean up
Set db = Nothing
Go to the top of the page
 
LPurvis
post Jun 15 2010, 09:01 PM
Post#4


UtterAccess Editor
Posts: 16,280
Joined: 27-June 06
From: England (North East / South Yorks)


It's the TableDef object.
See this thread: When to use CurrentDb and when to set a variable
Cheers.
Go to the top of the page
 
LittleMojo
post Jun 16 2010, 11:48 AM
Post#5



Posts: 6
Joined: 11-June 09



Wow...it wasn't some weird quirky thing at all. There was a whole complex issue behind it. Not sure I completely understand why when setting an object reference to a persistent object (like a table) differs from something more transitory (like a recordset). Except maybe that the reference is becomes transitive...a reference to an object which in turn references another object.
But either way, I did change the code snippet I posted to include a with...end with and it worked fine. Now that I have an idea of what was wrong, I'll adjust my code accordinly. Thank you for the help!
Go to the top of the page
 
vtd
post Jun 18 2010, 06:26 PM
Post#6


Retired Moderator
Posts: 19,667
Joined: 14-July 05



Thanks for the link, Leigh. Took me for ever to read including other links posted in the thread.
For the Recordset/TableDef, I just mis-typed in the explanation... I knew the code was for TableDef and the O.P. wouldn't have the problem if it were Recordset since Recordset would persist fine.
Cheers
Go to the top of the page
 
vtd
post Jun 18 2010, 06:44 PM
Post#7


Retired Moderator
Posts: 19,667
Joined: 14-July 05



>>Just that I probably assume there will be nothing wrong in them to correct.
ad assumption... I am as bad as others. I know that my brain wanders around a bit lately.
I do try to re-read what I type before posting but then I don't pick up all the mistakes, either.
Cheers
Go to the top of the page
 
LPurvis
post Jun 19 2010, 12:08 AM
Post#8


UtterAccess Editor
Posts: 16,280
Joined: 27-June 06
From: England (North East / South Yorks)


It's OK, you have to allow a bit of poetic licence... I don't really expect you to be infallible. <
lthough typos and wanderings of thought don't concern me personally - just the threat of incorrect assumptions and mis-information.
(Says the man that can't leave even a typo in one of his posts - even when he spots it aaages later :-p)
Cheers.
Go to the top of the page
 
vtd
post Jun 19 2010, 03:04 AM
Post#9


Retired Moderator
Posts: 19,667
Joined: 14-July 05



Me too... (intentional grammatical error according to common usage! < ). If I see my typos later, I correct them.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 06:12 AM