Solved

vba link tables

Posted on 2013-02-05
7
311 Views
Last Modified: 2013-02-06
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!!
0
Comment
Question by:developingprogrammer
  • 5
  • 2
7 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 38858272
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 38858302
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
 

Author Comment

by:developingprogrammer
ID: 38858381
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 75
ID: 38860523
You are welcome.  It was a good review.

Lots of misinformation and confusion in that post.

mx
0
 
LVL 75
ID: 38861384
Also see this post HERE

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

mx
0
 
LVL 75
ID: 38862212
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
 

Author Comment

by:developingprogrammer
ID: 38862440
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question