473,836 Members | 2,204 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem referring to existing tabledef object

2,322 Recognized Expert Moderator Top Contributor
Hi all my favorite Access elves, and happy NewYear.

Im trying to modify a tabledef by code, and I seem to be running into some trouble.
I am still in the early phases of getting an understanding on how to do it, and im allready stumped at simply refering to the tableDef.

I have tried this:
Expand|Select|Wrap|Line Numbers
  1. With CurrentDb.TableDefs("hist_tbl_Obs")
  2.   Debug.Print .Name & ":" & .SourceTableName
  3. End With
But I get the error:
Object is invalid or no longer set. (Error 3420)
The same happens for the code:
Expand|Select|Wrap|Line Numbers
  1. Dim T as dao.tabledef
  2. set T=CurrentDb.TableDefs("hist_tbl_Obs")
  3.   Debug.Print T.Name & ":" & T.SourceTableName

However if I use:
Expand|Select|Wrap|Line Numbers
  1. Dim t As TableDef
  2. For Each t In CurrentDb.TableDefs
  3.   If t.Name = "hist_tbl_Obs" Then
  4.     Debug.Print t.Name & ":" & t.SourceTableName
  5.   End If
  6. Next
It will run just fine, and output the values requested.

Maybe my brain has melted during the holidays, but I simply cannot wrap my head around why the 2 first examples are giving the error, and whether there is a less clumsy way to proceed then the last posted bit of code.
Jan 4 '12
27 26280
32,584 Recognized Expert Moderator MVP
Stewart is quite correct and possibly a little kind when he says the explanation he quoted was similar to what I was saying. It's what I should have said. Unfortunately I was quite pressed for time at that point so rushed off a brief explanation and suggested checking the Help System for more details.

Pointing out that the result was a function wasn't the important point - as that was about the non-persistence of the object returned unless assigned to an object immediately. The quoted reply in Stewart's post explains it perfectly. Particularly the second line. The only part I find strange is that using the result in a With section of code doesn't cause the object to persist across that section at least. It appears it does not though, so that's how it needs to be handled (IE. by assigning the object to a variable).

Now I've had time I've checked the Help System for CurrentDb() and it doesn't explain this point at all (so even sending you off there wasn't much help I'm sorry to say) :-( However, I do recall picking this point up some while ago now, and I'm pretty sure it came from somewhere in the Help System (as I find I very rarely have to look elsewhere for Access or VBA based info).
Jan 5 '12 #11
7 New Member
To use CurrentDb globally consistently in a project, write the following in a module

Declaration section:

Expand|Select|Wrap|Line Numbers
  1. Private m_dbThis As DAO.Database
In the Procedure section:

Expand|Select|Wrap|Line Numbers
  1. Public Property Get ThisDb() As DAO.Database
  2. If m_dbThis Is Nothing Then
    Set m_dbThis = CurrentDb
    End If
    Set ThisDb = m_dbThis
    End Property
Sep 16 '21 #12
32,584 Recognized Expert Moderator MVP
Hi Nsenor. Welcome to Bytes.com.

I can understand where you're coming from, using a class module to handle this, but there are two problems :
  1. You only include a part of what would be required in order to use it.
  2. There really is no benefit in designing it as a class. It doesn't offer any value over and above the simple code that you illustrate where a DAO.Database object is declared as Private (Public is often preferable here in a Standard Module of course.) and you have a procedure (or often just a repeated snippet of code) that checks if it's set or not and sets it only when not.
    Essentially :
    Expand|Select|Wrap|Line Numbers
    1. Public X As DAO.Database
    2. ...
    3. If X Is Nothing Then Set X = CurrentDb()
    4. 'X is now ready for use.
Sep 17 '21 #13
7 New Member
It is not in a class module. You write the declaration and the procedure in a standard module.

The advantage is that whenever you need to use CurrentDb, you call ThisDb directly and it will return the same memory location every time without the uncertain behavior of using CurrentDb.

It solves the problem of the original question with ease of use i.e. declare once and use every time, everywhere in the project as a stable CurrentDb clone.

In your construct, if X is not a property and you don't have a private holder for its value, you have to check for Nothing every time you need to use x, whereas the property can do that for you.
Sep 17 '21 #14
462 Recognized Expert Moderator Contributor
I would also have expected that code to be in a class module but can confirm it works in a standard module.
However, I've done a speed comparison test on both sets of code from posts #12 & #13 adapting my test database CurrentDb vs DBEngine(0)(0)

After testing repeatedly, I can state that there is no significant difference in the times for each method - if anything @nsenor's code was very slightly slower:

However, I do accept the point about checking If X Is Nothing each time. I don't do this in my own databases

Instead I just use this code once
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
Using that approach shaved 0.1 s off the time to 8.74s. Every little helps!

On the relatively rare occasions that need to use a different database than the current db, I use a separate variable for the purpose

If you are interested in the results for all the tests done originally together with the test database, see the link above
Attached Images
File Type: jpg Capture.jpg (163.7 KB, 957 views)
Sep 17 '21 #15
7 New Member
You do not have to ever declare any
Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO,Database
  2. and
  3. set db = ThisDb
If you use the ThisDb construct
You just use ThisDB as if CurrentDb was a well formed Object
Expand|Select|Wrap|Line Numbers
  1. Set tbl = ThisDb.TableDefs("SomeTable")
  2. Debug.Print tbl.Fields("SomeName"),Value
Expand|Select|Wrap|Line Numbers
  1. With ThisDb
  2. ...
  3. someVar = .SomeProperty
  4. ....
  5. End with
and you won't run into any problems. You can do that as many times and in as many places in the same project as you need to. Every time you make a declaration and an assignment, it is a redundant repetition and it tells on the speed

If the construct where to be in a class, unless it is PublicNotCreata ble (which is more or less a glorified std module), it has to be instantiated to be used. That (I think) would be too much hassle for just CurrentDb.
Sep 18 '21 #16
32,584 Recognized Expert Moderator MVP
Hi people.

That's very interesting. Like IslaDogs I had no idea a property could be set within a Standard Module. This is very interesting in itself.

My own handling of the value of CurrentDb() works very slightly differently, in as much as I have places in my code which are entry points and thus I check and set a whole bunch of variables that the rest of my code expects to be present & correct. From that point forward (Logically rather than positionally.) I use the object variable and know that it's set appropriately. Thus there is no requirement to check the validity of the value each time it's referenced.

Nevertheless, I like the flow of this approach. Whether it's used exclusively or just at those points where it's necessary to check / set upon entry hardly matters.

Your timings are interesting, and worth considering in some circumstances, but my principal interest is in logical and manageable code for portability between developers etc so I have to say I like this approach. Time delays of a millisecond, or even measured in such, are not an issue for me generally. Not unless, as in your tests, they're used so many times that the delay becomes significant.
Sep 18 '21 #17
32,584 Recognized Expert Moderator MVP
We cross-posted there.

I agree you can use it that way - but you aren't constrained to do so if you prefer, as I do, the approach where it's reliably set in an object variable.

NB. Please remember to use the [CODE] tags as you have in previous posts. I have a feeling you may have more suggestions in you so it would be worth saving me the effort of fixing them all afterwards ;-)
Sep 18 '21 #18
7 New Member
Thanks for the editing NeoPa

My reply box is in plain text now for a reason i cannot fathom. I will revert to adding {CODE} tags manually.

Of course, there is no compulsion in the use of the method

The beauty of this Construct is more evident if you were to use a resource like Excel WorksheetFuncti on in Access frequently as I do. It has a problem that you cannot declare it once as a New object. Every time you need to use it, you have to assign it and invoke Excel Application.

Expand|Select|Wrap|Line Numbers
  1. Public fn_Sht as Excel.WorksheetFunction

Expand|Select|Wrap|Line Numbers
  1. Set fn_Sht = Excel.Application.WorksheetFunction 

This calls Excel every time you invoke it. You may reduce resource consumption a little by declaring an Excel Application Object but the master stopper is to declare the WorksheetFuncti on first as a module level variable and then as a Public Property that exposes the private variable (after checking for Nothing(ness).

Expand|Select|Wrap|Line Numbers
  1. Private m_fn_Sht as WorksheetFunction

Expand|Select|Wrap|Line Numbers
  1. Public Property Get fn_Sht() As Excel.WorksheetFunction
  2.     If m_fn_Sht Is Nothing Then
  3.         Set m_fn_Sht = xlApp.WorksheetFunction
  4.     End If
  5.     Set fn_Sht = m_fn_Sht
  6. End Property

The drawdown on resources is very noticeable at 1st use after Reset or Startup. After that, you hardly notice you are working in Excel from Access.

This is what informed my use of it for CurrentDb. Although the stress on resource is not an issue, but the ease of Access and shortness of code is appealing.
Sep 18 '21 #19
462 Recognized Expert Moderator Contributor
Thank you for offering this as an alternate approach but I'm not convinced it has any real advantages so far.
I can see you are very keen on this approach but I've still not seen any benefit compared to the standard approach that I use.
Execution times are similar in each case. Your approach doesn't mean less code.

I was interested in timing in case this approach was significantly faster or slower.
My point was that the differences were so small as to be irrelevant.
Sep 18 '21 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

by: pablo | last post by:
Dear NewsGroupers, I am relatively new to OOP and cannet get my head around this problem. I have two classes. Class Child extends Parent. There is no constructor for the Child class. So when I create a child object the constructor for the parent object is called. That works fine. But now I have the problem that I want to add an already existing Parent object to create a new Child object. How can this be done?
by: Rainman | last post by:
I have an object ($obj) that contains members 'sunday', 'monday', 'tuesday', 'wednesday', etc. I can refer to them in my code as $obj->sunday, etc. But I want to refer to these members in a loop, such as: $dow = array('sunday','monday','tuesday',...); foreach ($dow as $day) { print $obj->$day; }
by: Robert Ginsburg | last post by:
I am upgrading an existing .NET 1.1 project to 2.0. (yes the project continues to work perfectly in 1.1). The project includes a reference to an assembly that manages communication to several databases and COM objects. Since we need to be able to update the COM objects, the COM interop is hand coded and is "late bound". That is so say, the progID is interograted to create the object and the instances of the object are invoked by...
by: bobwooderton | last post by:
Either I am using the wrong approach or I just don't get it. so, the 'this' in 'this.changeState()' on the second to last line of the constructor code refers to the object _referObject, instead of the quickEntryObject---I need to fix this. Do you think I have a paradigm error, or is there some way to get the desired effect? p.s. when I use: this._referObject.onclick = this.changeState; I no longer have access to the actual object...
by: awmb | last post by:
Hi I'm trying to create an event handler for an object, ListBox1, which looks something like this: protected void ListBox1_DataBinding(object sender, EventArgs e) { foreach(ListItem item in ListBox1) { if (item.Value.Equals("xyz"))
by: Roger (Bordeaux) | last post by:
Hello, You will find behind an example of the problem. The defined JSON object is multidimentinnal. The "weeks" object contains a serie of dates with week number and monday's date. I want to write a loop to display the dates. But the alert(typeof(b))
by: VolkerS | last post by:
Hallo, I need helping adding an existing Interface to an object I dispatched from a COM-server via win32com in Python. The Code for this in VisualBasic looks like that: Private Obj_1 As Obj1_LIB.Impl_1 Private mHelper As Object Private m_applJob As Object Private m_applModul As interface_Module //interface_Module is described in an IDL/TLB-File Private cont As Boolean
by: RSH | last post by:
Hi, I have a situation where I have multiple objects created from concrete classes: // Concrete implementations of the Abstract class ApprovalChain MarketingApprovalChain MktgAppChain = new MarketingApprovalChain(); AccountingApprovalChain AcctAppChain = new AccountingApprovalChain(); I also have a Department enum
by: AboutJAV | last post by:
Hi, I got a crystal report (.rpt + .cs files) and the dataset (xsd + cs + designer.cs + .xsc + .xss). I added them to my project and changed the namespace values in the cs files. When I added a method to declare a rpt class object like my crystal report file/class is MyCrystalReport.cs and the
by: Robert Bossy | last post by:
bvidinli wrote: If I understand correctly you want default values for non-existing keys. There are two ways for achieving this: Way 1: use the get() method of the dict object: conf.get(key, default) which is the same as: conf if key in conf else default
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.