473,836 Members | 2,202 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 26281
32,584 Recognized Expert Moderator MVP
Absolutely. Please don't see my comments as dismissive of your offering. Just how I interpreted the results. It looks very much like we agree on that :-)

It's an approach I'll consider using in future certainly, and it's nice to know about it.
Sep 18 '21 #21
7 New Member
Maybe my English is not so good to explain what I mean. It's like you know how sweet honey tastes but you have difficulty explaining to someone who is used to sugar. They are both sweet.

Let us assume in a project, you need to make reference to CurrentDb in say 20 procedures in a VBA Project.

Conventionally you will have to write at least 21 lines of code

Declare a Public variable

Expand|Select|Wrap|Line Numbers
  1. Public ThisDb as DAO.Database
For the 20 times you need to use CurrentDb you have to write the assignment Statement

Expand|Select|Wrap|Line Numbers
  1. Set ThisDb = CurrentDb
If you are an Object destructor enthusiast, you will need an additional 20 lines of code for the 20 objects created

Expand|Select|Wrap|Line Numbers
  1. Set ThisDb = Nothing
OR you may wish to create only 1 reference to CurrentDb and use it for the entire session. In this case, you must check to make sure CurrentDb is not Nothing every time you need to use CurrentDb

Expand|Select|Wrap|Line Numbers
  1. If ThisDb is Nothing Then
    Set ThisDb = CurrentDb
    End If
So at the minimum you need 21 lines of code for reference to CurrentDb in 20 procedures. In an ideal situation, you may need up to 91 lines.

If you use a Public Property (which is actually an embellished variable definition) to call a module level variable, you will only need 7 lines of code even for 1000 references to CurrentDb in a VBA Project.

Expand|Select|Wrap|Line Numbers
  1. Private m_ThisDb as DAO.Database

Expand|Select|Wrap|Line Numbers
  1. Public Property Get ThisDb() As DAO.Database
    If m_dbThis Is Nothing Then
    Set m_dbThis = CurrentDb
    End If
  2. Set ThisDb = m_dbThisEnd Property
Just 7 lines of code and you are done with CurrentDb problems.
No more declarations, no more definition.
ThisDb has become a self-defining, self-checking variable.
It will self-destruct because it is actually a reference to a private variable.

If you do the conventional method of declaring and defining

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. Set db=ThisDb
every time you need a reference to CurrentDb, you will not get the performance (or even the ease of use) advantage.

That one declaration is enough
That one definition is enough

You just use ThisDb as a variable. The efficiency is superb.
Sep 18 '21 #22
462 Recognized Expert Moderator Contributor
I appreciate your attempts to try & explain why you believe this is so efficient.
However, did you read my initial reply in post #15?

If so, you will see that your code is no faster than using Set db=CurrentDb once for the entire session.
In my opinion, there is absolutely no reason to check that value each time it is used.
I set the value at the start of the session and don't destroy it as there is no benefit in doing so.
That means I just use db.Execute each time it is needed.

I'm not denigrating your code. It works perfectly and is about as fast as using CurrentDb

However - sorry but I don't see your code as any more efficient than the approach I use.
After repeated tests, it is no faster (the differences are negligible).
It also does not use less code.

My approach needs just 2 lines :
Expand|Select|Wrap|Line Numbers
  1. Public db As DAO.Database
  2. Set db = CurrentDb
Some years ago, I got into a similar discussion with an MVP who had claimed back in 2009 that using DBEngine(0)(0) was up to 5000x faster than using CurrentDb. See https://www.experts-exchange.com/art...ternative.html.
He continued to repeat the same comments on numerous occasions over subsequent years despite evidence to the contrary.
He was similarly adamant that his recommended approach was the best.
In his case, it was easy to prove him wrong. In fact using DbEngine(0)(0) is usually slightly slower than CurrentDb
Sep 18 '21 #23
7 New Member
We learn every day

Please where do you define
Expand|Select|Wrap|Line Numbers
  1. Set db =CurrentDb
so that all other procedures in all other modules know that it has been set?
Sep 19 '21 #24
462 Recognized Expert Moderator Contributor
I normally set it in the load event of the startup form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Set db = CurrentDb
  3. End Sub
That value is retained throughout the session.
There is no point clearing it by using Set db=Nothing or the advantages of assigning it in the first place are lost.

That's ALL you need to do ..
So what could possibly go wrong ..and how would any issues be solved?

1. On the rare occasions that I need to start the database using the shift bypass, I can set db by just typing that code line in the immediate window

2. Alternatively, (e.g. if no startup form is used), a process similar to yours could be employed adding a sub(or function) in a standard module:
Expand|Select|Wrap|Line Numbers
  1. Sub SetCurrDb()
  2.     Set db = CurrentDb
  3. End Sub
Then just type, SetCurrDb in the immediate window or assign a keyboard shortcut using an Autokeys macro
If this is a function, then it could of course be run from an autoexec macro instead.

3. If the value of db is lost after a program crash, error 91 occurs. If you really wanted to, that error could be used to reset the value.
However, during development I would prefer to know that there is an issue - so I don't do this.

For the same reason, I wouldn't use a tempvar for this purpose as tempvars usually retain their value after a crash

4. If you need to use an external database for any procedure, just set its value as a different variable when needed

Hope that covers everything
Sep 19 '21 #25
7 New Member
Yes. Thanks @Isladogs.

I guess that covers everything for development that is for private use and crashes can be managed from the backend.
Sep 19 '21 #26
462 Recognized Expert Moderator Contributor
No reason to limit the approach to private use. I have several commercial apps that have used this approach for almost 20 years without problems.
Due to thorough testing during development, program crashes are almost non-existent anyway.

Sorry but I didn't understand your reference to handling crashes from the BE.
Sep 19 '21 #27
462 Recognized Expert Moderator Contributor
I revisited this topic (over 18 months later) and have just updated my web article comparing the speed of CurrentDB vs DBEngine(0)(0) and now including the use of ThisDb. See https://www.isladogs.co.uk/speed-com...s-2/index.html

After additional tests, I now agree with you that using ThisDb is indeed faster than either DBEngine(0)(0) or CurrentDb,
Typical times are 14 milliseconds to 68 milliseconds to 320 milliseconds respectively to set a variable referencing the current database..
I apologise for disputing your results back in 2021.

However, as each of the methods is very fast, they all form an insignificant part of the time needed to run a typical query
For that reason, it makes little difference overall which methos is used....as long as the variable isn't set repeatedly in a loop
Apr 21 '23 #28

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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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: 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: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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.