refreshing ADOX cat.tables

[formerTexan]

Hi,

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

For Each tbl In cat.Tables
    If tbl.Type = "TABLE" Or tbl.Type = "LINK" Then            ' No views, queries or system tables.
        rs.Open (tbl.Name), cnn, adOpenForwardOnly, adLockReadOnly

If I use the ADOX catalog method to raise the Tables collection (as outlined in the code above), a problem occurs if a table has just been deleted. A reference to the deleted table shows up in the collection. But an error is then raised when an attempt is made to open a recordset based on the now nonexistent table.

So is there anyway to update the Tables collection without doing a Compact/Repair or closing and reopening the application?
Using Cat.Tables.Refresh occurred to me, but it doesn’t fly.

I’m sure there is a more lucid way of describing what is actually going on, but hopefully I’ve relayed the gist of the problem.

Thanks for any suggestions,

Cheers,
Bill

 

[ZmrAbdulla]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office10052000.asp

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[formerTexan]

Thanks Zameer. Its a useful article and I will keep it on hand for reference. However it doesn't address what to do about deleted tables.

If you delete a table ("tblTEST") and then open the MSYSOBJECT table, you'll find that a tblTEST record remains. However it has been renamed to "~TMPCLP.....", presumably as a flag for deletion upon close or compaction of the application

So when subsequently looping through the cat.tables collection, tbl.name returns ~TMPCLP399641 and this throws a spanner into the following attempt to open a recordset.

So for now and until a better idea appears, I have just added another condition into the loop to exclude "deleted" tables.

If tbl.name <> "~TMP*" then

This got me to thinking about whether "deleted" tables were recoverable and I turned up the following link with a method for doing so. Might be useful for those who have fingers faster than the brain.

http://www.everythingaccess.com/tutorials.asp?ID=Undelete-Tables-and-Queries-in-Access

Cheers,
Bill

 

[ZmrAbdulla]

That is a good idea..
However.. the temp table remains until db closes. So one should be careful of that.

Unfortunately the link is blocked by my internet provider.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.