473,854 Members | 1,830 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 #1
27 26303
639 Recognized Expert Contributor
Hi Smiley

It would seem you need to do this
Expand|Select|Wrap|Line Numbers
  1.     Dim T As DAO.TableDef
  2.     Set T = CurrentDb.CreateTableDef("hist_tbl_Obs")
  3.     MsgBox T.Name & ":" & T.SourceTableName

Jan 4 '12 #2
2,322 Recognized Expert Moderator Top Contributor
Thank you for your promt reply.

The issue is that I want to (through code) modify the tabledef, in such a way as to convert the existing autonumber field KEY_OBS to a Number, Long, and then create a new autonumber field RowID.

As far as I can see you are creating a new tabledef, not modifying the existing one.
Jan 4 '12 #3
639 Recognized Expert Contributor
Hi Again

Mabe this then
Expand|Select|Wrap|Line Numbers
  1.     With CurrentDb.QueryDefs("hist_tbl_Obs")
  2.         MsgBox .Name & "  :  " & .SQL
  3.     End With

Jan 4 '12 #4
332 Contributor
Expand|Select|Wrap|Line Numbers
  1. Dim T As DAO.TableDef
  2. Dim dbs As Database
  3. Set dbs = CurrentDb
  4. Set T = dbs.TableDefs("hist_tbl_Obs")
  5. Debug.Print T.Name & ":" & T.SourceTableName
Jan 4 '12 #5
Stewart Ross
2,545 Recognized Expert Moderator Specialist
Mariostg's answer mirrors my own experience with Access tabledefs. I found in one of my projects that using With CurrentDB led to an object not set error, as it did for you Smiley. As per mariostg's answer, setting a database variable to CurrentDB instead then using that variable for the tabledefs resolved the 'object not set' issue.

After experiencing this issue I don't use the CurrentDB object in With or For Each statements at all, as it does not appear to give consistent results when used that way.

Jan 4 '12 #6
32,584 Recognized Expert Moderator MVP
This is all about the function (Not object but function call) CurrentDb(). The Help System has something to say on this, but in brief it's always a good idea to return the object once and store it for future use. Mario's code does this and will be found to be entirely reliable.
Jan 4 '12 #7
Stewart Ross
2,545 Recognized Expert Moderator Specialist
@NeoPa: I should indeed have been clear that the CurrentDB method of the application object is not an object in itself, but a method (function) of the Application object which 'returns an object variable of type Database that represents the database currently open in the Microsoft Access window' (quote from MS Help for Access).

The shorthand use of 'object' in referring to CurrentDB relates to what it returns - a database object.

I agree entirely that mario's code will be 100% reliable - but my main point is that use of the CurrentDB method without assigning it to an object variable of type database will not be 100% reliable, and gives rise to peculiar object not set errors when accessing properties of objects which are themselves present and correct in every other respect (such as the tabledefs object collection, which Smiley's question relates to).

Jan 4 '12 #8
Stewart Ross
2,545 Recognized Expert Moderator Specialist
This MSDN question thread provides a full explanation of why CurrentDB throws errors when used to access tabledefs etc.


As the first reply makes clear, in terms similar to NeoPa's:

"CurrentDB is *NOT AN OBJECT* - it is a METHOD that returns a DIFFERENT POINTER to the underlying database object for EACH INVOCATION of CurrentDB.

This is why you can't use it like that. If you don't store that particular pointer in a variable, it goes away instantly (as soon as the expression evaluation is completed), and any underlying/child object references go with it."
Jan 4 '12 #9
2,322 Recognized Expert Moderator Top Contributor
I was so focused on the TableDef aspect of it, that I never thought to look more closely at the currentDB object. I have used the Currentdb many times before, but in those cases it was usually about adding a tabledef, or doing a Execute. I guess I felt comfortable using it, so I never considered it could be the source of my trouble.

Thank you all for your time and help.
Jan 4 '12 #10

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: 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: 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: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.