Solved

vba link tables

Posted on 2013-02-05
7
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 
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

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.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

729 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