Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    9

    Unanswered: TableDefs an Asyncrous problem?

    The short code snippet below executes an SQL string which uses a previously constructed temporary table tblTemp. The tidy up code removes the temporary table.

    Unless I place the Set db=Nothing before the attempted deletion I receive an error message to the effect that the tblTemp is not in the TableDefs collection. The table tblTemp was also created via a SQL string db.Execute.

    I would like to understand how to force the temporary table into the TableDefs collection ready for deletion, obviously this hasn't happened when the deletion line is reached. If that line is removed the table appears in the TableDefs when the routine ends.

    Presumably db=Nothing does that (not sure why and I suppose the purpose of the post is to understand why this is) but is there any other parameter I can pass with a db.Execute which will remove this sync behaviour problem.

    Dim db As Database
    Set db = CurrentDb

    REM Other code here

    Rem The string SQL references a temporary table "tblTemp"

    db.Execute SQL, dbFailOnError
    REM The next line needed otherwise deletion fails with "not in collection error"
    Set db = Nothing

    Set db = CurrentDb
    db.TableDefs.Delete "tblTemp"

    Set db = Nothing

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's hard to provide an precese answer without seeing the whole code, specially the part that creates the temporaty table. However did you try to refresh the TableDefs collection of the database before trying to remove the temporary table?
    Code:
    db.Execute SQL, dbFailOnError
    REM The next line needed otherwise deletion fails with "not in collection error"
    ' Set db = Nothing
    
    ' Set db = CurrentDb
    
    db.TableDefs.Refresh
    db.TableDefs.Delete "tblTemp"
    Set db = Nothing
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    9

    Thumbs up

    Quote Originally Posted by Sinndho View Post
    It's hard to provide an precese answer without seeing the whole code, specially the part that creates the temporaty table. However did you try to refresh the TableDefs collection of the database before trying to remove the temporary table?
    Code:
    db.Execute SQL, dbFailOnError
    REM The next line needed otherwise deletion fails with "not in collection error"
    ' Set db = Nothing
    
    ' Set db = CurrentDb
    
    db.TableDefs.Refresh
    db.TableDefs.Delete "tblTemp"
    Set db = Nothing
    Thanks for the insight. Further research at see here reveals that TableDefs is NOT refreshed when a SQL execute that creates a table is used. The TableDefs Refresh method which does that is undocumented according to the article. So that in fact answers my inquiry and also provides a solution.
    Thanks again for the hint re TableDefs refresh which I didn't know about.
    Set db=Nothing must cause the refresh also.
    Regards,
    John B
    Last edited by johnbirt; 05-14-12 at 15:08. Reason: Missing link

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •