How to validate table indexes?

[Mark Burns]

....on a linked table from code running in the front-end database to the
back-end tabledef?

Accessing the front-end tabldef object's indexes collection results only on
errors.

How do I check the table's indexes? ...or must I physically open the
Back-end to do what I need?

 

[Mark Burns]

Weird.
Ok, I found the right way to do this, but I still have a question.

Why is it that, at least for linked tables, doing
currentdb.tabledefs(xxx).indexes(yyy).<blahblah> works as expected, but

dim oTd as dao.tabledef
set oTd = currentdb.tabledefs(xxx)
debug.print otd.indexes.count <--- error - object no longer set

??
the oTd is not Nothing, but the oTd's propertys all point to an object not
valid or no longer set error...?

why??

- Mark

 

[Tim Ferguson]

How do I check the table's indexes? ...or must I physically open the
Back-end to do what I need?

Don't understand: what do want to check? How would you know if it checked
out okay or not? What symptoms are you seeing?

Tim F

 

[Dirk Goldgar]

Weird.
Ok, I found the right way to do this, but I still have a question.

Why is it that, at least for linked tables, doing
currentdb.tabledefs(xxx).indexes(yyy).<blahblah> works as expected,
but

dim oTd as dao.tabledef
set oTd = currentdb.tabledefs(xxx)
debug.print otd.indexes.count <--- error - object no longer set

??
the oTd is not Nothing, but the oTd's propertys all point to an
object not valid or no longer set error...?

why??

Because the Database object oTD is based on has gone out of scope and
been destroyed after the statement that calls CurrentDb is done
executing. Unlike Recordset objects, which maintain a reference to
their parent objects, TableDef objects need someone else to keep the
parent object alive for subsequent statements. Use a Database object
variable:

Dim oDB As DAO.Database
Dim oTD As DAO.TableDef

Set oDB = CurrentDb
Set oTD = oDB.TableDefs(xxx)

Debug.Print oTD.Indexes.Count
' ... do other stuff with oTD ...

Set oTD = Nothing
Set oDB = Nothing

 

[Mark Burns]

Don't understand: what do want to check? How would you know if it checked
out okay or not? What symptoms are you seeing?

Tim,

In our case we're checking that the index was (re-)defined properly (and
that the updated index definition was properly updated at our various
locations).

....but like I said...figured it out.

Thanks anyway!

 

[Mark Burns]

Dirk,

<comments inline>

Because the Database object oTD is based on has gone out of scope and
been destroyed after the statement that calls CurrentDb is done
executing.

OOOOoooooohhhh! <*ding* The light comes on />
Y'know, I've seen this before, and just never figured it out...

Thanks for hitting me with the "isn't it OBVIOUS?" board rather more gently
than I deserved.
;-)

Unlike Recordset objects, which maintain a reference to
their parent objects, TableDef objects need someone else to keep the
parent object alive for subsequent statements. Use a Database object
variable:

Dim oDB As DAO.Database
Dim oTD As DAO.TableDef

Set oDB = CurrentDb
Set oTD = oDB.TableDefs(xxx)

Debug.Print oTD.Indexes.Count
' ... do other stuff with oTD ...

Set oTD = Nothing
Set oDB = Nothing

Y'know how sometimes you can be staring at something for an hour and not see
what's wrong right there in front of you, and as somebody passes by and in
passing points out that you've misspelled a word...? This is one of
those...and only I've been missing the obvious for YEARS.
Doh!
Thanks!