Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vba link tables

Posted on 2013-02-05
7
Medium Priority
?
331 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 2000 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 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