Can't Watch CurrentDb.TableDefs("myTable").Fields

Question

In the Access VBA editor, I can add CurrentDb.TableDefs to the Watch window and browse to CurrentDb.TableDefs.[Item 14], which is table "myTable".  If I try to add CurrentDb.TableDefs("myTable") or CurrentDb.TableDefs(14) to thw Watch window, all the properties show as <Object invalid>.  I'm actually trying Watch the more deeply embedded object CurrentDb.TableDefs("myTable").Fields without having to open up huge nested objects starting form CurrentDb.TableDefs, but I can't do this if the  intermediate object CurrentDb.TableDefs("myTable" or 14) doesn't work.  What factors prevent certain watch expressions from working?

Answer 1

I'm not exactly sure what value in the expression you are watching. Did you try to evaluate the expression in the Immediate Window? It may very well be an expression that cannot be evaluated in the current scope.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

In the Access VBA editor, I can add CurrentDb.TableDefs to the Watch window and browse to CurrentDb.TableDefs.[Item 14], which is table "myTable".  If I try to add CurrentDb.TableDefs("myTable") or CurrentDb.TableDefs(14) to thw Watch window, all the properties show as <Object invalid>.  I'm actually trying Watch the more deeply embedded object CurrentDb.TableDefs("myTable").Fields without having to open up huge nested objects starting form CurrentDb.TableDefs, but I can't do this if the  intermediate object CurrentDb.TableDefs("myTable" or 14) doesn't work.  What factors prevent certain watch expressions from working?

Hi Polydom,

CurrentDb is a "one hit" object (if you understand what I mean).

Does it work when you first assign CurrentDB to a variable, and use that object:

    Set MyDb = CurrentDb
    MyDb.TableDefs("myTable") ...

Imb.

Answer 3

@Paul: The expressions I'm watching are in 2nd sentence of original post.  Yes, the expression evaluates in Immediate window.

@Imb: Yes, I found from another thread (different but apparently related problem) that setting a variable to the object helps in VBA code and the Immediate window.  Not sure what you mean by a "one hit" object.  From my understanding of reference variables, they are actually pointers to objects behind the scene.  Is the object actually changing location so as to make the "one hit" pointer obsolete?

Answer 4

@Imb: Yes, I found from another thread (different but apparently related problem) that setting a variable to the object helps in VBA code and the Immediate window.  Not sure what you mean by a "one hit" object.  From my understanding of reference variables, they are actually pointers to objects behind the scene.  Is the object actually changing location so as to make the "one hit" pointer obsolete?

Hi Polydom,

As far as I know the object (or pointer) is available for use, but is immediately relased after use.

When you loop through the collection of tables using:

    For Each TableDef in CurrentDb.TableDefs()

it does not work, whereas

    For Each TableDef in MyDb.TableDefs()

works!

Imb.

Answer 5

Thanks.  I just find the whole concept of an ephemeral database to be bizarre.  You usually don't want big, complex data objects to be ephemeral like basic variable data types.

Answer 6

Thanks.  I just find the whole concept of an ephemeral database to be bizarre.  You usually don't want big, complex data objects to be ephemeral like basic variable data types.

Hi Polydom,

Sometimes it is appropriate, you can make "one_liners", and I love them:

   This_value = CurrentDb.OpenRecordset("SELECT MAX(Veld1) AS Max_veld FROM Table1 IN .... WHERE ...")!Max_veld 

if necessary wrapped in a function to generalize it.

Not necessary to declare a db-varable.

Imb.

Answer 7

I have a short subroutine that cycles through a file of SQL statements for that.  When I use VBA to query or manipulate parts of the Access object model, I'm more trying to twig things that aren't part of the database proper, e.g., how Access presents data in Datasheet view, e.g., http://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_winother-mso_2010/vba-to-left-align-text-field-of-table-in-datasheet/a0f3693c-b070-40ef-a0e0-d73286e40700

Answer 8

Hi Polydom,

Since your original issue has been solved. If you have any other new issue, I suggest you post a new thread for your new issue so that more developers could see your thread. Thanks for your understanding.

Best Regards,

Terry

Answer 9

Understood, Terry.  I wasn't trying to post a new issue.  I was just giving an example of what I meant by twigging things that aren't part of the database proper.  In fact, the cited thread was the context in which the question of this thread arose.