Solved

vba link tables

Posted on 2013-02-05
7
275 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 - Access MVP) 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now