How to validate table indexes?

  • Thread starter Thread starter Mark Burns
  • Start date Start date
M

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?
 
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
 
How do I check the table's indexes? ...or must I physically open the
Back-end to do what I need?
Click to expand...

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
 
Mark Burns said:
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??
Click to expand...

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
 
Tim Ferguson said:
Don't understand: what do want to check? How would you know if it checked
out okay or not? What symptoms are you seeing?
Click to expand...

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! :-)
 
Dirk,

<comments inline>

Dirk Goldgar said:
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.
Click to expand...

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
Click to expand...

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!
 
Back
Top