vba link tables

hey guys, this caused me 2 hrs of frustration where i've got very little time on my hands, but i've managed to narrow it down to the cause and it's really quite unexpected. could yall help me explain why this works and the other way doesn't?

THIS WORKS
Dim tdfLocal As TableDef
Set dbCurr = CurrentDb
Set tdfLocal = dbCurr.TableDefs("AAList")
With tdfLocal
strDBPath = "C:\test.mdb"
.Connect = ";Database=" & strDBPath
.RefreshLink

Open in new window


THIS DOES NOT WORK
Dim tdfLocal As TableDef
'Set dbCurr = CurrentDb
'THIS LINE OF CODE CHANGED
Set tdfLocal = CurrentDb.TableDefs("AAList")
With tdfLocal
strDBPath = "C:\test.mdb"
.Connect = ";Database=" & strDBPath
.RefreshLink

Open in new window


from my understanding, setting an object variable is just like using a container. gives me an additional layer of abstraction. however i do not need to set a container if i will only use 1 object. if i'm only going to use CurrentDb, then why must i set it in a dbcurr object (database) variable for the code to work? i really can't understand this. is this an access bug? i do hope it is. or if it's not, then i'm going to learn something valuable here. thanks guy!!
developingprogrammerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
btw ... this tread is a bit LONG ...

See THIS

... but does cover the subject - and goes into related subjects (CurrentDB vs DBEngine).  Oddly, the question was asked by a guy I know in the San Diego Access Users group. And, the best answers are by Dirk Goldgar (MVP) who I have met a few times at the MVP Summit, in fact I will see him in a couple of weeks at this year Summit.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
It's not a bug :-)

In the first case:
Dim dbCurr as DAO.Database ' I assume you have this line of code
Set dbCurr = CurrentDb
The dbCurr object pointer is retained after that line of code executes, thus is valid for the remainder of the code.  The pointer (object) dbCurr remains valid because it keeps (persists) a reference to the database object returned by CurrentDB.

However,
Dim tdfLocal As TableDef
Set tdfLocal = CurrentDb.TableDefs("AAList")
For TableDefs and QueryDefs ... no such pointer is retained. Consequently, tdfLocal loses scope immediately following that line of code and is no longer valid ... thus the error 'Object Invalid or no longer set when you try to reference the .Connect property.

The bottom line is ... it depends on whether the object created
maintains a pointer to the database object that created it.  Case 1 - Yes. Case 2 (QueryDefs and TableDefs) ... No.

mx
0
 
developingprogrammerAuthor Commented:
whao mx, the discussion is really, REALLY long! but it's very good, throttling depending on how much it's relevant for us to go in detail and get full understanding.

yea, reading dirk's first post summarised quite well the whole concept - that DBEngine is a single reference by jet and CurrentDb is by Access, presumably to give a more consistent UI feel.

thanks so much for your help once again mx and you answered my question (of why CurrentDb would exist if the pointer was always new) before i asked it with your second post!! thanks!! = ))))
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome.  It was a good review.

Lots of misinformation and confusion in that post.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also see this post HERE

Note in particular the responses by LPurvis and datAdrenaline  ... two of the BEST.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
According to the last link (UA) I posted, QueryDefs do *not* have this issue - according to Brent (datAdrenaline) ...
I need to confirm that.

mx
0
 
developingprogrammerAuthor Commented:
whao yup, a lot more concise than reading the whole previous thread, but yup still need time to digest. the MVP title is definitely not an easy one to earn! = )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.