By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Problem referring to existing tabledef object

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
  4.  


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

✓ answered by Stewart Ross

This MSDN question thread provides a full explanation of why CurrentDB throws errors when used to access tabledefs etc.

http://social.msdn.microsoft.com/For...9-6712762388ea

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."
-Stewart

Share this Question
Share on Google+
10 Replies


Expert 100+
P: 634
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
??

MTB
Jan 4 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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

Expert 100+
P: 634
Hi Again

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

MTB
Jan 4 '12 #4

100+
P: 332
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
  6.  
Jan 4 '12 #5

Expert Mod 2.5K+
P: 2,545
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.

-Stewart
Jan 4 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
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

Expert Mod 2.5K+
P: 2,545
@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).

-Stewart
Jan 4 '12 #8

Expert Mod 2.5K+
P: 2,545
This MSDN question thread provides a full explanation of why CurrentDB throws errors when used to access tabledefs etc.

http://social.msdn.microsoft.com/For...9-6712762388ea

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."
-Stewart
Jan 4 '12 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
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

NeoPa
Expert Mod 15k+
P: 31,494
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

Post your reply

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