DAO.TableDefs & Fields, and scope

[David Mueller]

I often use the code toward the bottom of this post in my subs and
functions. For this one particular function it seems that as soon as I set
a DAO.TableDef object it falls out of scope. Sometimes, I can't even set the
TableDef object - but the table clearly exists. My code just doesn't work
consistently.

I saw a similar post that suggested the use of ....

Dim db = DAO.Database
Set db = currentdb()

.... would solve their problem (and apparently it did).

Can anyone tell me what the difference is between the above code and what I
routinely use (posted below)? Is the workspace the problem?

Thanks.
David



--- often used code starts here ---

Dim wsp As DAO.Workspace
Dim db_wsp As DAO.Database
Dim rst As DAO.Recordset
Dim rstOpen As Boolean

Set wsp = DBEngine.Workspaces(0)
Set db_wsp = wsp(0)

Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim tdfs As DAO.TableDefs

Set tdfs = db_wsp.TableDefs <----- this sometimes can't be set
For each fld in tdfs <--- this falls out of scope immediately

 

[Allen Browne]

David, are you sure this is a question of scope?
Under what circumstances does it fail?

The code looks okay, but if CurrentDb() works when the dbEngine(0)(0) does
not, there could be a couple of things happening.

Firstly, a call to CurrentDb() flushes all the collections in the DAO model.
If you just added a new table through a means other than DAO, then
dbEngine(0)(0) may not find the tabledef yet, whereas CurrentDb() would find
it (since it forces the update of the collections.)

The other possibility is that dbEngine(0)(0) does not refer to the current
database. This can happen in some cases, such as after rolling back a
transaction, after running a wizard, or after compacting the database. You
may find that the current database actually became dbEngine(0)(1) for
example.

So, in general, it is preferable to use Currentdb() if there is any chance
that the data struture is fluid, or if any of the cases in the previous
paragraph could apply, though you do want to use the Workspace object for
your transactions.

 

[David Mueller]

Allen, Fabulous!

My data structure is extremely "fluid" at times, and this explains some
other behavior I couldn't explain.

David